Forum Discussion

BartVW's avatar
BartVW
Icon for Helper I rankHelper I
2 years ago

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.   Fr...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
BartVW
Icon for Helper I rankHelper I
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      BartVW
      Icon for Helper I rankHelper I
      2 years ago

      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.