Forum Discussion

ncbshiva's avatar
ncbshiva
Advocate V
7 years ago
Solved

Dynamic Parameters with SAP BW BEX

Hi Team,

 

I am using the SAP BW for connecting the data. I have 6 years of data . Initially i am pulling only 2 years of data into Power Bi desktop, so that i wont get any memory issues when i try to load all the 6 years of data.

 

And Fiscal Year is a variable in SAP Bex and below is the power quey code for the same with 2 years filtered.

 

{Cube.ApplyParameter, "[0S_FYEAR]", {{"[0FISCYEAR].[K22017]", "[0FISCYEAR].[K22018]"}}},

 

Now i want to create a parameter in Power BI and use that same in my Power Query.

So that i can publish the pbix file with 2 years of data in online and change the parameter value for all the six years , so that it will refresh in online without any issues.

 

Please let me know how to achieve this solution.

 

Thanks in advance.

Need Help
  • ncbshiva's avatar
    ncbshiva
    7 years ago

    I was able to achieve this solution with help of some blogs.

     

    Below is the example on how to pass the parameters in the SAP BW query.

     

    First you need to define your parameter , in my case it was Fiscal Year

     

    #"GetParameterValue" = "[" & (Fiscal_year) & "]",

     

    Then you need to use the above parameter in the cubeApplyParameter query as shown below.

     

    {Cube.ApplyParameter, "[0S_FYEAR]", {{"[0FISCYEAR]." & #"GetParameterValue", "[0FISCYEAR]." & #"GetParameterValue" }}},

     

     

19 Replies

    • ncbshiva's avatar
      ncbshiva
      Advocate V
      7 years ago

      Hi v-cherch-msft,

       

      This will help me when i am using SQL and other data sources. In my case i am using SAP BW BEX ,so this is different from others.

      And that example will not work i think,

       

      Please let me know if anyone has implemented with SAP BW with variable as parameters in Power BI.

       

      Thanks in advance.

      • ncbshiva's avatar
        ncbshiva
        Advocate V
        7 years ago

        I was able to achieve this solution with help of some blogs.

         

        Below is the example on how to pass the parameters in the SAP BW query.

         

        First you need to define your parameter , in my case it was Fiscal Year

         

        #"GetParameterValue" = "[" & (Fiscal_year) & "]",

         

        Then you need to use the above parameter in the cubeApplyParameter query as shown below.

         

        {Cube.ApplyParameter, "[0S_FYEAR]", {{"[0FISCYEAR]." & #"GetParameterValue", "[0FISCYEAR]." & #"GetParameterValue" }}},