Formula to pull Maximum of DateTime field

[pokerace]

Crystal 10 - ODBC

I am trying to insert a line into a formula that will allow me to only select the records with the most recent date. The field for this is a DateTime field. Here is the formula:


Maximum({JOB_STEP_EMP_PROD_RATE.JOB_STEP_EMP_PROD_RATE_DATE})

When I insert that into the formula is tells me that a boolean is required. Any help with why or what needs done?

 

[damonkimble]

Go to select expert -> show formula -> formula editor:

{JOB_STEP_EMP_PROD_RATE.JOB_STEP_EMP_PROD_RATE_DATE} = Maximum({JOB_STEP_EMP_PROD_RATE.JOB_STEP_EMP_PROD_RATE_DATE})

Assuming there is nothing else there, adding the above should do the trick.

cheers,
damon

 

[damonkimble]

Please note above is assuming that it is a date field, not a datetime.

damon

 

[lbass]

You are probably intending to select the maximum per a particular group, so you should go report->selection formula->GROUP and enter:

{JOB_STEP_EMP_PROD_RATE.JOB_STEP_EMP_PROD_RATE_DATE} = Maximum({JOB_STEP_EMP_PROD_RATE.JOB_STEP_EMP_PROD_RATE_DATE}, {table.groupfield})

-LB

 

[synapsevampire]

More simply, use Report->Selection Formulas->Record.

If you want the most recent record per entity, then you would group the report by that entity, and instead use the Report->Select Formula->Group and use:

{JOB_STEP_EMP_PROD_RATE.JOB_STEP_EMP_PROD_RATE_DATE} = Maximum({JOB_STEP_EMP_PROD_RATE.JOB_STEP_EMP_PROD_RATE_DATE},{My_Group_Field})

Replace {My_Group_Field} with the field you've groupe by.

-k

 

[pokerace]

Thanks lbass and SV.

 

[pokerace]

Actually, I have run into another problem. The report is now correctly displaying the maximum date and corresponding fields, however, when I sum one of the fields it will still add in the extra data that was previously displayed when the other dates were being pulled.

The database I am using keeps track up people’s % level as in the amount of work they are capable of doing compared to 100%. This % level changes every so often, and since the database keeps a history of these changes with the date of change, each one appears when I run a report for any given work day. If a person has had 10 level changes, then it would show 10x the number of output.

What I want to do is only total the output for the maximum date (most recent change date.) I will be using that specific % level and that day’s output in another formula.

 

[pokerace]

I am able to take a distinct count of the number of dates and then divide the inflated total by the distinct count to get the correct number, but if there is a better way please let me know.

 

[lbass]

Insert a running total where you sum the percent field, evaluate based on a formula:

{JOB_STEP_EMP_PROD_RATE.JOB_STEP_EMP_PROD_RATE_DATE} = Maximum({JOB_STEP_EMP_PROD_RATE.JOB_STEP_EMP_PROD_RATE_DATE}, {table.groupfield})

Reset on change of your person group.

-LB