Submitted by Vijay Pamidi
This article shows how you can enter the year and month in a text box prompt and have the report display the rolling 12 months based on the value entered in the prompt.
This is particularly useful when there is a large amount of data in the time dimension, which makes selecting a value from a drop down or list prompt cumbersome.
For example, if a user enters 2012 Dec in the prompt, the report output should display data from 2012 Jan to 2012 Dec.
Open Report Studio and select any package which is a cube and has a Date Dimension. In my cube Year Month is in YYYY Mon format.
Select a Crosstab Object and drag a Measure (ex : Quantity or Revenue) on the row side of crosstab.
Drag a Query Calculation from the Tool Box to the column side of the crosstab.
Enter the name Rolling Months and select Other Expression. Click OK.
Enter the expression below in the Expression Editor and click OK.
tail(periodsToDate ([Sample Cube].[All Dates].[All Dates].[All Dates], item(filter([Sample Cube].[All Dates].[All Dates].[Month], caption([Sample Cube].[All Dates].[All Dates].[Month]) = #prompt('Month', 'string', '2011 May')#),0)),12)
Explanation: Working from the inside outward, the filter function is used to return a month member based on the caption which is equated to a prompt macro. The prompt macro provides a default value which generates a text box prompt instead of a drop down prompt. The item function ensures that only one member at the index of 0 (the first member in the set) is returned. The periodsToDate function returns all month members up to the member selected by the prompt at the All Dates level so that we can span across years. The tail function trims the member set to just 12.
The report design should appear as below.
Run the report. Enter 2012 Dec as a value, or whatever caption is appropriate for your data. Click OK.
The report output should appear as shown below.
Follow @basupp on Twitter
for updates, news, articles and videos
related to Business Analytics Support.