Derived Fields for Lien listing

[ebisabes]

I want a list of all employees with a line for each lien. Right now I have it using 3 Tables: Liens, EE, & Payee info

Without any derived fields, for employees with more than one lien, it gives me each payee info under every lien case# (that many times)

ADP verified that I can fix this problem with derived fields but, is it a Sum(Distinct(@ decode ??? of some sort... Please let me know how to form the correct derived field so that each payee info only shows once for the correct Lien Case #

Thank you!

 

[CharlesCook]

You would use Database grouping and derived fields.

Here is an overview written for PCPW but the ideas are generic.

http://charlescook.com/PPS/ADP/ReportSmith/PCPW/DatabaseGrouping.pps

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[ArtieArt]

I do not have a pcpw database but this is the formula you need. Change the "PS_AL_CHK_DED"."AL_DEDCD" to your table & field as well as the 2nd "PS_AL_CHK_DED"."AL_AMOUNT" and the 'V' to your value. Make the fields with in the table that you are using in the derived field formula Query Only and then click the box at top right in Database Grouping, click done and Wa-La....

Copy & Paste

SUM(DISTINCT DECODE("PS_AL_CHK_DED"."AL_DEDCD",'V',"PS_AL_CHK_DED"."AL_AMOUNT",0 ) )


Artie Dunnings
ADTECH Consulting Inc
HRIS Functional & Technical Trainer
Alpharetta, GA