Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

List Deduction codes on one row

Status
Not open for further replies.

NOLA

Technical User
Apr 14, 2003
18
US
How would you create a report that would list every Deduction code total on one row by employee?






 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top