Forum Discussion
Cube.ApplyParameter - SAP BW connector - syntax to select range or between?
hi all
Using SAP BW Connector to connect to a CDS view in a S4/HANA system. Basic connection works ok and able to pull data.
To limit the data pull I would like to select a date range.
From below M example, this is the line I would like to customize: "{Cube.ApplyParameter, "[!V000026]", {{"[20230203]","[20230208]"}}}". Right now this selects 2 dates, but I'm looking for a way to select from a certain date to a certain date. In the underlying CDS view the parameter is set up as an interval filter.
Would anyone have any clue if this is possible?
let
Source = SapBusinessWarehouse.Cubes("ourserver", "xxx", "xxx", "xxx", [Implementation="2.0", LanguageCode="EN"]),
#"2CZI_EWM_WHSETASKITEMCUBE" = Source{[Name="2CZI_EWM_WHSETASKITEMCUBE"]}[Data],
#"2CZI_EWM_WHSETASKITEMCUBE/2CZEWMWTBI" = #"2CZI_EWM_WHSETASKITEMCUBE"{[Id="2CZI_EWM_WHSETASKITEMCUBE/2CZEWMWTBI"]}[Data],
#"Added Items" = Cube.Transform(#"2CZI_EWM_WHSETASKITEMCUBE/2CZEWMWTBI",
{
{Cube.ApplyParameter, "[!V000002]", {{"CHG1"}}},
{Cube.AddAndExpandDimensionColumn, "[2CTD6ZEP7AIPDRWRB0F3XMD4A0E]", {"[2CTD6ZEP7AIPDRWRB0F3XMD4A0E].[LEVEL01]"}, {"Confirmation Date.Confirmation Date Level 01"}},
{Cube.AddMeasureColumn, "Target Quan. in Alternative UoM", "[Measures].[TargetQuantityInAltvUnit]"},
{Cube.ApplyParameter, "[!V000026]", {{"[20230203]","[20230208]"}}}
})
in
#"Added Items"
8 Replies
Hi BartVW ,
Try:let ... {Cube.ApplyParameter, "[!V000026]", {{"[20230203]:[20230208]"}}} ... in #"Added Items"
sql - How to use MDX query to specify date range - Stack OverflowBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
Hi Anonymous
When I change DateList as follows:
DateList = "[20230116]:[20230117]"
Or when I put the value directly as per your suggestion:
{Cube.ApplyParameter, "[!V000026]", {{"[20230203]:[20230208]"}}}
I get this error message:
Expression.Error: The parameter value is not a valid MDX identifier.
Details:
[20230116]:[20230117]Hi BartVW
Please try:
1. create a custom function to generate a list of dates within a specified range:(DateStart as date, DateEnd as date) => let DurationDays = Duration.Days(DateEnd - DateStart) + 1, DatesList = List.Dates(DateStart, DurationDays, #duration(1, 0, 0, 0)) in DatesList2. Then, use this function to generate a list of dates within the desired range and apply it to the paremeter:
let ... DateRange = YourCustomFunctionName(#date(2023, 2, 3), #date(2023, 2, 8)), #"Added Items" = Cube.Transform(#"2CZI_EWM_WHSETASKITEMCUBE/2CZEWMWTBI", { ... {Cube.ApplyParameter, "[!V000026]", DateRange} }) in #"Added Items"Solved: Passing multiple parameters to SAP BW Query - Microsoft Fabric Community
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
Thank you Anonymous for your continued help in finding a solution.
Unfortunately using a function also didn't work. If I use the function to generate a date range of more than 2 days, I get the exact same error as before. If I use a date range of 2 days or less, I get a different type of error, probably because your function creates a list of date types.
Expression.Error: We cannot convert the value #date(2023, 2, 😎 to type Text.
Details:
Value=8/02/2023
Type=[Type]I'm somehow thinking the BW parameter doesn't accept more than 2 values, but this seems to be the case only when I feed the Cube.ApplyParameter function using a variable list. If I hardcode a list of dates, e.g. by directly using "[20230116]","[20230117]","[20230118]","[20230119]","[20230120]" it works fine.
I had the same problem. Please see below and try if it works for you or not.
Step 1: Create a Start Date and End Date -- this step will help you to dynamically select the date range.
let
#"currentYear" = Date.Year(DateTime.FixedLocalNow()),
#"currentMonth" = Date.Month(DateTime.FixedLocalNow()),
#"startYear" = if #"currentMonth"<4 then #"currentYear"-2 else #"currentYear"-1, // I had to introduce this step to get the last n FYs
#"startDate" = #date(#"startYear",4,1)in
#"startDate"
-----------
let
#"EndDate" = Date.From(Date.AddYears(DateTime.FixedLocalNow(),2))
in
#"EndDate"Step 2 : {Cube.ApplyParameter, "DATE CUBE", {GetStartDate,GetEndDate}}
It worked for me.Thank you for your input Gauravshahin !
I currently no longer have the issue, as I'm able to rely on query folding to properly apply the filters when sending the request to BW. I will keep you suggestion in mind in case I have to relook at options.
