Skip to main content
A
AndreaF
Expert
November 25, 2022
Solved

Filter datacell on UD dimension

  • November 25, 2022
  • 2 replies
  • 0 views

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

Best answer by JackLacava

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.

 

 

2 replies

J
OneStream Employee
JackLacavaOneStream EmployeeAnswer
OneStream Employee
November 26, 2022

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.

 

 

A
AndreaFAuthor
Expert
December 5, 2022

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

C
OneStream Employee
ChrisLoranOneStream Employee
OneStream Employee
November 28, 2022

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

    Terms & ConditionsAccessibility statement