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.   W...
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

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

      What locale are you using.

       

      If you create a new blank query with:

      = Text.From(<<Your logic>>)

      what is the ouput?

      • oslosa's avatar
        oslosa
        Helper I
        6 years ago

        It was the error message from the SAP server that was confusing. I thought that my output from Text.From() was '03.2020', which didn't make any sense, but that was just SAP's interpretation. I got '202003', which was expected given the code, but I needed '2020003' with the extra zero for it to be a valid entry in SAP. Your proposed solution would have worked were it not for the extra zero.

         

        Anyways, case closed. Thanks for the fast responses guys.