Filter datacell on UD dimension

AndreaF
Contributor III

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?

p1.png

 

Thank you

1 ACCEPTED SOLUTION

JackLacava
Community Manager
Community Manager

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.

 

 

View solution in original post

3 REPLIES 3

JackLacava
Community Manager
Community Manager

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.

 

 

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

ChrisLoran
Valued Contributor

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