Creating a benefits report to show 1 row per employee

[scotton]

Okay, I've tried sum(distinct decode and I think it's not working because I'm trying to use sum on a non-numeric field. I'm trying to show all the benefits in pv_health_benefit table with 1 row per employee with columns like such:
Emplid Name Medical Dental Vision
1234 Susie Que Cigna Delta VSP

I keep getting multiple rows. Also, I'd like to include the pv_life_add_ben, pv_disable_ben and pv_fsa_benefit tables so that the field can run 1 report and see ALL the benefits their employee has in 1 report.
I know there is a way to do this I'm just drawing a blank right now.

Any help on this is appreciated.
TIA,

Sarah

 

[CharlesCook]

Try this:

In a new report add the pv_health_benefit table
Use Max Effective Date
For the Effective Date Keys Use
EMPLID
COBRA_DEPENDENT
EMPL_RCD_NBR
PLAN_TYPE
BENEFIT_NBR

Make EMPLID "include in report"
Make EMPL_RCD_NBR "include in report"
Make PLAN_TYPE "Query Only"
Make BENEFIT_NBR "Query Only"

Add the pv_life_add_ben table
Use Max Effective Date
For the Effective Date Keys Use
EMPLID
EMPL_RCD_NBR
PLAN_TYPE
BENEFIT_NBR

Make EMPLID "Query Only"
Make EMPL_RCD_NBR "Query Only"
Make PLAN_TYPE "Query Only"
Make BENEFIT_NBR "Query Only"

Link the tables using
pv_health_benefit.EMPLID = pv_life_add_ben.EMPLID
pv_health_benefit.EMPL_RCD_NBR = pv_life_add_ben.EMPL_RCD_NBR
pv_health_benefit.PLAN_TYPE = pv_life_add_ben.PLAN_TYPE
pv_health_benefit.BENEFIT_NBR = pv_life_add_ben.BENEFIT_NBR

In Database Grouping ONLY Use
pv_health_benefit.EMPLID
pv_health_benefit.EMPL_RCD_NBR

This should give you 1 row per person.

Any other data you need should be made "Query Only" then use a Derived Field and SUM(DISTINCT ) if numeric or FIRST( ) if not numeric(really any agg function that does not return a numeric will work)

Take a look at

http://charlescook.com/PPS/ReportSmithandADPPCPayrollforWindows-DatabaseGrouping.pps

This should give you a starting point

Let me know how this works out.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[CharlesCook]

Just to put a finer point on this part:

"Any other data you need should be made "Query Only" then use a Derived Field and SUM(DISTINCT ) if numeric or FIRST( ) if not numeric(really any agg function that does not return a numeric will work)
"

If there is only one value for a bit of data like First Name for example you could make it "include in report" but then you MUST turn it on in Database Grouping or you would get an error.

If you were to "Include in report" a bit of data that had many different values like PLAN_TYPE and then turn it on in Database Grouping to get rid of the error you would get a record for each different PLAN_TYPE value.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[CharlesCook]

How did this work out?

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining