grouping/report formula question 1

[psimonnyco]

Hello

I have CRX and SQL Server 2000.

In the time record table, I have the following:

EMPLOYEE, PAY CODE, HOURS, WAGES
123, 01, 40, 1000
456, 01, 20, 500
456, 01, 24, 1000

ETC.

On the employee table, I have:

EMPLOYEE, RATE
123, 1000
456, 1400

I'd like to be able to write a report that sums the hours on time record such that I have one record for each employee's 01 pay:

123, 1000
456, 1400

Easy enough, right. I created a formula that compares the total amount to be paid from time record against the RATE field on employee (diff)

I can't set my select expert to diff>0.01 because the expression needs to be evaluated later.

The best that I could do was to suppress zero values under format. Still, having people wade though 20 pages of data to find the 30 records that matter doesn't seem ideal.

I could play with a SQL command here to do the grouping before it returns to the report, but this simply may be a CRX limitation. The DBA here isn't too keen on custom views.

Ideas? Thanks.

 

[lbass]

Please show what the result would look like, using your sample data.

-LB

 

[psimonnyco]

employee, rate, sumoftimerecords, diff
456, 1400, 1500, 100

 

[lbass]

Insert a group on employee. Then place {record.wages} in the detail section and insert a sum on it at the group level. Place {employee.employee} and {employee.rate} in the group footer, next to the inserted summary, and then create a formula:

//{@diff}:
sum({record.wages},{employee.employee}) - {employee.rate}

and add it for the difference.

-LB

 

[psimonnyco]

Thank you!

//{@diff}:
sum({record.wages},{employee.employee}) - {employee.rate}

where do I put the calculation?

and add it for the difference.

Add "it"? What do you mean?

 

[lbass]

Just place it directly in the group footer. Then suppress the group header and detail sections.

-LB

 

[psimonnyco]

I don't follow. I must be missing something. I didn't see that new 'diff' calc as a field on which I could use the select expert to suppress zero values. I think that I am stuck with using the entire report, exporting to Excel, and then filtering.

Thank you.

 

[lbass]

No, you can't select on that. I didn't understand that was what you wanted to do. Leave the layout as I suggested and go to report->selection formula->GROUP and enter:

sum({record.wages},{employee.employee}) - maximum({employee.rate},{employee.employee}) <> 0

Then if you want to do any calculations across groups, you will need to use running totals.

-LB

 

[psimonnyco]

Thanks. I built the group selection formula. I'm not sure, though, about which running total I would use to get around this CR limitation. Ideally, only the differences would print from the sum of 01 time records.

 

[lbass]

You did not show any records other than 01. Why not eliminate the others from the report in report->selection formula->record:

{table.paycode} = '01'

Having to use running totals is not a CR "limitation." The issue is that record selection selects the records for the report, and group selection selects from that pool which records will be displayed. The non-displayed records still contribute to inserted summaries. You didn't mention that you wanted to summarize across groups, but if you do, you would go to the field explorer->running total->new and in the first section select the summary and the field you want to summarize, then evaluate for each record, reset never (for a grand total). Place the running total in the report footer. It will automatically exclude non-displayed records.

-LB

 

[psimonnyco]

Adding a command would be an option if the performance locally wasn't so slow. I can only imagine how long this would take to run going through additional layers over the Web. I have put the SQL below.

This might be an instance in which performance trumps format.

Select "TIMERECORD"."PAY_CODE", SUM("TIMERECORD"."WAGE_AMOUNT") AS REGWAGES, SUM("TIMERECORD"."HOURS") AS REGHOURS, "TIMERECORD"."EMPLOYEE", "TIMERECORD"."STATUS"
FROM "DB30_LAW_PROD"."lawuser"."TIMERECORD" TIMERECORD
GROUP BY "TIMERECORD"."EMPLOYEE","TIMERECORD"."PAY_CODE","TIMERECORD"."STATUS"
HAVING "TIMERECORD"."PAY_CODE"='01'

 

[lbass]

Is there a question in there somewhere?

-LB

 

[psimonnyco]

Not one, per se. Just providing options.