pranalli - Viewing most current record only (LOOK HERE) !!!!!

[CharlesCook]

I can't respond in your threed for some reason.

You need to Effective Date the JOB record. Look at this PPT for more info:

http://charlescook.com/PPS/ADP/ReportSmith/Enterprise/Introduction-To-Effective-Dated-Tables.pps

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[pranalli]

Charles,

Thank you for your response, however that route doesn't quite work, let me explain.

I need to see the last action which was a "PAY" action type or "MER or HIR" action reason. If I effective date the table, it will show me only the people whose LAST action were one of those, but exclude them if they had some sort of intermittent action like a data change.

What I really need is to see all of my employees and the last time they received either a "PAY" or "HIR" action, regardless of what the most recent action may have been if not a PAY or HIR.

Right now, the report is working great with the exception of the fact that I am getting that historical data. I had taken out the effdt of the Job table so that I could view all of the PAY and HIR actions for each employee. Now I need to figure out how to tell Reportsmith to only show me the most recent action date out of this pool of information without excluding those individuals whose last action may not have been a PAY or HIR (for example, may we had to fix their next review date, so they would have a DTA action).

I'm truly at a loss on how I can do this. I thought maybe I could set up a selection criteria that says:

PS.JOB.ACTION_DT is equal to formula SELECT MAX(PS.JOB.ACTION_DT) FROM PRANALLI.PS.JOB

(I know the syntax isn't exact there, I'm just showing the logic). But when I do that I just get errors saying like missing expression.

Your collaboration is greatly appreciated Charles, and of course anyone else too!

 

[pranalli]

In my previous post, ignore the part where I need to see only "MER" or "HIR" action reason -- that is incorrect. Stick to "PAY" or "HIR" action as those encompass all of the various "Action Reasons" that I need.

 

[CharlesCook]

Keep the Selection Critera for Effective Dating the Job table.
Click on this selection and convert it to SQL.
Click on the SQL Selection Critera to get into the SQL editor.
Add to the second part of the SQL AND "PS_JOB"."ACTION" IN ('HIR','PAY')

The SQL Selection for the Effective Dating of JOB will look like this:

("PS_JOB"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "PS_JOB"."EMPLID"
AND "INNERALIAS"."EMPL_RCD_NBR" = "PS_JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EFFDT" = "PS_JOB"."EFFDT")
AND
"PS_JOB"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "PS_JOB"."EMPLID"
AND "INNERALIAS"."EMPL_RCD_NBR" = "PS_JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EFFDT" <= SYSDATE)
[!]AND "PS_JOB"."ACTION" IN ('HIR','PAY')[/!])

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[pranalli]

I'll give it a shot when I have a few minutes and let you know how it goes. Thanks!

 

[pranalli]

I verified the SQL line for line, that seems to cause the exact same problem. It excludes employees whose last action was not either "PAY" or "HIR"

 

[CharlesCook]

My Bad....

("PS_JOB"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "PS_JOB"."EMPLID"
AND "INNERALIAS"."EMPL_RCD_NBR" = "PS_JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EFFDT" = "PS_JOB"."EFFDT")
AND
"PS_JOB"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "PS_JOB"."EMPLID"
AND "INNERALIAS"."EMPL_RCD_NBR" = "PS_JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EFFDT" <= SYSDATE)
[!]AND "INNERALIAS"."ACTION" IN ('HIR','PAY')[/!])

You will lose people without a PAY or HIR.

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[pranalli]

Wow, that worked! I still have one or two duplicates, but that's because of poor data entry on our payroll side -- I can work with that easily.

I don't know if it is outside the scope of what can be explained here, but I'd really like to understand why that worked, but the other method didn't? As far as I can tell, the only difference is that we used an alias instead of the table name?

Also, just FYI, I had to move the right parenthesis next to 'SYSDATE' to after the new line to get it to work right.

 

[CharlesCook]

I can't really explain it very well in a text comment. But it has to do with looking at the inner query (INNERALIAS) and the outer query (PS_JOB). The inner query happens for each record selected by the outer query. So using the INNERALIAS alias kept everything in the context of the sub query.

Anyway it works this way...

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[pranalli]

I get what you're saying.

Anyway, thanks a ton for your help it's greatly appreciated. I just moved into this position, and the previous person was running a report with ALL historical data and then running it through a query in Access to weed out data. Needless to say, it was an inefficient use of the ADP system, and quite prone to error.

My goal was to rid the need for all of that, and I thank you for your help in getting there!

 

[CharlesCook]

Your welcome... Check out my website if you get a chance.

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[FireGeek21]

Pranalli -
I believe I have a solution for you.

I am assuming you have an Oracle database.

First, remove the effective date line from your Report Query - Selections. Next, insert new SQL criteria. Copy the following:

TO_CHAR(J.EFFDT, 'YYYYMMDD') || J.EFFSEQ = (
SELECT MAX(TO_CHAR(iJ.EFFDT, 'YYYYMMDD') || iJ.EFFSEQ)
FROM PS_JOB iJ
WHERE J.EMPLID = iJ.EMPLID
  AND J.EMPL_RCD_NBR = iJ.EMPL_RCD_NBR
  AND ((J.ACTION = 'HIR') OR
       (J.ACTION = 'PAY' AND J.ACTION_REASON = 'MER'))
  AND J.EFFDT <= SYSDATE)

TO_CHAR(J.EFFDT, 'YYYYMMDD') || J.EFFSEQ alters the date and concatenates the Effseq into a numerical string such that you get the true max row. I also included the Action and Action_Reason criteria. ‘MER’ is typically associated with the Action of ‘Pay’. This is indicated thru the use of () as well as the OR and AND operators.

I hope this helps. Feel free to contact me if you have any other questions.

RSGeek
rsgeek@wi.rr.com

 

[pranalli]

Thank you both for your help, it was great learning experience for me. As it turns out though, the report was still dropping some records.

I ended up getting the report within arms length of working perfectly, but ultimately scratched the project for now. I had it showing last increase amount along with the max row of the review date table, but it was still dropping approximately 100 records, and I was unable to determine why. I got ADP on the phone and on my computer to look at it with me, but even they said that this report is so complex they'd have to spend hours working on it and charge me -- but forget that, I like figuring this stuff out myself!

Anyway, what I did for now is create two separate reports. I removed the "last increase amount" and "last increase percentage" fields from my monthly review report (these were the fields that were causing the drop in records), and created it as its own report. I then export them both into Excel and simply use a vlookup to merge the last amount data into my review date report. At least this way I am 100% confident that I am getting all of my records and reliable data.

But bottom line if I am to revisit this issue, I need to somehow create a report where I can bring in the inc_amt and inc_pct fields associated with the most recent actions PAY or PRO (and in some cases, XFR). I tried including "HIR" as one of the actions since I assumed everybody would at least have a "HIR" action, but that didn't help.

 

[pranalli]

Correction to my last paragraph:

But bottom line if I am to revisit this issue, I need to somehow create a report where I can bring in the inc_amt and inc_pct fields associated with the most recent actions PAY or PRO (and in some cases, XFR) but still keep ALL records (meaning every employee should show up on the report regardless of if they've received an increase). I know how to do this by itself, but combined with the 6+ other tables that this report has, it causes problems. I tried including "HIR" as one of the actions since I assumed everybody would at least have a "HIR" action, but that didn't help.