I had a client report which needed a parameter list to default to the previous closed financial period date. The SSAS cube includes reporting periods into the future which have data entries even if the month has not been ‘closed’ by accounting department. Without a ‘Month Closed’ flag in the cube, the only way to build the parameter list was by querying the date dimension database table.
When adding a date parameter to the report dataset, SSRS will automatically build a hidden dataset which pulls the list of available dates from the cube. For this example, I left that data set alone and it is automatically used in the parameter’s ‘Available Values’ property.
The requirement was to set the ‘Default Value’, so the following query checks the relational table and builds a single MUN for the previous closed accounting month.
The DimTime table is small so the query responds quickly. SSRS is set to use the DateDimMUN for the default value. These settings are changed in the parameter properties, in the ‘Default Values’ tab.
SELECT MAX(FiscalCloseDate) AS FiscalCloseDate , MAX(FiscalMonthKey) AS FiscalMonthKey , '[Time].[Fiscal].[Fiscal Month].&[' + LTRIM(STR(MAX(FiscalMonthKey))) + ']' AS DateDimMUN FROM dbo.DimTime WHERE (FiscalCloseDate < GETDATE()) I will try to write an example using AdventureWorks2008.