I developed SSRS 2008 reports based on SSAS olap cubes and I face the following situation : Imagine that the Date Dimension has set the default member by an ALTER CUBE statement to the last day where a certain measure has values. Let's say in my case the result of the default member is 07/06/2011. Than I use a parametrised report which has among other parameters a multivalue parameter called @LocalDateCalendar based on the date dimension. The relevant part of the query - I do take into account what the designer generates - looks like : SELECT NON EMPTY { [Measures.[...] } ON COLUMNS, NON EMPTY {... } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM FROM ( SELECT ( STRTOSET(@LocalDateCalendar, CONSTRAINED) ) ON COLUMNS FROM [MISDBCC])) WHERE ( IIF( STRTOSET(@LocalDateCalendar, CONSTRAINED).Count = 1, STRTOSET(@LocalDateCalendar, CONSTRAINED), [Local Date].[Calendar].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS ( I omitted other parameters , etc. ) The big issue is the following : If the user selects more than one day - where one of the day is the default one 07/06/2011 - then only the results for the default 07/06/2011 and the other dates are ignored. Also choosing All, only the results for the 07/06/2011 are displayed. You are kindly asked to help me solving this issue. Thank you a lot. Best regards, Mihai

Hi Mihai, It seems the MDX is not correct for multiple value parameter, it should be FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!LocalDateCalendar.Value,",") & "}', CONSTRAINED) ) ON COLUMNS not SELECT ( STRTOSET(@LocalDateCalendar, CONSTRAINED) ) ON COLUMNS For more information about multiple value parameter in MDX, please see Robert's reply at this thread http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/cf0ef6ba-d128-4acf-899d-48df71c60296 Thanks, Challen FuPlease remember to mark the replies as answers if they help and unmark them if they provide no help.

Hi Challen Fu Thank you a lot for your answer. I changed the data source to ole db 10 for AS, and rewritten the query as displayed bellow : = " SELECT NON EMPTY " +" { " +" [Measures].[...] " +" } ON COLUMNS " +" ,NON EMPTY " +" { " +" [...].[...].[...].ALLMEMBERS " +" ,[...].[...].[...].ALLMEMBERS " +" ,[...].[...].[...].ALLMEMBERS " +" ,[...].[...].[...].ALLMEMBERS " +" } " +" DIMENSION PROPERTIES MEMBER_CAPTION ,MEMBER_UNIQUE_NAME ON ROWS " +" FROM ( SELECT StrToSet (@A ,CONSTRAINED ) ON COLUMNS " +" FROM ( SELECT StrToSet (@T ,CONSTRAINED ) ON COLUMNS " +" FROM ( SELECT StrToSet (@D ,CONSTRAINED ) ON COLUMNS " +" FROM ( SELECT StrToSet (@C ,CONSTRAINED ) ON COLUMNS " +" FROM ( SELECT STRTOSET ( '{" & Join( Parameters!LocalDateCalendar.Value,",") & "}', CONSTRAINED ) ON COLUMNS " +" FROM [M] ))))) " +" WHERE " +" ( " +" IIF " +" ( " +" StrToSet(@T,CONSTRAINED).Count = 1 " +" ,StrToSet " +" (@T " +" ,CONSTRAINED " +" ) " +" ,[...].[...].CurrentMember " +" ) " +" ) " +" CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS ;" Unfortuneately the result is the same as previously described : If the user selects more than one day - where one of the day is the default one 07/06/2011 - then only the results for the default 07/06/2011 date are displayed and the other dates are ignored. Also choosing All, only the results for the 07/06/2011 are displayed. ( I expected her the aggregated results for the two days I selected). So the case is the combination of multi value parameter from a dimension that has set the default member. Will it possible to reproduce the case in Adventureworks setting the default member on Date dimension and then build a parametrized rapport with a Date and one measure ? I really do not understand this strange behaviour both on a SSAS - accepting the default generated code in query analyzer for a multi value parameter and the one you gave me based on Robert's reply. Hope you will help me clarify this issue, as it applies for all the reports in the present solution and I'm really under pressure. Thank you. Best regards, Mihai

Thank you very much for your feed-back and herby the following answer : SSMS and Excel pivot tables display the right results. When I select All and separately the two days , I got the right results. When I select both days I got the wrong results , that is the same result as for the default member ( 07/06/2011 in my case). I deleted the default member - based on a Tail(Exists([Local Date].[Day].MEMBERS, [Local Date].[Current Day].&[True]),1).Item(0) - and the results are the same. So quite normally, the 'unexpected result' is not due to the default member on date dimension. I traced the query in SQL Server Profiler and acutally both dates are actually part of the parameter : ( as showed bellow) <Parameter> <Name>LocalDateCalendar</Name> <Value xsi:type="xsd:string">{ [Local Date].[Calendar].&amp;[20110606],[Local Date].[Calendar].&amp;[20110607] }</Value> </Parameter> <Parameter> I even used both the ole db version of the report where I used the : +" FROM ( SELECT STRTOSET ( '{" & Join( Parameters!LocalDateCalendar.Value,",") & "}', CONSTRAINED ) ON COLUMNS " but the results are the same. It seems like only one value of the fields - either I use Fields!XXX.FormattedValue or Fields!XXX.Value - is binded in the tablix although the data set seems ok. Do you have any suggestion of what I do wrong and how I can solve this issue ? Thank you a lot for your answer. Best regards, Mihai

Hi I finally found a work-around but let me summarize once againg the issue : a default member on a dimension, let's say the Date dimension. report uses a parameter and it's default member is based on it ( typically the last date where the system has a non empty measure). user picks up the default member for the date dim. and another date - in order to see the aggregated results - but only the results for the default member are displayed. mdx query is the same whether you use AS or ole db for as . the query includes both days but the cube retrieves only the results for the default member. When I eliminate the default member on the dimension the results are right. My conclusion was : There is a cube// dim. bug and I worked on the following work-around : Delete the default member for the dimension , create different roles and create the default member only for one role , as I still need it for some reports. Then use different connections for the reports data sources depending on the roles ( Roles= directive). And last but not least at the report level I created data sets for default member for the Date parameter involved , based on the previous logic for defining the default member at dimension level. ( Tail(Exists (...) construction . I must frankly admit , that I missed so much a feed-back, especially because I was convinced that someone must has been struggling with this kind of issue before. Hope that Challen Fu will log // reproduce // further investigate this issue. Best regards, Mihai

Hi I finally found a work-around but let me summarize once againg the issue : a default member on a dimension, let's say the Date dimension. report uses a parameter and it's default member is based on it ( typically the last date where the system has a non empty measure). user picks up the default member for the date dim. and another date - in order to see the aggregated results - but only the results for the default member are displayed. mdx query is the same whether you use AS or ole db for as . the query includes both days but the cube retrieves only the results for the default member. When I eliminate the default member on the dimension the results are right. My conclusion was : There is a cube// dim. bug and I worked on the following work-around : Delete the default member for the dimension , create different roles and create the default member only for one role , as I still need it for some reports. Then use different connections for the reports data sources depending on the roles ( Roles= directive). And last but not least at the report level I created data sets for default member for the Date parameter involved , based on the previous logic for defining the default member at dimension level. ( Tail(Exists (...) construction . I must frankly admit , that I missed so much a feed-back, especially because I was convinced that someone must has been struggling with this kind of issue before. Hope that you will log // reproduce // further investigate this issue. Best regards, Mihai

Thanks Mial sharing the informationPlease remember to mark the replies as answers if they help and unmark them if they provide no help.

Hi Challen Fu I had to mark my last reply as answered. Best regards, Mihai