Using IF and Maximum in selection expert

[Former Member]

I'm trying to select records based off of maximum value in a field but having trouble figuring out the language needed to tell crystal how to do so.  I can suppress records like this once they are in, but dont know what to use for selection expert to exclude them instead.  And that is really what I need to do.

Here is what I have so far.

IF {TableA.FieldY} = Maximum ({TableA.FieldY}) then ???Select the record?? else ???Don't pull the record into the report??

I can hide them via suppression once they are in, but because of how I need to group the report, this is not effective.  To suppress I created a formula:

Formula "ZZZ" - IF {TableA.FieldY} = Maximum ({TableA.FieldY}) then {{TableA.FieldY} else "NotCurrent"

Supression - if {@ZZZ} = "NotCurrent" then true else false

Thanks for any advice!

[abhilash_kumar · Accepted solution]

Hi Derrick,

If you're reporting directly against tables/views and want this selection criteria to be pushed back to the database, here's what you need to do:

1) Create a SQL Expression from the Field Explorer. The expr would look like this:

Maximum("TableA.FieldY")

Notice the use of double quotes and not curly braces.

2) Go the Record Selection formula and add this code:

{%Sql Expression} = {TableA.FieldY}

Another way to do this is to report off of a SQL query and include this filter in sql's where clause.

-Abhilash

[Former Member · Accepted solution]

Thanks Abhilash.

I tried this, had to use Max instead of maximum, and get the following errors when attempting to refresh data.  "Failed to retrieve data from the database.  Details: ORA-00934: group function is not allowed here" and "Details: Fail to execute SQL statement.  OCI call:OCIStmtExecute [Database Vendor Code: 394]"

I googled the error code and found:

ORA-00934 group function is not allowed here

Cause: One of the group functions, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, was used in a WHERE or GROUP BY clause.

Action: Remove the group function from the WHERE or GROUP BY clause. The desired result may be achieved by including the function in a subquery or HAVING clause.

Here is my formula in record selection:

(year({ARRSTBND.BND_ARREST_DT}) = {?ReceivedYear}) and

(month({ARRSTBND.BND_ARREST_DT}) = {?ReceivedMonth}) and

({CASE.OFIC_CD} = uppercase({?OfficeCode})) and

{CASE.STAT_CD} = {?CaseStatus} and

{PTY.PTY_CD} in ["DFNDT"] and

{%Maxseq}={BONDSTAT.BND_STAT_SEQ}

Here is ny SQL expression:

Max("BONDSTAT"."BND_STAT_SEQ")

[abhilash_kumar · Accepted solution]

Hi Derrick,

Try modifying the SQL Expression to:

(

Select Max("TableA.FieldY") From TableA

)

The Select statement has to be surrounded by parenthesis.

-Abhilash

[Former Member · Accepted solution]

Thanks again!

we are getting close as the error is gone, but it does not retrieve any records, even when I remove all the other selection criteria.   I'm guessing I need to better define it somehow. 

[abhilash_kumar · Accepted solution]

Remove the SQL Expression from the selection formula.

Next, drag and drop it on the Details Section - does it return a valid date? In this case, does it correctly return the Maximum date from the records?

-Abhilash

[Former Member · Accepted solution]

When placed in the details section is returns one value, the maximum for all 90 records that have been selected.

I guess I need to define to select the maximum value for records its first grouped by?

I can't thank you enough for all your help.. This is amazing!

[abhilash_kumar · Accepted solution]

If you want to return records where the value = Max value of that group, modify the SQL Expression to:

(

SELECT MAX(`A`.`FieldY`)

FROM TableName A

WHERE `A`.`Group_Field` = `YourTable`.`Group_Field`

)

Notice the use of table aliases here.

-Abhilash

[Former Member · Accepted solution]

Getting an error in the expression.    Possible because the group by is in another table?

CASE.DSCR is the main group.  Think of it as customer number.  Each customer has a bond that goes through a life cycle and at each stage in the cycle it gets a sequence number.(BONDSTAT.BND_STAT_SEQ)   When I pull the records, it pulls all the sequences, when I only want the current, or maximum. 

Here is where I am currently, with the SQL expression error:

(

SELECT MAX(`BONDSTAT`.`BND_STAT_SEQ`)

FROM TableName BONDSTAT

WHERE `BONDSTAT`.`DSCR` = `CASE`.`DSCR`

)

[abhilash_kumar · Accepted solution]

What is the error you get this time?

-Abhilash

[abhilash_kumar · Accepted solution]

Try something like this:

(

SELECT MAX(BONDSTAT."BND_STAT_SEQ")

FROM TableName BONDSTAT

WHERE BONDSTAT."DSCR" = CASE."DSCR"

)

-Abhilash

[Former Member · Accepted solution]

Getting error "ORA-00942: table or view does not exist" upon trying to save the SQL expression.

I tried changing the table and filed called in the last line and still get the same error.   The tables do exist.  I linked the CASE_ID fields in the two tables using database expert wizard as well.  (CASE is a view.)  I tried with other tables and the same error remains.

(

SELECT MAX(BONDSTAT."BND_STAT_SEQ")

FROM TableName BONDSTAT

WHERE BONDSTAT."CASE_ID" = CASE."CASE_ID"

)

I'm thinking I need something like this, but don't know the syntax.

(

SELECT BONDSTAT."CASE_ID", Max("BONDSTAT"."BND_STAT_SEQ")

From TableName BONDSTAT

GROUP BY "BONDSTAT"."CASE_ID";

)

In theory, this would pull only records with the highest bond stat seq... yes?

[abhilash_kumar · Accepted solution]

The SQL Expression can only return one field - so this will not work.

Remove the SQL Expression from the report > Go to the Database Menu > Select Show SQL Query.

Does the 'CASE' table show up as 'CASE' or something else?

-Abhilash

[Former Member]

Hello Derrik,

Crystal reports follow multipass reporting model as a reason of that you can't use Max in the record selection directly. So as already suggested please create a SQL statement that will calculate the Maximum of ({TableA.FieldY}) then you can use the same in Record selection.

Hope this helps.

Thanks

Niraj