Forum Discussion

seangly's avatar
seangly
New Contributor III
4 months ago

Account filter in FdxExecuteDataUnit

Hi Community,

I am building a Business Rule which will pull data base level data for a specific parent under the Account dimension.

I am using FdxExecuteDataUnit to pull data from the cube, but I am not able to add Parent.Base as Account filter in the parameter.  It's seem like I am only able to put a member directly and not and .Base member in the filter.

Dim sFilter As String = "Origin='Import' and Flow='EndBal_Input'"

BRApi.Import.Data.FdxExecuteDataUnit(si, sCubeName, $"E#{sEntFilter},E#82,E#85", sCons, scenarioId, $"S#{sScenario}", $"T#{vPeriod}", sView, True, sFilter, 4, False)

Thank

 

Finance
  • sameburn's avatar
    sameburn
    4 months ago

    Hi seangly , this is expected behaviour.  The FDX Data Unit api retrieves data using Data Unit only.  Once that datatable is retrieved, the filter is then used on that dataset.  At this point there is no relationship back to the Dimension library (you just have data in columns and rows).  This is why you are basically passing something like a SQL WHERE Clause into the filter string variable.  Data for A# is not stored at an aggregated level.  So to achieve what you want you need to convert your expansion e.g. A#Parent1.Base into a WHERE clause that contains the base members of the Parent you want e.g. 

    Dim sFilter As String = "Origin='Import' AND Flow='EndBal_Input'  AND Account = 'Child1' AND Account = 'Child2'"

    Hope this helps

  • sameburn's avatar
    sameburn
    Contributor II
    4 months ago

    Hi seangly , this is expected behaviour.  The FDX Data Unit api retrieves data using Data Unit only.  Once that datatable is retrieved, the filter is then used on that dataset.  At this point there is no relationship back to the Dimension library (you just have data in columns and rows).  This is why you are basically passing something like a SQL WHERE Clause into the filter string variable.  Data for A# is not stored at an aggregated level.  So to achieve what you want you need to convert your expansion e.g. A#Parent1.Base into a WHERE clause that contains the base members of the Parent you want e.g. 

    Dim sFilter As String = "Origin='Import' AND Flow='EndBal_Input'  AND Account = 'Child1' AND Account = 'Child2'"

    Hope this helps