2 Replies Latest reply on Dec 19, 2016 7:07 PM by Benjamin Chen

    Using Parameters to control calculated members with MDX cube

    Louisa Lei

      Hi,

      I work with relational database a lot but I am new to MDX cubes and having a question about using parameters in calculated members.

       

      In my worksheet, I created a calculated member to aggregate the sales (BL Net Amt) from Jan 2015 to March 2015.

      member1.PNG

      Right now I have a year parameter (integer type) and a month parameter (string type). I know the internal parameter name: year [Measures].[Parameter_Parameter 4] and  [Measures].[Parameter_Parameter 6]

      What I want to do is to use these 2 parameters to dynamically change [2015] and [March] in the calculated member, so the users can choose which year and which month to calculate.

      Can someone show me how to achieve this? I saw people mentioning this is do-able but I am still not sure if it is possible.

       

      Thanks!

      Louisa

        • 1. Re: Using Parameters to control calculated members with MDX cube
          Louisa Lei

          Update:

          I emailed Tableau support and they told me Tableau has yet had a built-in way to use parameters in calculated members.

           

          But I found a way to work around with strtoset :

          AGGREGATE({strtoset('[Time].[Year Months].[Year].[2015].[January] : [Time].[Year Months].[Year].[2015].[' + [measures].[parameter_parameter 6] + ']' )}, [Measures].[BL Net Amt])

           

          This way, the query Tableau runs would be:

          WITH

          MEMBER [Measures].[Parameter_Parameter 6] as ' "March" '

          MEMBER [Measures].[YTD BL Net Sales]

              AS AGGREGATE({strtoset('[Time].[Year Months].[Year].[2015].[January] : [Time].[Year Months].[Year].[2015].[' + [measures].[parameter_parameter 6] + ']' )}, [Measures].[BL Net Amt])

          ......

           

          and the result is correct.

          Also, if I want to control is a member instead of a set, then I can use strtomember.

           

          One problem is, this takes 2 more minutes to run , so basically this won't help. I might still need to find a better way to do it.

          • 2. Re: Using Parameters to control calculated members with MDX cube
            Benjamin Chen

            Hi, Louisa

             

            I'm facing the same problem when trying to use parameter in calculated members based on SAP BW cube.

             

            I'm wondering if mdx recognizes the "[Measures].[Parameter]", because in my case, when I create a Tableau parameter and then put it in the form [Measures].[Parameter] in a calculated member. It reported an error.

             

            I am not sure if that because of different cube sources, after all, mine is BW.

             

            Hope you hear what you think.

             

            Best regards,

            Benjamin