derived field to pull check gross in range of dates 1

[ttrsnoob]

Is it possible to write a derived field that will pull a range of checks and give the check gross?

something like...

DECODE ( CHECK DATE , CHECK DATE RANGE , CHECK GROSS )

?

 

[CharlesCook]

What you can do is put an SQL Select in a derived field to do this. THIS IS A VERY VERY ADVANCED TECHNIQUE. You really need to know SQL to do this. I don’t currently have access to ReportSmith so I can’t create or debug this for you.

Some things to know:
Your SQL must be enclosed in parens
Watch out for unprintable characters (like tab, new line etc).
If you code your SQL in some other editor then the derived field editor.
You will need to link your derived field SQL to the fields in your report.
WHERE
(
(“DERIVED_FIELD”.”EMPLID” = “JOB”.”EMPLID”)
AND
(“DERIVED_FIELD”.”EMPL_RCD#” = “JOB”.”EMPL_RCD#”)
AND
(….
Your SQL can ONLY return 1 Value
Use SUM()

I hope this helps a bit. If in doubt you may need to contract this out.

Charles Cook

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[ttrsnoob]

Thanks Charles - This is what I put together. When I test it - it says derived field ok but when I go to run the report it says SQL command not properly ended. I know you said you would not be able to debug it or test it but is there anyone else reading this who might venture a guess? Thanks


SUM((SELECT
TOTAL_GROSS."CK_GROSS"
FROM
PS_AL_CHK_DATA "TOTAL_GROSS"
WHERE
("TOTAL_GROSS"."EMPLID" = "PS_AL_CHK_DATA"."EMPLID")
AND
("TOTAL_GROSS"."EMPL_RCD_NBR" = "PS_AL_CHK_DATA"."EMPL_RCD_NBR")
AND
(TOTAL_GROSS."CK_GROSS" =
(SELECT MAX("INNERALIAS"."CK_GROSS")
FROM
PS_AL_CHK_DATA "INNERALIAS"
WHERE "INNERALIAS"."EMPLID" = "TOTAL_GROSS"."EMPLID"
AND "INNERALIAS"."EMPL_RCD_NBR" = "TOTAL_GROSS"."EMPL_RCD_NBR"
AND "INNERALIAS"."CK_GROSS" = "TOTAL_GROSS"."CK_GROSS"
AND "INNERALIAS"."CHECK_DT" BETWEEN (TO_CHAR ('MM-DD-YYYY','01-01-2009')) AND (TO_CHAR('MM-DD-YYYY','04-27-2009'))))))

 

[CharlesCook]

I would start with a little more basic query:

(
SELECT
SUM(TOTAL_GROSS."CK_GROSS") AS GROSS
FROM
PS_AL_CHK_DATA "TOTAL_GROSS"
WHERE
(
("TOTAL_GROSS"."EMPLID" = "PS_AL_CHK_DATA"."EMPLID")
AND
("TOTAL_GROSS"."EMPL_RCD_NBR" = "PS_AL_CHK_DATA"."EMPL_RCD_NBR")
)
)

Charles@CharlesCook.com

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[ttrsnoob]

ok that works perfectly to grab the total of all the checks. How do I limit it to a range of checks? for example just the checks from this year?

 

[CharlesCook]

Something a bit like this should work:

(
SELECT
SUM(TOTAL_GROSS."CK_GROSS") AS GROSS
FROM
PS_AL_CHK_DATA "TOTAL_GROSS"
WHERE
(
("TOTAL_GROSS"."EMPLID" = "PS_AL_CHK_DATA"."EMPLID")
AND
("TOTAL_GROSS"."EMPL_RCD_NBR" = "PS_AL_CHK_DATA"."EMPL_RCD_NBR")
AND
("PS_AL_CHK_DATA"."CHECK_DT" BETWEEN 01/01/2009 AND 12/31/2009)
)
)

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[ttrsnoob]

May I refer to you as Master Yoda from now on?

THANK YOU - you rock!!!!!

 

[CharlesCook]

Thanks feel free to give me a star.

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting