Cube.ApplyParameter - SAP BW connector - syntax to select range or between?

BartVW · ‎12-01-2023

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"

Gauravshahin · ‎05-30-2024

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.

BartVW · ‎06-26-2024

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.

BartVW · ‎12-04-2023

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]

Anonymous · ‎12-06-2023

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
    DatesList

2. 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 Team

If 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

BartVW · ‎12-08-2023

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.

Anonymous · ‎12-08-2023

Hi @BartVW ,

Please try this custom function again.

= (DateStart as date, DateEnd as date) =>
let
    DurationDays = Duration.Days(DateEnd - DateStart) + 1,
    DatesList = List.Transform(List.Dates(DateStart, DurationDays, #duration(1, 0, 0, 0)),each "["&Date.ToText(_,"YYYYMMDD")&"]") 
in
    DatesList

It should be close.

Best Regards,
Gao
Community Support Team

If 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

BartVW · ‎12-08-2023

hi @Anonymous

Yes with this, it works ok if I keep to only 2 dates. But as soon as i have a date range of more than 2 days, it throws the same error again.

Anonymous · ‎12-04-2023

Hi @BartVW ,

Try:

let
...
      {Cube.ApplyParameter, "[!V000026]", {{"[20230203]:[20230208]"}}}
...
in
  #"Added Items"

sql - How to use MDX query to specify date range - Stack Overflow

Best Regards,
Gao
Community Support Team

If 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