I have a cube with several dimensions one being date. There is a FK from the fact table on paydate to the data dimension. I created an SSRS report against the cube with a parameter being on the date dimension. The user selects the year and the month and gets the pay measure information for that month....which is all good BUT when the user opens the parameter, they need to select the year and month for every year and month contained in the date dimension. I would like to have the parameter built so that the only dates visible to select are dates which are in the fact table. Currently we only have 7 months of data so the selection I'd like to have is 2010 months 8 through 12 and 2011 months 1 through 3. Do I need to build a different dimension or can I filter the parameter with some expression?

I would like to have the parameter built so that the only dates visible to select are dates which are in the fact table. Make the following the parameter definition: SELECT DISTINCT xDate FROM myFactTable Related link: A queried list causes the report server to retrieve a set of values and labels from a dataset when the report is run. When you specify a queried available values list, you select the dataset, the field to use for value, and the field to use for label. When specifying a queried available values list, it is recommended that you create a simplified dataset to be used specifically by the parameter, rather than using a more complicated dataset that is also used by data regions within the report. Using the same dataset for both the valid values list and the data regions in the report might produce unexpected results in the valid values list. http://msdn.microsoft.com/en-us/library/aa337234.aspx Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM

So you're saying that even though I'm processing a cube, go and get the parameter list from the relational tables? OK...so I need to add another datasource to the report so I can query the relational tables. So this had been the generated selection for the parameter list (which gave me all year-month combinations): WITH MEMBER [Measures].[ParameterCaption] AS [Pay Date].[Year-Month].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Pay Date].[Year-Month].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Pay Date].[Year-Month].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Pay Date].[Year-Month].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@OperatorsOperatorHierarchy, CONSTRAINED) ) ON COLUMNS FROM [Pays Data]) So now I change it to go against the new rdbms dataset by pointing to the new datasource instead of the cube and adding the query: SELECT DISTINCT DimDate.CalendarYearMonth FROM FactPays INNER JOIN DimDate ON FactPays.PayDate = DimDate.DateKey ORDER BY DimDate.CalendarYearMonth DESC So I try to preview and get "The report parameter 'PayDateYearMonth' uses the field 'ParameterValue' in a dataset reference but the dataset 'PayDateYearMonth' does not contain that field. I got that fixed and then all the subsequent parameters had issues....so I only left in the top two parameters - one which selected a department via a hierarchy in the cube and this date parameter. And when I try to preview that simplified report, that fails on: query execution failed for dataset 'GetPayments' Query(1,628) the restrictions imposed by the constrained flagin the strtoset function were violated. Here is the dataset that failed with the above error: SELECT NON EMPTY { [Measures].[Pay Amount] } ON COLUMNS, NON EMPTY { ([Locations].[Location ID].[Location ID].ALLMEMBERS * [Pay Date].[Year-Month].[Year-Month].ALLMEMBERS * [Operators].[Operator Name].[Operator Name].ALLMEMBERS * [Adjustment Codes].[Description].[Description].ALLMEMBERS * [Pay Date].[Date Name].[Date Name].ALLMEMBERS * [Customer Table].[Customer Number].[Customer Number].ALLMEMBERS * [Customer Table].[Customer Name].[Customer Name].ALLMEMBERS * [Customer Table].[Account Classification].[Account Classification].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@PayDateYearMonth, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@OperatorsOperatorHierarchy, CONSTRAINED) ) ON COLUMNS FROM [Pays Data])) WHERE ( IIF( STRTOSET(@OperatorsOperatorHierarchy, CONSTRAINED).Count = 1, STRTOSET(@OperatorsOperatorHierarchy, CONSTRAINED), [Operators].[OperatorHierarchy].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS So I'm wondering if using a relational query to filter my parameter is the right way to go. If it is, I'm definately missing something in how to make it work. If I remove the PayDateYearMonth parameter, the above selection works fine...so its the way I've used the relational query and result into the above statement. One more funny thing, in query designer, everything worked fine.