Derived Fields by Effective Date of Table, as it were

[vlfox]

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 !

 

[IamnotCharles]

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)

 

[pranalli]

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.