Forum Discussion

ncbshiva's avatar
ncbshiva
Icon for Advocate V rankAdvocate 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" }}},

     

     

ncbshiva's avatar
ncbshiva
Icon for Advocate V rankAdvocate 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]))

 

 

Anonymous's avatar
Anonymous
Not applicable
7 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
    Icon for Advocate V rankAdvocate V
    7 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

       

      • ncbshiva's avatar
        ncbshiva
        Icon for Advocate V rankAdvocate V
        6 years ago

        Hi,

         

        Change your parameters to the required format like 20160108. It should work