Using Maximum function in report with out having grouping in report

[chsri]

Hi,

I need to pick documents with latest release date and calculate expiry date by adding expiry period (r_expperiod)
to it. Later I need to create a formula (expiry_status) to find "docs already expired" and "Docs expiring in 30 days" and should group by expiry_status.

My problem is to calculate latest release date i need to group by document number first, and use Maximum function to get latest release date and calculate expiry date and expiry_status

But i dont want my report format to be grouped by document no. first because after i group by expiry_status, for each document i will have "expired" and "expired in 30 days" grouping.

To avoid this i tried to have SQL Expression format, but i cannot get a single return value for this since i cannot pass the document number to the expression.

Is there any alternative for me to calculate the latest date with out having the grouping format (by doc number) in my report.


R_DATERELEASED R_DOCNUMBER R_EXPPERIOD
--------- -------------------------------- -----------
01-JAN-01 MF-7023 24
01-JAN-01 MF-7022 24
03-DEC-01 MF-7032 24
01-JAN-01 MF-7032 24
01-JAN-01 MF-7025 24
31-JAN-03 MF-7025 24
10-AUG-00 MF-8035 24
01-JAN-01 MF-8035 24

I thank in advance for your support.

Regards
Chunduru

 

[lbass]

If you are using 8.0 or 8.5 you can change the SQL query directly by going to database->show SQL query and adding the following at the end of the WHERE statement:

and table."releasedate" = (Select MAX(AKA."releasedate") from table AKA where AKA."docno" = table."docno")

You would substitute your table name for "table" and your field names for "docno" and "releasedate". You can now insert a group on {@status} where only the maximum record per document number will appear in any group (without capturing multiple records by document number).

If this isn't an option for you, you could group on {table.docno} (and suppress the group header and footer) and then go to report->edit selection formula->GROUP and enter:

{table.releasedate} = maximum({table.releasedate},{table.docno})

Then create a formula {@status} like the following (incorporating your own logic):

if datediff("d",{table.releasedate},currentdate) > 30 then "Already Expired" else
"Expiring in 30 Days"

Then right click on {@status} and insert a minimum, and right click on {table.releasedate} and insert a maximum. Then go to report->topN/sort groups and choose:

"maximum of {table.releasedate}" - descending
"minimum of {@status}" - descending

This will display all those expiring within 30 days first, followed by all those already expired. Although not in groups per se, they are clustered together.

-LB

 

[synapsevampire]

As LB alludes to, this is based on technical information, such as Crystal version and the database and connectivity used.

And intead of modifying the SQL directly, which is generally a bad idea as you lose the ability to use the GUI to modify it going forward, you might use a SQL Expression to accomplish this.

I've created some fairly advanced SQL statements, including correlated subqueries.

-k

 

[synapsevampire]

I forgot to mention, SQL Expressions are available from CR 7 and up.

-k