Returning the maximum value in a field before printing 1

[simonfisher]

Hi,

I am using CR10 with an Oracle database. I am trying to retrieve rows based on the maximum value contained in a column as I only want some of the rows.

This is what I am trying to do but I keep getting an error in the formula. Could someone please help.

WhileReadingRecords;
numberVar maxPeriod;
maxPeriod := Maximum ({ACNT_HIST.PERIOD});
maxPeriod := maxPeriod - 99;

The error message relates to the Maximum function:

'This function cannot be used because it must be evaluated later'.

Is there another way of doing this?

Thanx

Simon Fisher
Perth, Western Australia

 

[Madawc]

If you get maximum as a summary (right click and chose insert), then you could suppress detail lines by suppressing the section, using a formula based on that value. Does that help?

Madawc Williams (East Anglia, UK)

 

[dgillz]

Dump this formula entirely. Go to report, edit selection formula, group, and enter a formula:

{ACNT_HIST.PERIOD} = Maximum ({ACNT_HIST.PERIOD})

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[lbass]

If you use group selection and use dgillz' formula, you will only return those records which equal the maximum date for the entire report (which might be what you are looking for). If you want to do this per group, then use:

{ACNT_HIST.PERIOD} = maximum({ACNT_HIST.PERIOD},{ACNT_HIST.groupfield})

Another approach would be to use a SQL expression {%maxperiod}:

(select max(AKA.`PERIOD`) from ACNT_HIST AKA where
AKA.`groupfield` = ACNT_HIST.`groupfield`)

You would replace "groupfield" with your actual group name.

Then you would go to the record selection formula and enter:

{ACNT_PERIOD} = {%maxperiod}

The advantage of using a SQL expression is that you can use inserted summaries, while if you use group selection, you will need to use running totals to avoid counting non-group selected records.

-LB

 

[simonfisher]

I need to retrieve the MAX(period) because I need to use this value to retrieve rows for the previous 12 months only and not all the rows. I thought by finding the MAX(Period), I would then be able to calculate the starting period from which to retrieve the rows. Once I get the MAX(Period) I will then need to perform some calculations on it before I can start doing the retreival. Essentially I want to do an SQL BETWEEN with the MAX(Period) as the upper limit and the calculated value as the lower limit.

Any Ideas?

Thanx


Simon Fisher

Perth, Western Australia

 

[lbass]

Simon,

When you repost, I think you should show some examples of the period field and specify its datatype. If it is a number that resets for each year, then that would have to be accommodated.

-LB

 

[simonfisher]

ACNT_HIST.PERIOD} is a Number field and contains values like 200503, 200502, 200502, 200412 etc. I need to only extract rows for the last twelve months. I need to get the maximum value of the period and then calculate the previous 12 months. If MAX(ACNT_HIST.PERIOD} ) returns 200503 then I need to start the data from 200404.

Thanx

Simon Fisher

 

[lbass]

I still don't know whether you need the maximum for groups or for the report itself. Since you haven't mentioned any groups, I suppose I should assume that is what you intend. I would still use the SQL expression {%maxperiod}:

(select max(AKA.`PERIOD`) from ACNT_HIST AKA)

Then use a record selection formula like:

{ACNT_PERIOD} in
(
if val(right(totext({%maxperiod},"000000"),2)) <> 12 then
val(totext(val(left(totext({%maxperiod},"000000"),4))-1,"0000") + totext(val(right(totext({%maxperiod},"000000"),2))+1,"00")) else
val(left(totext({%maxperiod},"000000"),4)+"01")
) to {%maxperiod}

You could also convert the periods to dates, but that would entail a similar amount of coding.

-LB

 

[simonfisher]

Thanks for that, it works fine.