I have received this question a couple of times before and this also came up in the forums today. Lets look at the question first (modified version of the original question to make it more clearer). “I have a dashboard prompt wherein the user can choose any year (using a dropdown). I also have a report which shows sales across all the years (containing a lot of historical data). Now, based on the year that was chosen in the dashboard prompt i would like to restrict the report to only the previous 4 years of the selected date“. Though this question is pretty straight forward, i thought i would blog about it to serve as a point of reference for future questions like this. Just to make it a bit more complex lets also make the number 4 above dynamic. Lets start first with creating a dashboard prompt on 2 columns. One prompt would extract the year from a date field and the other would just let the user enter some numeric data. Make both the columns to set presentation variables promptdate and promptnumber. The first column would have a formula to extract the year from the date.
YEAR(TIMES.CALENDAR_MONTH_START_DATE)
And the second one would just let the user to enter any value. So, in order to achieve that enter the below formula
CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Test’ END
Once this is done, lets create a simple report containing 2 columns. CALENDAR_MONTH_START_DATE and its corresponding SALES.
Now, create a filter on any of the columns and convert that filter to SQL.
Now, in the SQL box enter the below filter formula.
TIMES.CALENDAR_MONTH_START_DATE >= TIMESTAMPADD(SQL_TSI_YEAR, – @{promptnumber}{2}, TIMESTAMP ‘@{promptdate}{2003}-01-01 00:00:00′)
Similarly, create another filter and enter the below formula after converting it to SQL.
TIMES.CALENDAR_MONTH_START_DATE <= TIMESTAMP ‘@{promptdate}{2003}-12-31 00:00:00′
So, what the above filter does is it dynamically varies the window of the filter that is being applied.Notice that even this SQL box can accept presentation variables.The other important thing to note in the formula is the use of negative year values in TIMESTAMPADD function. This will move the year backwards.
Now, if you go to the dashboard prompt and enter 1 and 2006 in the prompts we would get a report containing the dates from 2005. Similarly, if you enter 2 and 2005, it would get a report containing dates from 2003 till 2005.
Again, very simple but can be useful in a lot of situations.
Posts
