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]))

 

 

Anmolgan's avatar
Anmolgan
Icon for Post Prodigy rankPost Prodigy
7 years ago

How did you build this function inside of the PowerBI?