Passing multiple values to a Dataset(MDX) RRS feed

  • Question

  • The scenerio is . I have 2 dataset
    Dataset 1 : To pull account IDs for a particular unique group. 
                - written in sql and quered from a different database
                - eg : Select Account_id from Customer_table where group_id=12 (results :101 ,109 ,111 )
    Dataset 2 : To use these account ID in parameter to retrieve other data. 
                - Here Data source is a Cube (SSAS).
    Can any one Help me with this because i cannot directly use those Account_id into the parameters because MDX takes in as Store Set eg: [Account].[AccuntID].&[101] ??

    (Advance : Niether can i take an additional parameter in my SSRS report to concatenate the Store Set eg : = "[Account].[AccuntID].&[" & Parameter!Acocountid.Value & "]" because the account ID retrieved have different hierarchy and fall in any eg : [Account].[AccuntID].[Hier9].&[109] ,

    Please advice on this . Thank You 
    Tuesday, January 13, 2015 10:38 AM


  • Hi Keshav11,

    According to your description, you want to use values which are from T-SQL query in MDX query to filter data. Right?

    In this scenario, since the values from T-SQL query are under different hierarchy in the cube, we can't concatenate the parameter value into a string and convert the string into a member. Because we can't determine the hierarchy for each value. For your requirement, one workaround is using Case When statement in your query.

    Case  @param

    when 109 then '[Account].[AccuntID].[Hier9].&[109]'

    when 101 then '[Account].[AccuntID].[Hier5].&[101]'



    If you have any question, please feel free to ask.

    Simon Hou
    TechNet Community Support

    Wednesday, January 14, 2015 11:59 AM