Hello,
I am requesting some assistance. I am trying to write a business rule that will exclude " 1 account and 1 department combination" from and entire rollup. For example, the all included account that is pulling is A#equip_rev and I am trying to exclude Account#410000, Dep#190 from the rule. Do anyone know how I should right that rule to exclude that account/department combination? Your help and feedback is greatly appreciated.
You can use the remove function A#equip_rev.remove(410000):Dep#Total.remove(190).
If this request (to remove a specific Acct/UD combination) is for a DataBuffer then it would be more efficient to exclude them inside the DataBufferCells loop by testing the DataBufferCellPk, and not to use GetDataBufferUsingFormula, because if you put filter expressions into GetDataBufferUsingFormula then it will perform multiple passes through the databuffer, and need to allocate 2x or 3x the memory otherwise required.
Some of those from an Essbase background may have heard the old phrase "IF in a Dense Dimension, FIX in a Sparse Dimension". OK it was a big generalization, but the same concept applies here ( IF statement vs initial GetDataBuffer[the "FIX" statement] ).
In other words, if you can exclude that Acct/Dept combination as part of the databuffer scan then that is more efficient, like this example:
Dim di As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo(String.Empty)
Dim buf As DataBuffer = api.Data.GetDataBuffer(DataApiScriptMethodType.Calculate, "<starting buffer script>" , di)
Dim iExclAcct As Integer = api.Members.GetMemberId(DimTypeId.Account, "410000")
Dim iExclDept As Integer = api.Members.GetMemberId(DimTypeId.UD1, "190")
For Each cell As DataBufferCell In buf.DataBufferCells.Values
If Not cell.CellStatus.IsNoData _
AndAlso cell.CellAmount <> 0 _
AndAlso cell.DataBufferCellPk.AccountId <> iExclAcct _
AndAlso cell.DataBufferCellPk.UD1Id <> iExclDept Then
' --- do your stuff based on the cell object --
' --- by this point you have already filtered out the Acct/Dept combination, as well as zero/blank cells, by only doing one pass
End If
Next
If it's for a CubeView or dashboard or something like that, then I would lean towards using the .Remove function as OS_Pizza mentioned.