Keeping Employees in an accumulator report

[ShaneSQ]

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!

 

[CharlesCook]

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

 

[ShaneSQ]

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

 

[CharlesCook]

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

 

[CharlesCook]

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

 

[ShaneSQ]

Shoot! Of course!

Thanks for your help, that solved it.