Dynamic Column Names in Report based on Date Parameter

[JGirl]

I have a Sales report that takes a date parameter "parm_AsAtDate".

Im my query, I have 1 dimension, and 4 calculated measures:
* Dimension: Product
* Fact: Month-2
* Fact: Month-1
* Fact: MonthToDate
* Fact: YearToDate

The report is run with "parm_AsAtDate" selected at runtime by the user.  For example, selecting a date of 20-MAR-2006 would select only sales up to a certain date.  In this example, the query would contain:
* Fact: Month-2 = Sum of all sales in January 2006
* Fact: Month-1 = Sum of all sales in February 2006
* Fact: MonthToDate = Sum of all sales between 01-Mar-2006 and 20-Mar-2006
* Fact: YearToDate = Sum of all sales between 01-Jan-2006 and 20-Mar-2006

As another example, the report could be run with "parm_AsAtDate" = 31-DEC-2005, in which case:
* Fact: Month-2 = Sum of all sales in October 2005
* Fact: Month-1 = Sum of all sales in November 2005
* Fact: MonthToDate = Sum of all sales between 01-Dec-2005 and 31-Dec-2005
* Fact: YearToDate = Sum of all sales between 01-Jan-2005 and 31-Dec-2005

I have the data side of things sorted out, and working correctly, but what I want now is dynamic column labels.

So...using the previous examples, if I run the report for 20-MAR-2006, I want the Month-2 Column to be labelled "Jan 2006", and if I run the report for 31-DEC-2005, I want the Month-2 column to be labelled "Oct 2005".

The problem is that in the 'report expression' editor for the labels, you only get the option to calculate a 'layout expression' (as opposed to a 'tabular model data item', so no functions like _add_months, extract etc are available)?

Anyone able to help with this?
J

[bdybldr]

Hi JGirl,
Add data items to you query for Month-1, Month-2, etc (The actual dates that should display, not sum of sales)  You can also do this by adding a child tabular model that will store all of your date related list column titles.

Then, click on the list column title you want to change and select the appropriate query item from the dropdown list on the properties tab.

HTH.  Keep us posted.

[JGirl]

Hi.  Thanks for the suggestion, but....

I tried that, but it doesnt display anything in the labels when I run the report (ie. the labels become blank)  :'(

I've also tried:
* formatting the parameter value as a date to only show the month name (doesnt work)
* creating a list (containing one cell) to display each column name, and including a layout component reference to each list in the header (i was getting desperate....still doesnt work)

Im using CRN 1.1 MR3

[bdybldr]

Have you tested the tabular data to see what you get for a result in the query?  Maybe nothing is being returned.  Make sure that data is coming back in your query.  Then we can go from there.