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

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'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