Sometimes I need to create a dashboard prompt that shows only the most recent x periods. I like to make this dynamic so that the values are not hard-coded and the prompt can survive when new data appears in the warehouse.
The best way I have found to acheive this is to create the prompt based on a logical sql query and make use of the RCOUNT function to get only x rows of data. I include a fact measure in the query so that only valid periods are returned from the database.
Here is an example which gets the most recent 6 months where fact data exists:
Select q1.month from
(Select “Date”.”Month” as month,
“Fact Table”.”Measure Name” as join_forcer,
RCOUNT(“Date”.”Month”) as row_count
from “Subject Area Name”
where RCOUNT(“Date”.”Month”) <=6
order by “Date”.”Month” desc
I think you’ll agree this is a nice, simple concept.
I also use almost identical sql to set the default value of the prompt to the most recent period. Simply change the <=6 to =1 to achieve that.