How can I eliminate unwanted records by using the maximum function? 1

[AndersonCJA]

Crystal reports 8
oracle database

If I try the line of code below as part of the selection criteria, I recieve a message, "This function cannot be used because it must be evaluated later"

{table.field} = maximum({table.field})

I know there is the option of grouping by the field name, however it would be so much more effecient to have a way to get only the records I need.

Is there a way?

Thank you,

 

[lbass]

If you have the option of using SQL expressions (look in the field explorer), create the following expression
{%maxdate}:

(select max(A.`date`) from Table A where
A.`groupfield` = Table.`groupfield`)

This assumes you want the maximum of some field (here I used date) per some group, e.g., on Customer ID. Substitute your exact field names from 'date' and 'groupfield' and your Table name for 'Table'. Leave 'A' as is, since it represents an alias table name.

Then go to your record selection formula and add:

{table.date} = {%maxdate}

If you can't use a SQL expression, you could use group selection (report->edit selection formula->GROUP), using a formula like:

{table.date} = maximum({table.date},{table.customerID})

However, all records are returned to the report with this method, even though only the maximums are displayed, which makes it harder to do calculations, etc., since you need to use running totals in many instances, etc.

-LB

 

[Madawc]

AndersonCJA, what are you trying to do? Can't you eliminate unwanted records using report> edit selection formula > record?

If you just want the highest value of {table.field}, and won't know its specific value in advanve, consider grouping by {table.field} and then using report> edit selection formula > group select to suppress the other groups. I think you have that in 8; it is definitely there in 8.5.

Madawc Williams (East Anglia, UK)
Windows XP & Crystal 10

 

[GJParker]

Add the selection formula under the group selection

report menu -> selection formula -> group

HTH



Gary Parker
MIS Data Analyst
Manchester, England

 

[AndersonCJA]

Wow! I am delighted! thank you so much.
My objective is to get the most recent transaction on an account.
My first try I tried this:
(SELECT MAX(A.transnbr)
FROM
Trantbl A
WHERE
A."transnbr" = Trantbl."transnbr")

selecting:
{Trantbl.transnbr} = {%MAXRTXN} I still recived duplicate records so I changed the sql expression to:

(SELECT MAX(A.transnbr)
FROM
Trantbl A
WHERE
A."ACCT" = Trantbl."ACCT")

and used the same selection of
{Trantbl.transnbr} = {%MAXRTXN}

This information is so valuable I have never used the sql expression before. What a useful tool this is. Thank you.
I will give you a star.

 

[AndersonCJA]

I want to also thank GJParker and Madawc for their input. I have not used the report group selection before and reviewed this some, and will revisit later. Thank you,
Connie