I have tree prompt which is populated with data from time dimension. How can i populate it with last 12 months from current date?
thanks.
Apply a filter along these lines to the Query that the Tree Prompt is based on:
([Development Layer].[Opened Date Dimension].[Opened_Date_Dim_Year] = (extract(year,current_date) - 1)
and [Development Layer].[Opened Date Dimension].[Opened_Date_Dim_Month_Num] >= extract(month,current_date))
OR
([Development Layer].[Opened Date Dimension].[Opened_Date_Dim_Year] = extract(year,current_date)
and [Development Layer].[Opened Date Dimension].[Opened_Date_Dim_Month_Num] < extract(month,current_date))
Even better, make a Model Filter out of this expression (which is the preferred method) and then you can just drag this Model Filter into your report query.
hth.... M