×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

derived field to pull check gross in range of dates

derived field to pull check gross in range of dates

derived field to pull check gross in range of dates

(OP)
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 )

?

RE: derived field to pull check gross in range of dates

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

RE: derived field to pull check gross in range of dates

(OP)
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'))))))
 

RE: derived field to pull check gross in range of dates

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

RE: derived field to pull check gross in range of dates

(OP)
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?

RE: derived field to pull check gross in range of dates

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

RE: derived field to pull check gross in range of dates

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

THANK YOU - you rock!!!!!

RE: derived field to pull check gross in range of dates

Thanks feel free to give me a star.

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close