×
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 Fields by Effective Date of Table, as it were

Derived Fields by Effective Date of Table, as it were

Derived Fields by Effective Date of Table, as it were

(OP)
I have some reports related to Emp Counts that I change the Report Variable each time from 1/31, 2/28, etc, etc to go with the Selection of "Effective Date of Table".

Rather than changing the Report Variable anywhere from 3 to 12 times, though, I just wondered if there's a way to do a Derived Field and make a column that would pull each accordingly:  Jan, Feb, etc.

ThanX & Happy New Year to ALL !

RE: Derived Fields by Effective Date of Table, as it were

If you can base it on the system date when the report is run, you should be able to do something.  If this is an ADP SQL database, you can use the @now function in date/time function.  Then, you can use the @month command.  So, your first calc may be @(month(@now))......then, you can use the decode command to return what you want.  As an example, it may be @Decode(calc1,value1)

RE: Derived Fields by Effective Date of Table, as it were

Not sure what system/database you are using, but this can definitely be done in an Enterprise environment.  What I do is simply choose "none" for the effective date option, and then make the fields I want "query only".  

Then I'd just create derived fields looking for each month
January Example (for Oracle):

DECODE(TO_CHAR(DATE_FIELD,'MM'),'01',DATA_YOU_WANT)

You still may get duplicates if there is more than one entry in the month, you could either try to concoct a select max statement to get rid of the dups or just deal with them in Excel, which sometimes I just find easier and more reliable to do.  

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