Forum Discussion

vmanojrc30's avatar
vmanojrc30
Contributor
5 months ago
Solved

Member Filter Functions in Finance BR

Is it possible to use Member Expansion functions like U1#[Top].Base.Where , U1#[Top].Base.Options within api.Data.GetDataBufferUsingFormula?

I want to get data to the buffer from a Scenario / Scenario Type which is setup with a Summary UD1 dimension in the Cube Configuration.The data is loaded to this Scenario at Summary UD1 members.

In the Spreadsheet I am able to pull  the data using below member filter function. U1#[XX].Base.Options(cube = AA , ScenarioType = Operational)

However when I apply the same function in the api.Data.GetDataBufferUsingFormula , I am getting errors stating need a comma after Options.

If this function cannot be used within Finance BR is there another way to get the data from base members of Summary UD1 dimension?

Dimension

    Summary UD1

            Detail UD1

 

Members

Summary UD1

      Top

            CC001

 

Detail UD1

      Top

          CC001

                CC0011

 

 

                 

 

     

 

 

Finance
  • rhankey's avatar
    rhankey
    5 days ago

    If a member filter contains commas, you will need to wrap the member filter with [].  Eg "[U1#[XX].Base.Options(cube = AA , ScenarioType = Operational)]" so that the FilterMembers() function does not get confused when parsing the filters.

7 Replies

Sort By
    • ChristianW's avatar
      ChristianW
      Valued Contributor
      4 months ago

      Hi

      Daniel's solution is correct. There is also a RemoveMembers function. 

      Cheers

  • vmanojrc30's avatar
    vmanojrc30
    Contributor
    4 months ago

    Hi DanielWillis ChristianW 

    I am particularly having issues when using Base.Option OR Base.Where filter functions.

    In the Spreadsheet I am able to pull the data using below member filter function. U1#[XX].Base.Options(cube = AA , ScenarioType = Operational).However when I apply the same function in the api.Data.GetDataBufferUsingFormula , I am getting errors stating need a comma after Options.

    So I am not sure if these functions are valid within api.Data.GetDataBufferUsingFormula

  • CarlosAlvear's avatar
    CarlosAlvear
    Contributor
    5 days ago

    Dear all, I'm also having the same issue as vmanojrc30, stating that a comma is missing when using "U1#[XX].Base.Options(cube = AA , ScenarioType = Operational)" in a databufferUsingFormula call. Should it work or it isn't defined yet in the databufferUsingFormula?

    Thank you

    • rhankey's avatar
      rhankey
      Contributor II
      5 days ago

      If a member filter contains commas, you will need to wrap the member filter with [].  Eg "[U1#[XX].Base.Options(cube = AA , ScenarioType = Operational)]" so that the FilterMembers() function does not get confused when parsing the filters.

      • CarlosAlvear's avatar
        CarlosAlvear
        Contributor
        5 days ago

        Thank you Rhankey!

        That works! A follow-up question:

        There's a scenario A with summary members (scenario Type 7) and a scenario B with extended members (scenario Type 2). I can recover a 1st databuffer using :

        api.data.getDataBufferUsingFormula("FilterMembers(S#ScenarioA, [U1#[XX].Base.Options(cube = AA , ScenarioType = ScenarioType7)]")

        but when using 

        api.data.getDataBufferUsingFormula("FilterMembers(S#ScenarioB, [U1#[XX].Base.Options(cube = AA , ScenarioType = ScenarioType7)]")

        this last databuffer is empty (I guess because the base data belongs to one level below). Would it be possible to get this 2nd data buffer at a summary level just as the 1st Data Buffer?

        Thank you