List Deduction codes on one row

[NOLA]

How would you create a report that would list every Deduction code total on one row by employee?

 

[BryanLane]

You must explicitly create a column for each deduction, ReportSmith will not come up with them for you.

Select the table (I used V_Chk_VW_Deductions) and then select name, pay period, dedcode & dedamt. Make dedcode & dedamt query only.

Create a derived field for each column you want. Such as Dental. In the formula panel first select (or type) Sum (Distinct()[under Summary functions] and then then @Decode() [under String functions], nested in the Distinct(). The decode components will be dedcode, the code you want, dedamt and zero. So that the formula looks like:
SUM(DISTINCT
@DECODE(REPORTS.V_CHK_VW_DEDUCTION.CHECKVIEWDEDCODE,
'B',
REPORTS.V_CHK_VW_DEDUCTION.CHECKVIEWDEDAMT,
0)
)
I line-broke it for my only clarity when I proof them.

Now you must use Database Group. Check off the Group data before loading... box and then select how you want to group it, in this case by employee name.

Select the pay period you want and click of the "Exclude duplicate rows" checkbox at the bottom

Click Done and you will see a report with a single line for each employee and an amount for their dental deduction. Add the remaining deductions as a Derived Formula (copy and paste the dental and just change the code as you create each one.

 

[IamnotCharles]

I would not exclude duplicate rows. If there are duplicates, they need to be investigated. As an example, if an employee had the identical deduction code and amount for a single paydate, something may be wrong. It could possibly be a manual check that was updated, or there was a double deduction for a vacation check, etc.