Member Formula - Min/Max Calculations
- 3 years ago
Depends a lot on my/our interpretation of the requirements as stated here.
If you want to find which mix of UD1-UD8 is the one mix that yields the MIN/MAX amounts with those accounts, then it should be possible to open a single databuffer which is just pre-filtered on those four accounts (using GetDataBufferUsingFormula(FilterMembers()) if necessary)
Then when iterating through the databuffer, you get a DataBufferCell, and you can first test if
DataBufferCell.DataBufferCellPk.AccountId = [ Account ID of A#Account1 ]
If found then you grab the DataBufferCell as a variable, then set DataBufferCell.DataBufferCellPk.AccountId to the accountID of one of the other accounts (e.g. Account2), and then immediately retrieve the corresponding cell for Account2 (with the same UDs) from the same databuffer , like this :'------------------------------------------- Dim myBuf As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#All,[A#SomeParent.Base])") Dim i_AcctId_Acct1 As Integer = api.Members.GetMemberId(DimTypeId.Account,"Account1") Dim i_AcctId_Acct2 As Integer = api.Members.GetMemberId(DimTypeId.Account,"Account2") Dim i_AcctId_Acct3 As Integer = api.Members.GetMemberId(DimTypeId.Account,"Account3") For Each myCell In myBuf.DataBufferCells.Values If myCell.DataBufferCellPk.AccountId = i_AcctId_Acct1 AndAlso Not(myCell.CellStatus.IsNoData) Then Dim Acct2CellPk As New DataBufferCellPk(myCell.DataBufferCellPk) Acct2CellPk.AccountId = i_AcctId_Acct2 Dim cellForAcct2 As DataBufferCell = myBuf.GetCell(si, Acct2CellPk) Dim Acct3CellPk As New DataBufferCellPk(myCell.DataBufferCellPk) Acct3CellPk.AccountId = i_AcctId_Acct3 Dim cellForAcct3 As DataBufferCell = myBuf.GetCell(si, Acct3CellPk) ' --- tests Acct1 - Acct2 - Acct3 , for this specific combination of UD1-8, and check if cells exist for other accts ' --- assume if no cell exists for Acct2/Acct3 then assume they are zero. You might want to have a different behaviour. Dim Acct1Amount As Decimal = myCell.CellAmount Dim Acct2Amount As Decimal = If(cellForAcct2 Is Nothing, 0, cellForAcct2.CellAmount) Dim Acct3Amount As Decimal = If(cellForAcct3 Is Nothing, 0, cellForAcct3.CellAmount) Dim testResult As Decimal = Acct1Amount - Acct2Amount - Acct3Amount '--- compare the testResult with previous min/max amounts -- '--- put your own code here to update your own variables accordingly -- End If Next '--------------------------------------------
Remember a DataBuffer is basically a dictionary with a DataBufferCellPk as it's key. So you can look up cells very quickly once you have a DataBufferCellPk object that it initialized with all the member IDs for each dimension type.
So you should be able to get a DataBufferCell for Account1/Account2/Account3/Account4 , each which have the same corresponding UDs. Once you have 4 variables of type DataBufferCell then you can do the min/max math directly on the cellAmounts, and record the min/max results before looping to the next iteration.