Forum Discussion

AndreaF's avatar
AndreaF
Contributor III
3 years ago

Filter datacell on UD dimension

Hi,   I have an existing extensibility rule which copy some data from a source scenario/period to a target scenario/period.   This is the "core" of the code Dim listDriversDataCellExFilterd...
Finance
  • JackLacava's avatar
    JackLacava
    3 years ago

    Switch the problem around: retrieve the ID of that member (with brapi.Finance.Members.GetMemberId), before you even enter the loop, and then compare that ID with the one of the cell inside the loop. Among other things, it will be much faster than looking up a member name with every iteration. Ideally, you always work with member IDs rather than names.

    When you do that, probably you'll also discover that "None" always has the same ID ( -999 ) in all dimensions, so you don't really need to look it up; although, if you wanted to follow absolute best practices, you would pull it from DimType.<your dimension>.DefaultMemberId.

     

     

JackLacava's avatar
JackLacava
Honored Contributor

Switch the problem around: retrieve the ID of that member (with brapi.Finance.Members.GetMemberId), before you even enter the loop, and then compare that ID with the one of the cell inside the loop. Among other things, it will be much faster than looking up a member name with every iteration. Ideally, you always work with member IDs rather than names.

When you do that, probably you'll also discover that "None" always has the same ID ( -999 ) in all dimensions, so you don't really need to look it up; although, if you wanted to follow absolute best practices, you would pull it from DimType.<your dimension>.DefaultMemberId.

 

 

AndreaF's avatar
AndreaF
Contributor III
3 years ago

Thank you! As per your suggestion, I have changed the commands generating the DataCellEx before I enter in the loop (the DataCellEx object is called listDriversDataCellExFilterdForDrivers in my example).

For completeness, this is the bit of code I have changed:

Before:

Dim listDriversDataCellsFilterdForDrivers As List(Of Datacell) = listDriversDataCells.Where(Function(itemdatacell) brapi.Finance.Members.IsBase(si,accountDimPk,parentAccountDriver,itemdatacell.DataCellPk.AccountId) ).ToList()
Dim listDriversDataCellExFilterdForDrivers As List(Of DatacellEx) = listDriversDataCellsFilterdForDrivers.Select(Function(itemdatacell) New DataCellEx(itemDataCell,String.Empty,DimConstants.Local,accounttype.Balance.Id)).ToList()

After (I have added the orange part):

Dim listDriversDataCellsFilterdForDrivers As List(Of Datacell) = listDriversDataCells.Where(Function(itemdatacell) brapi.Finance.Members.IsBase(si,accountDimPk,parentAccountDriver,itemdatacell.DataCellPk.AccountId) And itemdatacell.DataCellPk.UD3Id=DimConstants.None).ToList()
Dim listDriversDataCellExFilterdForDrivers As List(Of DatacellEx) = listDriversDataCellsFilterdForDrivers.Select(Function(itemdatacell) New DataCellEx(itemDataCell,String.Empty,DimConstants.Local,accounttype.Balance.Id)).ToList()