Forum Discussion
Using Bex Query to import data into power bi
Hi Anonymous
You are truely a saver.....
I will be often connecting with you hereafter...
I would like to appreciate you alot . Thank you
Regards,
Husna
Take a look at this post. I posted this a long time ago but have followed this pattern since and haven't had any issues.
https://community.powerbi.com/t5/Desktop/SAP-BW-BEx-Dynamic-Parameters/m-p/806078#M387707
Here is an image from one of my reports. This has been running for a long time but as I look at it there are some things that stick out. I should probably update the SAPStartDate parameter to take the current year - 1 or 2 (Depending on how far back we want to go). I shouldn't have hard coded the start year like that as the users aren't going back to 2017 at this point. This just causes the file to grow with unnecessary data. Also, on my SAPEnddate I have set it to the current year and hard coded the last day of the year. I dont' have as much as an issue with this as this as there is issue with selecting a date in the future like this. That said, each one of these values could be dynamically created based on today's date and what were trying to accomplish. One last thing, I also prefer to create the paramter as the full field value and pass that in as a value (As shown in the pDateFrom & pDateTo). It's been a while since I've had to play with these but I believe by doing it this way you can still interact with your navigation steps where as if you just pass in the parmeter value into the Cube.ApplyParameter Step I beleive that you have issues interacting with the navigation steps. Any way, hopefully this points you in the right direction.
Let me knwo if you have any issues or problems.
lmf232s
Hi Anonymous
I tried replicating your M code , but i get the below error
For Start and end date i wanted to keep it as previous month, since we get the complete data of the month , i hope i have done the coding correctly for that
Regards,
Husna
Change your code to the following (Removing all the # and ")
CurrentDate = DateTime.LocalNow(), CurrentMonth = Date.AddMonths(CurrentDate, -1), Monthex = Number.ToText(Date.Month(CurrentMonth)),Then pass in this way
[Cube.ApplyParameter, "[ZSUSRPER]", {Monthex, Monthex}},Keep in mind I hand coded this so there may be a mistake that would prevent you from copying the code but you should get the idea. Notice I removed all the # and " from the fields.
I appolgize, you can actually keep the #" in all the fields and it works just fine. I guess I've just never done it that way.
I think it may have something to do with the way you are creating the data. Date's in SAP are typically in an integer format '20200101' rather than a true date '01/01/2020', etc.
Can you do me a favor and connect to the query selecting dates from the paramter drop down and then past your M-Code. This will allow you to see the format of the parameters that you need to pass in. For example, if I just connect to the query and select the dates from the drop downs I get the following;
{Cube.ApplyParameter, "[0I_DAYS]", {"[ZSD_IS02___F13].[20200101]", "[ZSD_IS02___F13].[20200201]"},This allows me to see how we need to format the value that is being passed into the parameter. In my original post we were dealing with Fiscal Period so the values was actually '[0FISCPER].[K1220180001]' and in this case you have to make sure that you are passing in the parameter correctly. You'll have to include the 'K12' part of the value and then append the date to this value.
Does that make sense?