Select highest pay rate

[f0rg3tfu1]

This one might be a bit tricky, so if you need clarification please ask.

Working with CR 10, SQL Server backend.

I have a report that lists all timesheets for our employees. Basic report...

In the group header: Name, SSN, address, Rate of Pay dates and Rate of pay.

In the Detail: Timesheet Date, Charge Hours, and Pay Type.

Only problem is that when our employees recieve a raise, Its showing up as two different timesheets on my report. I only want to return the higher payrate.

I tried using a formula inserted in the group header: Maximum ({EMPL_LAB_INFO.HRLY_AMT}) but it didnt seem to help.

Any ideas?

 

[lbass]

Assuming you have a group on {table.employee}, go to report->edit selection formula->GROUP and enter:

{table.rateofpaydate} = maximum({table.rateofpaydate},{table.employee})

This will return those detail records with the most recent pay rate. However, if you need to do calculations, you will need to use running totals, since the non-group-selected records will still contribute to inserted summaries. You could also create a SQL expression {%maxdate}:

(select max(AKA.`rateofpaydate`) from Table AKA
where AKA.`Employee` = Table.`Employee`)

Substitute your table name for "Table" wherever it appears, and substitute your exact field names for "Employee" and "rateofpaydate". If you have other groups or conditions in your record selection criteria this expression would need to be adjusted. Then go to report->edit selection formula->RECORD and enter:

{table.rateofpaydate} = {%maxdate}

The last solution will allow you to use inserted summaries as necessary.

-LB

 

[f0rg3tfu1]

Thanks for the assistance, but its not working for some reason. Here is my formula in the Group Selection area:

{EMPL_LAB_INFO.HRLY_AMT} = maximum({EMPL_LAB_INFO.HRLY_AMT},{SUMMARIZATION_EMPLOYEES.EMPL_ID})

Im still returning the same results as before. The group is on the {SUMMARIZATION_EMPLOYEES.EMPL_ID}.

If i used the SQL statement, where would I insert it in the report?

 

[lbass]

You are using the wrong field. You want to use the rate of pay DATE field, not the amount field. I assumed that this date field appears at the detail level, but that it was dragged into the group header.

As I mentioned earlier, you would place the SQL expression in your record selection formula:

{table.rateofpaydate} = {%maxdate}

-LB

 

[f0rg3tfu1]

So when you refer to RateofpayDate field... are you refering to the date at which the rate of pay changes?

If so this was a typo on my part... I meant to type dates of employment but I had been distracted.

The sql view is not pulling a date for change of pay rate. Is there a way to do this using just the rate of pay as stated in my original question?

That SQL statement you gave me should work by using rate of pay if im not mistaken correct?

 

[lbass]

Your formula for the group selection should actually have worked. The only reason it wouldn't is if you have some other groups that conflict with this. So what is your entire group structure?

Or I think the SQL expression should work with pay, also, but if you have other groups we need to know what they are.

-LB

 

[f0rg3tfu1]

No the Employee ID field is the only one we are grouping on. I too am unsure of whats happening here.

 

[lbass]

Try laying out your fields in the detail section so you can see what's going on, i.e., drag the fields from the group header into the detail section and then report back with a sample display of the results.

Please note that when you use group selection, all records will still appear in the group tree and contribute to inserted summaries, but the display on the report canvas changes. Are you sure the group selection didn't work?

-LB

 

[f0rg3tfu1]

Ill check it out that way. Long story, but our DB is pretty screwy... never did like the format.