Forum Discussion

oslosa's avatar
oslosa
Helper I
6 years ago
Solved

Dynamic data retrieval based on today's date

Hi, I am trying to retrieve data for the most recent date from an SAP BW server. It requires me to query a parameter in the following format "[0FISCPER].[K4202003]" to retrieve March 2020 data.

 

When using the following:

{Cube.ApplyParameter, "[!V000003]", {{ Text.Combine({"[0FISCPER].[K4",Number.From(Date.Year(DateTime.LocalNow()))*100+Number.From(Date.Month(DateTime.LocalNow())),"]"})}}}

I get the error message:

Expression.Error: We cannot convert the value 202003 to type Text.

Details:

    Value=202003

    Type=[Type]

which indicates that I am on the right track, but as expected I need it to be a string and not a number.

 

I then try to convert it to a string with Text.From(), but then I get the error message:

DataSource.Error: The SAP BW server reported an error: 'Value '03 .2020' is not valid

which is not at all the '202003' I expected. Does anyone understand why I don't get the desired conversion?

 

Need Help
  • artemus's avatar
    artemus
    6 years ago

    oh, the solution I gave can easily be fixed... just add a 0 e.g.

    yyyy0MM

10 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    HI just to be specific here:

    This is delivering the error-message you've posted below?:

     

    {Cube.ApplyParameter, "[!V000003]", {{ Text.Combine({"[0FISCPER].[K4",
    Text.From(Number.From(Date.Year(DateTime.LocalNow()))*100+Number.From(Date.Month(DateTime.LocalNow())))
    ,"]"})}}}

     

     

    • ImkeF's avatar
      ImkeF
      Community Champion
      6 years ago

      Hm, actually a bit at a loss here - how can this formula actually work?

      Wouldn't it have to be:

       

      {Cube.ApplyParameter, "[!V000003]", {{ Text.Combine({"[0FISCPER].[K4" &
      Text.From(Number.From(Date.Year(DateTime.LocalNow()))*100+Number.From(Date.Month(DateTime.LocalNow())))
       
      & "]"})}}}

       

      Please check your exact coding again.

      • oslosa's avatar
        oslosa
        Helper I
        6 years ago

        Of course, the error was that I should have multiplied by 1000! I wanted 2020003 and not 202003

      • artemus's avatar
        artemus
        Microsoft Employee
        6 years ago

        How about trying:

         

        DateTimeZone.ToText(DateTimeZone.FixedUtcNow(), "yyyyMM", "en-us")

         

        Edit: lower case y

         

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi oslosa 

    Is this problem sloved? 
    If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
    If not, please feel free to let me know.
     
    Best Regards
    Maggie