How to create a SSRS Report with Multivalue Parameter based on SSAS datasource RRS feed

  • Question

  • I am currently developing a report that requires a multivalue text parameter. Datasource for this report is SSAS Cube. When i use a single parameter option it works fine but when i make my parameter multivalue the report dosent work. I tried using Join function in my parameter expression to concatenate different text values but it still does'nt work. Could someone please help me with this?? Below are the MDX query and the parameter expression for my report (with single parameter). Please suggest where do i need to make changes to make this work as multivalue.

    MDX Query

     SELECT NON EMPTY { [Measures].[Verified Client Outcome Total Value], [Measures].[Verified Client Outcome Count], [Measures].[Completed Client Milestone Count], [Measures].[Client Count], [Measures].[Completed Client Action Count], [Measures].[Client Outcome Count] } ON COLUMNS, NON EMPTY { ([System User].[Austrader Full Name].[Austrader Full Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Client Outcome - Status].[Client Outcome - Status - Status Reason].&[Verified by document], [Client Outcome - Status].[Client Outcome - Status - Status Reason].&[Verified by email], [Client Outcome - Status].[Client Outcome - Status - Status Reason].&[Verified by other] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DateFiscalYearName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@SystemUserPostOffice, CONSTRAINED) ) ON COLUMNS FROM [SystemUserCube]))) WHERE ( IIF( STRTOSET(@SystemUserPostOffice, CONSTRAINED).Count = 1, STRTOSET(@SystemUserPostOffice, CONSTRAINED), [System User].[Post Office].currentmember ), IIF( STRTOSET(@DateFiscalYearName, CONSTRAINED).Count = 1, STRTOSET(@DateFiscalYearName, CONSTRAINED), [Date].[Fiscal Year Name].currentmember ), [Client Outcome - Status].[Client Outcome - Status - Status Reason].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

     Parameter Expression 

     ="[System User].[Post Office].[Post Office].&[" + Parameters!SystemUserPostOffice.Value + "]"


    Priyam Shah


    Thursday, April 22, 2010 9:08 AM

All replies

  • Hi Priyam Shah,

    The easiest way is to use the query builder where you can assign additional filter for the query to be the desired field and check it as a parameter (while in the MDX graphical designer). This way BIDS will do everything by itslef and you will have the report working.

    Otherwise - you cannot get it working because you have to actually format the passed valu from the parameter as a MDX SET which is something like this: {value1, value2}. My strength is not MDX so whenever I have to do such report, I directly use the designer as I never could pass a correct SET as parameter value to the dataset.


    Thursday, April 22, 2010 12:38 PM
  • Hi Ivan,


    Thanks for your reply. I applied the first method. its working fine when i pass a parameters with no spaces or special characters (Albany, Adelaide etc) but when it comes to words having space and special characters. i get an error. when i do a single value parameter reports i usually overide this things by using a fully qualified path as below and concatenating the string:

    ="[Client Relationship Manager].[Region Division Name].&[" & Parameters!Region.Value & "]"

    but in multivalue this does not work. Any idea??

    Friday, April 23, 2010 12:32 AM