Skip to main content
J
JuleneDS
Newcomer
February 14, 2025

Sum of accounts at base levels

  • February 14, 2025
  • 2 replies
  • 0 views

I would like to return a sum of all rows in my cube view but using the 'sum of rows' is only returning the first result for every account under the main hierarchies I'm calling. 

This is the literal statement I would like to sum -

GetDataCell(A#BALANCE_SHEET.Base.Remove(A#32000).Where(AccountType DoesNotContain 'Liability') - A#BALANCE_SHEET.Base.Remove(A#32000).Where(AccountType = 'Liability') - A#32000:F#BegBal - A#INCOME_STATEMENT.Base.Where(AccountType = 'Revenue') + A#INCOME_STATEMENT.Base.Where(AccountType DoesNotContain 'Revenue'))

Has anyone done something similar through an XFBR string? 

2 replies

B
BeatlesForever
Newcomer
February 14, 2025

Hello,

If it's a KPI that users use frequently, I would create a KPI account with the Account Type and Formula Type set to "DynamicCalc". I would also try to use existing accounts for the calculation.

In the Formula field, you could write something like the following:

Return api.Data.GetDataCell("A#TotalAssets:F#CLO - A#TotalLiabilities:F#CLO - A#32000:F#OPE - A#TotalRevenue:F#None + A#TotalExpenses:F#None")

Hope it helps!

T_Kress
OneStream Employee
T_KressOneStream Employee
OneStream Employee
February 18, 2025

Could you not just build an alternate account rollup in the account dimension to achieve what you are trying to achieve through a bunch of cube view math?  The account dimension auto-aggregates so adding an alternate account rollup does not hurt consolidation performance or cube view performance.  And it may be far easier than trying to do a GetDataCell with complex math directly on the cube view.

Terms & ConditionsAccessibility statement