Forum Discussion
SAP BW BEx Dynamic Parameters
I wanted to see how others are querying SAP BW through BEx and see how and if you are leveraging dynamic parameters. I realize that what I'm about to ask could be controlled at the BEx level but I like to have more control at the Power BI Layer.
The first time you connect to a BEx query you are prompted to enter required parameters (And optional if needed)
Once you select all your fields and input your parameters you M script looks something like this:
However there are times when I have date parameters and I'd like to Power BI to automatically pull in today's date by dynamically setting the date. So I do something along the lines of this where I create variables within M to build the parameters
I then update the parameters in the Mscript to reference the variables I've created
This has worked thus far but I wanted to see how others may be going about this and see if maybe there was a more robust way to manager this utilizing the Manage Parameters, Parameter tables, and potentially functions to look up parameter values and pass them in.
Thoughts?
Hi Anonymous,
You can follow below blogs to use query parameter in M query.
Power BI Desktop Query Parameters, Part 1
Power BI Desktop Query Parameters, Part2 – Dynamic Data Masking and Query Parameters
Regards,
Yuliana Gu
14 Replies
Hi Anonymous,
You can follow below blogs to use query parameter in M query.
Power BI Desktop Query Parameters, Part 1
Power BI Desktop Query Parameters, Part2 – Dynamic Data Masking and Query Parameters
Regards,
Yuliana Gu
We have a BW Query on Accounts Receiveable and use a dynamic filter for today's date. Rest of the logic in the report is based on this variable.
I am able to create a report on this query, issue is when i refresh next day the dynamic variable date does not change to today's date as it happen in Bex.
What is the solution for this.
thanks,
MAT.
Maybe I am missing something here. The issue still persists. I have a test Power BI analytic that "direct queries" SAP BW. It works fine in Power BI desktop client and I can change BEx query parameters within the Power BI desktop environment. However, the moment I publish it, I no longer can see the BEx query parameters in order to change the parameter values at runtime. Am I missing something here?
Thanks
Jagannathan Santhanam
Not sure how you've set this up but the idea here is that the paramters are dynamic, there is no need to change them. The should populate for you based on the logic that you've written. Look at the last post above your original, you'll see that a paramter was created called TodaysDate and then this value is what gets passed into your M script. Nothing in this post deals with manually changing paramters but rather the paramters are dynimacally/self populated for you.
Now I say this without ever having done a direct connect to SAP BW. So maybe your case is different. That said, could you post your mscirpt or some screenshots of what your tried to achieve so I can have a better idea.
I will admit, I am absolutely new to Power BI. We use SAP Business Objects, Tableau etc., in our environment. All of these reporting, visualization tools allow the user to dynamically interact at report/visualization run-time and change the underlying BW BEx parameters. For example, I as the report author could have created and published company profitability visualization for the 1st Quarter of FY 2020. An end-user would like to refresh this visualization for the 2nd Quarter of FY2020. This is NOT possible in Power BI?