| Author |
Message |
lrcrystaluser
Newbie
Joined: 16 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 10
|
   Topic: maximum date
Posted: 16 Feb 2009 at 7:29am |
|
Hi all
i have a problem getting the maximum date based on another field for example i have the following fields grouped by username
username, date, code
what I want is the maximum date based on the code = {?Parameter} then if the code for that user does not exist I need to show a maximum date where code={?otherparameter}
Any help would be greatly appreciated!
|
|
Keep on trying!
|
 IP Logged |
|
|
Vack
Newbie
Joined: 22 Oct 2008
Location: United States
Online Status: Offline
Posts: 24
|
Posted: 16 Feb 2009 at 10:40am |
I would go into the section expert of your details section. Select the suppress check box. Hit the formula button.
date < maximum
This will suppress all records that are less than your maximum date.
When you get to the 2nd part of your question it seems like you would need to group by username and code and doing maximum subtotals for each group. May need more information on the codes and what could be contained in {?otherparameter}
|
|
IP Logged |
|
lrcrystaluser
Newbie
Joined: 16 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 10
|
Posted: 16 Feb 2009 at 11:05am |
|
Unfortunately I can't suppress the other records.
here is an example of the data
Username Date Code
john 01/01/2008 C
john 10/01/2008 C
john 14/01/2008 S
so i would want the date 10/01/2008 returned however if there were no entries that contained a C then I would want 14/01/2008 returned.
Thanks for your reply, I obviously didn't make myself clear.
|
|
Keep on trying!
|
|
IP Logged |
|
Vack
Newbie
Joined: 22 Oct 2008
Location: United States
Online Status: Offline
Posts: 24
|
Posted: 16 Feb 2009 at 11:31am |
This may be over my head then. Are you dealing with VB code or some other programming language? When you say "returned" I'm assuming the record with the 10/01/2008 date would be the only thing you want to show on the report.
|
|
IP Logged |
|
lrcrystaluser
Newbie
Joined: 16 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 10
|
Posted: 17 Feb 2009 at 1:59am |
|
I want the value in a box on the group header so I am just asking for the maximum date value in the group with the same code as {?parameter} if no record with that code exist then give me the maximum date in the group with the same code as {?otherparameter}
|
|
Keep on trying!
|
|
IP Logged |
|
Vack
Newbie
Joined: 22 Oct 2008
Location: United States
Online Status: Offline
Posts: 24
|
Posted: 17 Feb 2009 at 7:26am |
I'm not able to get the max date into the header. I can only get it to work with the footer by using a running total and having it evaluate using
?Paramerter = Code
This would look at my parameter value I entered and only give the maxdate from that code.
Then you would do a 2nd running total where code<>?parameter.
Sorry I couldn't get what you were looking for.
|
|
IP Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 18 Feb 2009 at 3:00am |
Hi
This could be a solution
Since you have grouped by username ,you can Insert summary which will be on Date Field and summary type will be Maximum at Group Level once done.
Go to Details Section Right Click then select Section Expert then in Common Tab Click X+2 next to Suppress(No Drill-Down) and enter the code below
{TABLE.datefieldname}< Maximum ({table.datefieldname}, {TABLE.usernamefieldname})
So when you run the report it will ask for the code so if you enter C and if record details exist for C it will show you record with the Maximum date for C if you select S it will show you records for with the Maximum date for S
let us know if this is not correct.
Cheers
Rahul
Edited by rahulwalawalkar - 18 Feb 2009 at 3:01am
|
|
IP Logged |
|
lrcrystaluser
Newbie
Joined: 16 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 10
|
Posted: 18 Feb 2009 at 3:14am |
|
Thanks for the reply Rahul the problem is that I cannot suppress any records, they are required by the user also.
|
|
Keep on trying!
|
|
IP Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 18 Feb 2009 at 3:42am |
Unfortunately I can't suppress the other records. here is an example of the data Username Date Code john 01/01/2008 C john 10/01/2008 C john 14/01/2008 S so i would want the date 10/01/2008 returned however if there were no entries that contained a C then I would want 14/01/2008 returned. Thanks for your reply, I obviously didn't make myself clear.
as per my suggestion the above requirement will be satisfied can you explain in more detail pls.....
cheers
rahul
Edited by rahulwalawalkar - 18 Feb 2009 at 3:42am
|
|
IP Logged |
|
lrcrystaluser
Newbie
Joined: 16 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 10
|
Posted: 19 Feb 2009 at 12:55am |
|
that was a very small example. There may well be many other codes that are connected and these would also need to be viewed.
|
|
Keep on trying!
|
|
IP Logged |
|
|
|
