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...
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" }}},

     

     

Anmolgan's avatar
Anmolgan
Post Prodigy
7 years ago

I want to achive the same thing, I have 10 Months of data loaded in my BI Desktop, but I want to see 2 Years of data and this causing problems to load in BI desktop, Can you elaborate more in how can I achive this from BI Service ?

ncbshiva's avatar
ncbshiva
Advocate V
7 years ago

Hi,

 

You need to create parameter and pass that into the CubeApplyParamter (BEx Query parameter).

I have created a function by using below code and called that in my Bex Query :

 

let
Source = (Fiscal_year as text) => let
#"GetParameterValue" = "[" & (Fiscal_year) & "]",

 

 

let
GetCurrentYear = Date.Year(DateTime.LocalNow()),
FirstYear = GetCurrentYear - #"YearRange"+1,
GenerateYears = {#"FirstYear"..#"GetCurrentYear"},
#"Converted to Table" = Table.FromList(GenerateYears, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom1" = Table.AddColumn(#"Converted to Table", "Prefix", each "K2"),
Renamed_Columns = Table.RenameColumns(#"Added Custom1", {{"Column1", "Years"}}),
MergedColumns = Table.CombineColumns(Table.TransformColumnTypes(Renamed_Columns, {{"Years", type text}}, "en-US"), {"Prefix", "Years"}, Combiner.CombineTextByDelimiter("", QuoteStyle.None), "Fiscal_Year"),
GetSapBwDat = Table.AddColumn(MergedColumns, "SapBwData", each GetSAPBWData([Fiscal_Year]))

 

 

  • Anmolgan's avatar
    Anmolgan
    Post Prodigy
    7 years ago

    How did you build this function inside of the PowerBI?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi I also have the same requirement but my source is SAP HANA.  I have created two parameters in PBI Desktop 1. Calday from 2. Calday2. And this parameter I should pass to the Advance editor dynamically. can you explain how to do this?

     

     

    My requirement is to pass the Calday values dynamically to SAP HANA view and get the data on the fly. so we can load the historic data year by year with an incremental refresh.

     

    Thanks

    • ncbshiva's avatar
      ncbshiva
      Advocate V
      6 years ago

      Hi ,

       

      You can create a parameter called Calday with some values which is required for you.

       

      And replace the current value with Parameter that you have created.

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

       

      {Cube.ApplyParameter, "IP_CALDAY_FROM",& #"GetParameterValue"}

      {Cube.ApplyParameter, "IP_CALDAY_TO",& #"GetParameterValue"}

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi ,

         

        we have implemented as you said but still, we are facing issues as shown in the below.

        In My scenario, Caldayfrom and CaldayTo parameters are defined like this.  

        CalDayFrom : Type : Date

                              Suggested Value: Any value 

                              Current value: 01-Aug-16

        CalDayTo : Type : Date

                              Suggested Value: Any value 

                              Current value: 30-Aug-16

         

        In my SAP HANA spurce data is coming as follows

         

         

        Thanks