I have a report that I built in SSRS 2008 which reference an analysis services cube as the dataset. There are four parameters in the report: DEPARTMENT, DISCIPLINE, YEAR, MONTH. I was initially having issues with the parameters for DEPARTMENT and DISCIPLINE but managed to work through those. I'm now encountering major problems with the YEAR and MONTH parameters. Here is the MDX the query designer generates: SELECT NON EMPTY { [Measures].[Employee Hours] ,[Measures].[NewHourPercentage] } ON COLUMNS ,NON EMPTY { [Department].[Discipline].[Discipline].ALLMEMBERS* [Department].[Department].[Department].ALLMEMBERS* [Employee].[Employee Full Name].[Employee Full Name].ALLMEMBERS* [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS* [Employee].[Employee Level].[Employee Level].ALLMEMBERS* [Employee Hours Time Category].[Utilization Category].[Utilization Category].ALLMEMBERS* [Employee Hours Time Category].[Time Category].[Time Category].ALLMEMBERS } DIMENSION PROPERTIES MEMBER_CAPTION ,MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT - { [Employee Hours Time Category].[Utilization Category].&[PTO] } ON COLUMNS FROM ( SELECT StrToSet (@TimeMonth ,CONSTRAINED ) ON COLUMNS FROM ( SELECT StrToSet (@TimeYear ,CONSTRAINED ) ON COLUMNS FROM ( SELECT StrToSet (@DepartmentDepartment ,CONSTRAINED ) ON COLUMNS FROM ( SELECT StrToSet (@DepartmentDiscipline ,CONSTRAINED ) ON COLUMNS FROM [OLSON BI] ) ) ) ) ) WHERE ( IIF ( StrToSet (@TimeYear ,CONSTRAINED ).Count = 1 ,StrToSet (@TimeYear ,CONSTRAINED ) ,[Time].[Year].CurrentMember ) ,IIF ( StrToSet (@TimeMonth ,CONSTRAINED ).Count = 1 ,StrToSet (@TimeMonth ,CONSTRAINED ) ,[Time].[Month].CurrentMember ) ) CELL PROPERTIES VALUE ,BACK_COLOR ,FORE_COLOR ,FORMATTED_VALUE ,FORMAT_STRING ,FONT_NAME ,FONT_SIZE ,FONT_FLAGS; When I try to run the report, I get the infamous "The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated". Now, for the two parameters in question, I created two new datasets that are comprised of just the year and month respectively. When I run the report, both of them appear in the drop down box as they appear in the filter expression box in query designer, so I'm ASSUMING they're being passed in to the report in the proper format. If I remove the CONSTRAINED flag, a completely different error is thrown and the query designer forces me to choose a default value for al parameters which I don't want. I had no idea working with parameters and MDX and SSRS was so difficult... If anyone has any ideas, it would be appreciated. A. M. Robinson

First off, the generated MDX is horrible, I only write from scratch now. ;-) I would re-write your MDX as: SELECT { [Measures].[Employee Hours] ,[Measures].[NewHourPercentage] } ON COLUMNS ,NONEMPTY( { [Department].[Discipline].[Discipline].ALLMEMBERS* [Department].[Department].[Department].ALLMEMBERS* [Employee].[Employee Full Name].[Employee Full Name].ALLMEMBERS* [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS* [Employee].[Employee Level].[Employee Level].ALLMEMBERS* [Employee Hours Time Category].[Utilization Category].[Utilization Category].ALLMEMBERS* [Employee Hours Time Category].[Time Category].[Time Category].ALLMEMBERS }, { [Measures].[Employee Hours] ,[Measures].[NewHourPercentage] }) ON ROWS FROM (SELECT -{[Employee Hours Time Category].[Utilization Category].&[PTO]} ON COLUMNS FROM (SELECT StrToSet(@DepartmentDepartment,CONSTRAINED) ON COLUMNS FROM (SELECT StrToSet(@DepartmentDiscipline,CONSTRAINED) ON COLUMNS FROM [OLSON BI]))) WHERE ( StrToSet(@TimeMonth,CONSTRAINED), StrToSet(@TimeYear,CONSTRAINED) ) You say you are returning year and month, are you using the unique name of the member as the value in the parameter? If you are using StrToSet it creates tuple set and if it is constrained, it will make sure this tuple is valid. So you should be passing in something along the lines of: [Dim Date].[Month of Year].&[1] for month [Dim Date].[Calendar Year].&[2011] for year Take a look at any autogenerated datasets for your other parameters. They are likely hidden. Cheers! Ryan

I'm actually passing in "Calendar 2011" and "July 2011" - which is what is populated in the drop down for both of these. The other issue is that at some point this report is going to be automated. What will need to happen is that on a certain date this report will run. There will be several data driven subscriptions - each one of those subscriptions will be for a DISCIPLINE (there will be a default for discipline and department). The month parameter will need to be the previous month and the year will need to be the current yer (these would be the default across all reports). Given how complex passing in parameters to an MDX dataset and all the manipulations involved, I have no idea how to get the report to wrok passing in parameters ad hoc, much less using default. How do you tell a report to use members of a dimension as available values for a parameter but at the same time pass in something completely differnt? Also, when right-clicking on my data source, I am not given the option to view Hidden Datasets...that doesn't seem right to me. I am really no good with MDX at all! A. M. Robinson

Ryan: I wanted to try a different approach real quick. I went ahead and removed all of the CONSTRAINED flags from the MDX givnig me this: SELECT NON EMPTY { [Measures].[Employee Hours] ,[Measures].[NewHourPercentage] } ON COLUMNS ,NON EMPTY { [Department].[Discipline].[Discipline].ALLMEMBERS* [Department].[Department].[Department].ALLMEMBERS* [Employee].[Employee Full Name].[Employee Full Name].ALLMEMBERS* [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS* [Employee].[Employee Level].[Employee Level].ALLMEMBERS* [Employee Hours Time Category].[Utilization Category].[Utilization Category].ALLMEMBERS* [Employee Hours Time Category].[Time Category].[Time Category].ALLMEMBERS } DIMENSION PROPERTIES MEMBER_CAPTION ,MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT - { [Employee Hours Time Category].[Utilization Category].&[PTO] } ON COLUMNS FROM ( SELECT StrToSet(@TimeMonth) ON COLUMNS FROM ( SELECT StrToSet(@TimeYear) ON COLUMNS FROM ( SELECT StrToSet(@DepartmentDepartment) ON COLUMNS FROM ( SELECT StrToSet(@DepartmentDiscipline) ON COLUMNS FROM [OLSON BI] ) ) ) ) ) WHERE ( IIF ( StrToSet(@TimeYear).Count = 1 ,StrToSet(@TimeYear) ,[Time].[Year].CurrentMember ) ,IIF ( StrToSet(@TimeMonth).Count = 1 ,StrToSet(@TimeMonth) ,[Time].[Month].CurrentMember ) ) CELL PROPERTIES VALUE ,BACK_COLOR ,FORE_COLOR ,FORMATTED_VALUE ,FORMAT_STRING ,FONT_NAME ,FONT_SIZE ,FONT_FLAGS; But now when I click OK on Query Designer, I get an error: TITLE: Microsoft Visual Studio ------------------------------ Query preparation failed. ------------------------------ ADDITIONAL INFORMATION: An MDX expression was expected. An empty expression was specified. (Microsoft SQL Server 2008 R2 Analysis Services) ------------------------------ BUTTONS: OK ------------------------------ I'm sorry, but isn't my query the EXACT same thing that parsed just fine before, except without the CONSTRAINED flag? Why does it now not like the expression?!? And if I click the "Click to Execute Query" link, it asks me for a default value for all parameters. If I don't specify a default for all four, the dialog won't allow me to execute the query. When I sustituted your query in, it parsed fine and results returned fine, but again, when I ran the report I got the same STRTOSET / CONSTRAINED error. I'm getting confused as to what you mean by how I should be passing in my month and year?? Could you be a little more specific as to what exactly I need to do to get this report to just take a year and a month...? The year and month wityhin the cube are not formatted like a simple "2011" or "July". They appear in the cube as I showed in my last post - "Calendar 2011" and "July 2011". I am really struggling with this whole thing... A. M. Robinson

Sorry, misjudged the data because of the name. Can I get a little more info from you? What are the names of the members where the year and month are stored in the cube? Passing in variables can be a little wacky, but it's really not that much different from SQL. When you are in the query designer you do have to give default values. When you finish designing your query, you can go to the parameters section and delete the defaults or set better ones. The form that STRTOSET() expects is: "[Dimension Name].[Member Name].&[Key Value]" or even "[Dimension Name].[Member Name].[Hierarchy Name].[Name Value]", although this is less efficient. Browse your cube and find an example member, then drag it into the SSMS window- you should see the form I have just described. Ryan