×
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

Keeping Employees in an accumulator report

Keeping Employees in an accumulator report

Keeping Employees in an accumulator report

(OP)
SO I'm writing a report that needs to list all my terminated employees,(selected FOR in the selections) and tell me whether they have any thing accumulated in a couple of accumulators.

I notice that the link:
 V_EMPLOYEE.FILE# = V_YTD_ACCUMULATOR.FILE#

Causes those employees with nothing in their accumulators to be removed from the report. If I remove the link, All the terminated employees are shown, but my derived tables become useless (of course!). Is there a way to "selectively link" these, so my employees without anything in their accumulator table remain on the report?

Thanks!

RE: Keeping Employees in an accumulator report

Whenever you do a link that uses FILE_NBR you also need to use PAYGROUP.

When you link tables you are saying that in TABLE_A there will be a record with a FILE_NBR and in TABLE_B there will be a record with the same FILE_NBR.

AND

In TABLE_A there will be a record with a PAYROUP and in TABLE_B there will be a record with the same PAYGROUP.

If there is no match in TABLE_B the record in TABLE_A is dropped.  To get around this you need to turn on "Include Unmatch Records" for TABLE_A for ALL links between to two tables.

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

RE: Keeping Employees in an accumulator report

(OP)
Thanks for your time,

I don't understand what you are saying about the file_nbr and PAYGROUP, I can't find this field or table (but I now suspect it's neither!). Please alleviate my curiosity if you can.

The "Include Unmatched Records" thing sounded very promising, but alas, yeilded no change.


Cheers,

Shane


SQL text if it helps:
SELECT
@DECODE(REPORTS.V_EMPLOYEE.COMPANYCODE,'T4D','WAL','K89','WCC','QH6','ICC','CN5','DCC','578','CT','8OY','PG'),  SUM(DISTINCT  @DECODE(REPORTS.V_YTD_ACCUMULATOR.SPECIALACCUMCODE,'92',REPORTS.V_YTD_ACCUMULATOR.SPECIALACCUMAMT,0) ) ,  SUM(DISTINCT  @DECODE(REPORTS.V_YTD_ACCUMULATOR.SPECIALACCUMCODE,'94',REPORTS.V_YTD_ACCUMULATOR.SPECIALACCUMAMT,0) ) , REPORTS.V_EMPLOYEE.DATE1, REPORTS.V_EMPLOYEE.FIRSTNAME, REPORTS.V_EMPLOYEE.LASTNAME, REPORTS.V_EMPLOYEE.TERMINATIONDATEROE, REPORTS.V_EMPLOYEE.TERMINATIONREASON
FROM
REPORTS.V_YTD_ACCUMULATOR, REPORTS.V_EMPLOYEE
WHERE
(((REPORTS.V_EMPLOYEE.STATUS = 'T') AND
  ((REPORTS.V_YTD_ACCUMULATOR.SPECIALACCUMCODE IS NULL) OR
  (REPORTS.V_YTD_ACCUMULATOR.SPECIALACCUMCODE IS NOT NULL))))
AND
(REPORTS.V_EMPLOYEE.COMPANYCODE = REPORTS.V_YTD_ACCUMULATOR.COMPANYCODE(+) ) AND (REPORTS.V_EMPLOYEE.FILE# = REPORTS.V_YTD_ACCUMULATOR.FILE#(+) )
GROUP BY
REPORTS.V_EMPLOYEE.DATE1, REPORTS.V_EMPLOYEE.FIRSTNAME, REPORTS.V_EMPLOYEE.LASTNAME, REPORTS.V_EMPLOYEE.TERMINATIONDATEROE, REPORTS.V_EMPLOYEE.TERMINATIONREASON, 1
HAVING
(((( SUM(DISTINCT  @DECODE(REPORTS.V_YTD_ACCUMULATOR.SPECIALACCUMCODE,'92',REPORTS.V_YTD_ACCUMULATOR.SPECIALACCUMAMT,0) ) ) > 0) OR
(( SUM(DISTINCT  @DECODE(REPORTS.V_YTD_ACCUMULATOR.SPECIALACCUMCODE,'94',REPORTS.V_YTD_ACCUMULATOR.SPECIALACCUMAMT,0) ) ) > 0)))
ORDER BY
REPORTS.V_EMPLOYEE.LASTNAME

RE: Keeping Employees in an accumulator report

I'm sorry this is PC Payroll not Enterprise.  I need to think about it and get back to you.

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

RE: Keeping Employees in an accumulator report

You SQL helped.

Go to Tools then Selections and delete the following:

(REPORTS.V_YTD_ACCUMULATOR.SPECIALACCUMCODE IS NULL)
OR
(REPORTS.V_YTD_ACCUMULATOR.SPECIALACCUMCODE IS NOT NULL)
They don't do anything.


Go to Tools then Database Grouping and get rid of ALL the Selections
This is what is getting you.


Then let me know if that helpes.

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

RE: Keeping Employees in an accumulator report

(OP)
Shoot! Of course!

Thanks for your help, that solved it.

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