Report Design: maximum date

Print Page | Close Window

maximum date

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5524
Printed Date: 04 May 2024 at 2:52pm

Topic: maximum date

Posted By: lrcrystaluser
Subject: maximum date
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!

Replies:

Posted By: Vack
Date 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}

Posted By: lrcrystaluser
Date 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!

Posted By: Vack
Date 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.

Posted By: lrcrystaluser
Date 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!

Posted By: Vack
Date 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.

Posted By: rahulwalawalkar
Date Posted: 18 Feb 2009 at 3:00am

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

Posted By: lrcrystaluser
Date 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!

Posted By: rahulwalawalkar
Date Posted: 18 Feb 2009 at 3:42am

as per my suggestion the above requirement will be satisfied can you explain in more detail pls.....

cheers

rahul

Posted By: lrcrystaluser
Date 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!

Posted By: rahulwalawalkar
Date Posted: 19 Feb 2009 at 2:03am

Can you explain in more detail please,as what codes and how many parameters you are using to filter your report records,because if you are using filters in your record selection formula ii will filter the report as per the code ,So for example if you enter code C you will see records for C with Maximum Date ,and if you enter S as code you will see records for S and not C.

So please explain as when you say that you can't suppress records ? and also another parameter means what according to you,also can you paste more sample data ,so that we can help you.....

Cheers

Rahul

Posted By: lrcrystaluser
Date Posted: 24 Feb 2009 at 7:17am

My apologies to all I have not explained myself clearly at all. Here is what I am trying to do:

I have 3 fields: username, date, code
i have a startcode and an endcode string parameter
i have grouped by username then displayed all of that users records

I am trying to calculate the difference in dates where the startdate = the maximum date where code = the startcode parameter and the enddate = the maximum date where code = endcode

for example:
user enters C for startcode and a S for endcode

in the username group header/footer I want to display the
maximum date where code = C, the maximum date where code = S and the difference in weeks between these 2 dates.

I hope that this better explains what I am trying to do and thanks in advance for any help.

-------------
Keep on trying!

Posted By: rahulwalawalkar
Date Posted: 27 Feb 2009 at 3:43am

This is what you do...

Create two subreports,one for Start Code and One for EndCode.

Steps for Start Code Sub-Report ,follow the same steps for EndCode Subreport

In those two Sub-Reports

Create Group by

UserName and then

Code

Then in Insert Summary Insert Maximum Date for that Code.

suppress all the other sections.

Then Create a Shared Variable to capture the maximum date for the StartCode

Create a Formula for that.

Frm_SharedSt_codedate

WhilePrintingRecords;
Shared datevar StDate;
StDate := date({max_date.date})

Place the formula in suppress section above Group Header.

Then Insert the subreport in Main Report Group Header once done right click the subreport and change subreport links

Move(?ParameterStartCodename} to Fields to Link to and Check the box

Select data in subreport based on field select the code from dropdown.

Then in subreport record selection formula check for the below code is created automatically if not enter the code below

{table.codefieldname} = {?Pm-?StartCodefielname}

So you will get Maxdate for start code for the username ,

Then in main report you will need to create a formula to get the max date for start code

Create a formula

Frm_SharedStCodeDate

WhilePrintingRecords;
Shared datevar StDate;

Place this formula in Group Header or Footer .

once done to calculate diff in weeks use datediff

Create a formula

DiffInWeeks

DateDiff ("w",{@Frm_SharedStDate},{@Frm_SharedEndDate})

For give date the value for weeks is 1

Let me know how it goes,if you want I can mail you the report with your sample data

Cheers

Rahul

Posted By: lrcrystaluser
Date Posted: 27 Feb 2009 at 6:29am

Hi Rahul,
First many thanks for your help. This is beyond anything I have done before and I am struggling with the sub report formula. What is {max_date.date}?

-------------
Keep on trying!

Posted By: rahulwalawalkar
Date Posted: 27 Feb 2009 at 7:04am

That will be your {table.datefieldname}

Cheers

Rahul

Posted By: lrcrystaluser
Date Posted: 27 Feb 2009 at 7:34am

that is what I thought but I am getting an error "A date time is required here."

-------------
Keep on trying!

Posted By: DBlank
Date Posted: 27 Feb 2009 at 8:06am

this must be a string rather than a date field

try converting it using the cdate function:

cdate({table.datefieldname})

Posted By: lrcrystaluser
Date Posted: 27 Feb 2009 at 8:11am

it is a date field but I tried the cdate function anyway. Still the same error!

-------------
Keep on trying!

Posted By: DBlank
Date Posted: 27 Feb 2009 at 8:14am

just to check try cdatetime

Posted By: lrcrystaluser
Date Posted: 27 Feb 2009 at 8:18am

I had to change the variable to a datetimevar but that has got rid of the error. Thank you!

-------------
Keep on trying!

Print Page | Close Window