Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Derived Fields by Effective Date of Table, as it were

Status
Not open for further replies.

vlfox

Technical User
Oct 13, 2005
40
US
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 !
 
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)
 
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.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top