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 listDriversDataCellExFilterdForDriversTarget As New List(Of DatacellEx)

For Each itemDataCellEx As DataCellEx In listDriversDataCellExFilterdForDrivers
   itemDataCellEx.DataCell.DataCellPk.TimeId = TimeTargetId
   itemDataCellEx.DataCell.DataCellPk.ScenarioId = ScenarioTargetId
   listDriversDataCellExFilterdForDriversTarget.Add(itemDataCellEx)
Next itemDataCellEx

 

Now, I was trying to update this to only copy data where UD3=None, but I am not sure on how to do that. I can see datacells have a function called GetUD3Name, but the function requires a FinanceRulesApi parameter.

 

Would using the GetUD3Name function work for my scope and what would be an example of valid FinanceRulesApi parameter?

 

Thank you

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.

     

     

  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor
    3 years ago

    Tip: Use DimConstants.None (which evaluates to -999), instead of a hard-coded -999.  Much more meaningful and readable.

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    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.

     

     

    • 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()