How to identify emp's with and emp's without ded code xxx in same rpt. 2

[NewToThis2]

CR V9 - I am writing a report that identifies those employees with a deduction code of 855 (FSA) but I want the list to include all employees even if they don't have that deduction code. For example, those who have a deduction code of 855 will show the dollar amount and those who do not have that deduction code, the amount field will be blank or zero.

Further Info: Every employee has various deductions codes/amounts which I do not want to see. In other words, if an employee has 4 different deduction codes and none of them are 855, I still need the employee to show up on the report with an amount of zero dollars. If an employee has 4 different deduction codes and one of them IS 855, then I need that employee to show on the report with the associated dollar amount.

Your suggestions/help would be greatly appreciated.

Thanks so much!

 

[mbolek]

why dont you write a select statement getting all the data then group on deduction code and employee name. It will show all the zeros in group and 855

Then according to the data you have, you can create formulas and make the amount display as blank or 0 when amount field is null
formula would be
if isnull({amountfield})= true then
0
else
{amountfield}

Regards
Emre

 

[GJParker]

The best way to handle this will depend on how your data is held in the database. i.e.

is the data held in a single table or many tables.



Gary Parker
MIS Data Analyst
Manchester, England

 

[NewToThis2]

Both the deduction codes and amounts are in one table. The rest of the static employee data is in another file. They are linked by employee number.

 

[lbass]

You need a left join FROM the employee table to the deduction table, with no selection criteria on the deduction table.

Then write a formula {@855}:

if isnull({deduction.type}) or
{deduction.type} <> 855 then 0 else
{deduction.amount}

Place this in the detail section, along with the employee ID. If there are multiple entries per employee, then you could right click on {@855} and insert a summary (sum) and then suppress the detail section.

-LB