Include unmatched records 1

[funscrapn]

I am trying to create a report that will list the employees general deductions from the GENL_DEDUCTION table and the deduction from the employees pay check from the PAY_DEDUCTION table. The problem is that if the deduction is not listed on the PAY_DEDUCTION table (because nothing came out of the employees check) I need it to say $0.
So, in my selection criteria I have:
Any of the following apply:
1. data field DED.DEDCD (not sure if that's right) is equal to formula (SELECT MAX(A.PAY_END_DT) FROM PS_PAY_DEDUCTION A WHERE A.DEDCD = PS_GENL_DEDUCTION.DEDCD AND A.PAY_END_DT < PS_GENL_DEDUCTION.EFFDT)
2. data field DEDCD is null

Thanks for your input!!!

 

[CharlesCook]

data field DED.DEDCD is equal to formula (SELECT MAX(A.PAY_END_DT) FROM PS_PAY_DEDUCTION A WHERE A.DEDCD = PS_GENL_DEDUCTION.DEDCD AND A.PAY_END_DT < PS_GENL_DEDUCTION.EFFDT)

DED.DEDCD is never going to equal MAX(A.PAY_END_DT)


This is going to be a very complex report. It is critical that you understand how to link the tables together. This is where you must be able to understand the data dictionary entity relationship diagrams.

First things first. Your tables and links should look like this. The links are in green you need ALL of them.

Start a NEW report with these tables and links and post back here and we will pick it up from there.


CharlesCook.com
Specializing in ReportSmith Training and Consulting

 

[funscrapn]

This is a payroll client (not autolink) so instead of the AL tables I use PAY_CHECK AND PAY_DEDUCTION. I can only check 'unmatched records' from one table. When I try to check it for multiple tables (i.e. PAY DEDUCTION and GENL_DEDUCTION) I get the following error message:
ORA-01417: a table may be outer joined to at most one other table.

Just to try to get this working I've only started with 3 tables: PAY_CHECK, PAY_DEDUCTION and GENL_DEDUCTION.
I did figure out last night the error in my formula, and fixed it to the same that you gave me. I'm still not getting any records. It does not seem to matter what table I have the check mark for 'unmatched records' - I still get no records found.

 

[CharlesCook]

Your correct. Lets try to get this configuration to work then we can go after the check.

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[funscrapn]

Hi,

I have the report set up as follows:
PERSONAL_DATA is linked to JOB by EMPLID
JOB is linked to GENL_DEDUCTION by EMPLID and EMPLID_RCD_NBR.
JOB is linked to PAY_CHECK by EMPLID and EMPLID_RCD_NBR
PAY_CHECK is linked to PAY_DEDUCTION by COMPANY, LINE_NBR, OFF_CYCLE, PAGE_NBR, PAY_END_DT, PAYGROUP, SEP_CHK
GENL_DEDUCTION is linked to PAY_DEDUCTION by DEDCD

I have to add PAY_CHECK in order to link to the employee. I can check the box 'unmatched records' between the GENL_DEDUCTION and PAY_DEDUCTION, but as you can see, I can't check it for EMPLID and EMPL_RCD_NBR.

 

[CharlesCook]

Christine,

Can you give me a buzz in my office I want you to try something for me. I don't have access to ReportSmith right now or I would just try it myself.

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[pongo42]

Hi, I work with ADP PCPW, and we can't figure out how to get reports where some of the employees have, for example, an additional earning and some don't - we want records for all, but it always kicks out the ones who don't have any additional earnings - is there a basic step to get all? I've read the post, but can't find how to apply it.

 

[funscrapn]

You need a derived field. It will be something like this.

SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'B' ,"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT",0 ))

This says that when the earnings code is 'B' put the amount in the column. If there's no amount enter '0'.

 

[pongo42]

Thanks for the tip - I tried this - but, we want all the additional earnings that are set up as permanent info - in table - REPORTS.V_ADDL_EARNS (not a checkview table)along with all other employees who have nothing set up in this tab. Doing it this way for this table doesn't seem to work like in checkview. Any ideas?