Crystal Syntax vs. Report Smith Syntax

[AcctSolver]

I am building a report to return payroll data with one line per paycheck. There will be a column for each earnings type. It will be zero of the employee had no earnings of that type, and populated once if he did.

The data is structured with one line for every earnings type.
CHK#, CHKSEQ#, FILE#, NAME, GROSSPAY, REGERNING, EARNSCD, EARNSAMT
12133, 001, 101, MTR, 525.00, 400.00, 12, 15.00
12133, 001, 101, MTR, 525.00, 400.00, B, 30.00
12133, 001, 101, MTR, 525.00, 400.00, H, 80.00

I want my report to look like this:
CHK#, CHKSEQ#, FILE#, NAME, GROSSPAY, REGERNING, EARN12, EARNB, EARNH
12133, 001, 101, MTR, 525.00, 400.00, 15.00, 30.00, 80.00

The commas are just there to help make the field separations more clear.

In ReportSmith, the tool supplied with ADP Payroll, the syntax to get the regular earnings looked like this:
SUM(DISTINCT @NULLVALUE(REPORTS.V_CHK_VW_EARNINGS.REGERNING,0) )

To get the other earnings it looked like this
SUM(DISTINCT @NULLVALUE( @DECODE(REPORTS.V_CHK_VW_EARNINGS.EARNSCD,'12',REPORTS.V_CHK_VW_EARNINGS.EARNSAMT),0) )

What is the appropriate syntax in Crystal to achieve the same result? I will have as many lines as there are paychecks for all employees for a year. I will also have totals at the bottom of the columns.

 

[Charliy]

use a Running Total and do Distinct Count

 

[hilfy]

Actually, it's a little more complicated than that.

1. Group on check number.
2. Suppress the details and the check number group header.
3. Put your data in the group footer section.
4. For the Grosspay and Regerning columns, place a summary to get the maximum value for the check number.
5. For EACH of the other columns, create a formula like this:

if {MyTable.ERNSCD} = '12' then {MyTable.ERNSAMT} else 0

6. To put the additional data in the columns, place a summary that gets the maximum value for the check number.

There are no running totals required.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[AcctSolver]

Thank you hilfy, that did solve most of the issues, but I am not home free yet.
I grouped on a concatenated field of check#, check sequence number, and name.
The idea of using a maximum as a total worked great for the most part. However, the data set has both positive and negative numbers in the fields. When I have a negative number in the field, the results return 0 as the maximum.

I could create formulas to split negative numbers into different fields, then use min sums on the negative fields. Does anyone have a less cumbersome idea? My report is already 156 fields wide now.

Thanks,

 

[hilfy]

Would a max of the absolute value work?

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com