ReportSmith - selecting the current row in Benefits 1

[ctreports]

I have tried and tried to do a report that will pull ONLY the current Benefit program row. I have called the support desk at ADP and they said there is no way. Surely, someone has an idea? I would be so grateful. Thank you.

 

[CharlesCook]

What tables are you using?

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[ctreports]

I am using the job, personal_data, employment, and ben_prog_partic tables.

 

[CharlesCook]

What you would need to do is write your own effdt selection critera using EVENT_DT as if it were EFFDT. What you could do is create a report using the JOB table and copy the SQL out of it and modify it to work with the ben_prog_partic table. You really need to know what you are doing to do this. This is where it is worth the money to hire a consultant.

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[ctreports]

Ah - I knew it would be tough..... thanks for your help.

 

[ArtieArt]

You probally using the PS_BEN_PROG_PARTIC TABLE. You want to assign the table an alais of "A". In selection, insert an selection that states:
Data Field EVENT_DT TO FORMULA. Click on the line at the end and copy and paste the below statement.

(SELECT MAX(B.EVENT_DT) FROM PS_BEN_PROG_PARTIC B
WHERE A.EMPLID = B.EMPLID)

This will bring back the most current Event Date which will return the current Benefit Program for that date.




Artie Dunnings
ADTECH Consulting Inc
HRIS Functional & Technical Trainer
Alpharetta, GA

 

[ctreports]

Thank you so much Artie! I will try it.