Formula help needed - Emp Deductions 2

[NewToThis2]

CR V9, Oracle database. I need to write a report that identifies every employee who DOES NOT have a Dental and/or a Vision deduction (Codes 835 and 845). There are 40 different deduction codes and an employee could be signed up for any number of them. When I tried to exclude the codes for Dental and for Vision, (to find out who doesn't have them) the report brings back every other deduction that applies to the employee - times hundreds of employees. I don't want to see any of those other deductions, I only want the result to look something like this:

Emp 17 - John Doe, No Dental Deduction
Emp 17 - John Doe, No Vision Deduction
Emp 10 - Joe Brown, No Dental Deduction
Emp 12 - Jane Green, No Dental Deduction
Emp 12 - Jane Green, No Vision Deduction

Any help would be much appreciated. I'm new to CR and I'm having trouble thinking this one through. Thanks so much!

 

[fredp1]

Id approach this problem using sql. I can't think of a way that will work in CR.

Basically breaking the problem down,
1) You need a subquery to give you a list of employees that have a dental or vision record.
2) You need to the do a left outer join with your employee table and the above subquery.
3) then specify you only what to see the employees that do dot exist in the subquery. (is null)
in SQl Server the code is this

select t1.emp from tblemployee t1 left outer join (select emp from tbltransaction where code=835 or code=845) t2 on t1.emp=t2.emp
where t2.emp is null

If you can create a view in Oracle, great. Then you can use CR to use that view.

In Crystal you will need to change the SQL to be something like the above example. (of course substitute your table/field names for the ones that I used)

I've only have 8.5 so I can't be specific in v9.

Has anyone got any approaches in using Crystal?

Fred

 

[fredp1]

Actually there is a way to do this using straight CR but it is a bit detailed.

You need a subreport, shared variable and conditional suppresion to acheive it.

1) On your main report, add the employee table and add the employee id in the detail section.
2)Insert a new deatil section above the existing detail so the subreport can go there.
3) Insert a Subreport and add the transaction table with the filter of the 2 codes that you want.
The subreport will go in section A.
4) Edit the subreport and add a shared variable e.g.
{@emp}
whileprintingrecords;
shared stringvar shremp:='';
shremp:= {transaction.emp}
5)Make sure that perform grouping on server is off on the subreport.
6) suppress every section in the sub.
7) on the main report create a shared variable e.g.
{@shremp}
whileprintingrecords;
shared stringvar shremp;
8) on the details B section where the emp no is displayed. format the section and go to conditional supression
and add not({@shremp}='' )
9) format the subreport by removing the borders, resize the subreport in section A to make it as narrow as possible and format the section to underlay following sections.
10) very important to link the subreport by emp (right click on the sub and change subreport links, click on the emp field then click on > button.

The trick is the conditional suppresion. Unfortunatly because of the way CR is designed, you cannot use a shared variable as part of the selection formula, hence the section suppresion.

That should do it.

 

[lbass]

Another approach is to use a left join from the employee table to the deductions table. Insert a group on {employee.ID}, and then create a formula {@dentalvision}:

if isnull({deduction.code}) then 1 else
if {deduction.code} = 835 then 1000 else
if {deduction.code} = 845 then 100 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@dentalvision},{employee.ID}) in 1 to 1000

This will return only those employees who have no deductions or who have either dental or vision deductions, but not both.

Then create a formula in the formula editor {@missingdeduction}:

if sum({@dentalvision},{employee.ID}) = 1 then
"No Dental, No Vision" else
if sum({@dentalvision},{employee.ID}) = 1000 then
"No Vision" else
if sum({@dentalvision},{employee.ID}) = 100 then
"No Dental"

Place this formula in the group header for the employee.

-LB

 

[fredp1]

Thats a very interesting technique!
Its certainly much more efficient that the above example.
I like it.

Fred