×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Creating a benefits report to show 1 row per employee

Creating a benefits report to show 1 row per employee

Creating a benefits report to show 1 row per employee

(OP)
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

RE: Creating a benefits report to show 1 row per employee

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

RE: Creating a benefits report to show 1 row per employee

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

RE: Creating a benefits report to show 1 row per employee

How did this work out?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close