Top N in Cubeview using BR
I am trying to get top N (top 10) customers by revenue account in my cubeview. For this i re-purposed the snippet finance code for List Ranked to this:
Namespace OneStream.BusinessRule.Finance.Ranked_list
Public Class MainClass
Public Function Main(
ByVal si As SessionInfo,
ByVal globals As BRGlobals,
ByVal api As FinanceRulesApi,
ByVal args As FinanceRulesArgs
) As Object
Try
Select Case api.FunctionType
Case FinanceFunctionType.MemberListHeaders
Return New List(Of MemberListHeader) From {
New MemberListHeader("Ranked")
}
Case FinanceFunctionType.MemberList
If args.MemberListArgs.MemberListName.Equals("Ranked", StringComparison.InvariantCultureIgnoreCase) Then
Dim header As New MemberListHeader("Ranked")
Dim members As List(Of Member) = GetRankedMembers(si, api, args)
Return New MemberList(header, members)
End If
End Select
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
#Region "Ranking Logic"
Private Function GetRankedMembers(
ByVal si As SessionInfo,
ByVal api As FinanceRulesApi,
ByVal args As FinanceRulesArgs
) As List(Of Member)
Try
'-------------------------------
' Required parameters
'-------------------------------
Dim rankType As String = args.MemberListArgs.NameValuePairs("RankType")
Dim rankCount As Integer = Convert.ToInt32(args.MemberListArgs.NameValuePairs("RankCount"))
Dim loopMemberFilter As String = args.MemberListArgs.NameValuePairs("LoopMemberFilter")
If Not args.MemberListArgs.NameValuePairs.ContainsKey("DataCellToRankMemberFilter") Then
Throw New Exception("Missing required parameter: DataCellToRankMemberFilter")
End If
Dim dataCellFilter As String = args.MemberListArgs.NameValuePairs("DataCellToRankMemberFilter")
Dim cubeName As String = New MemberScriptBuilder(dataCellFilter).Cube
'-------------------------------
' Determine looping dimension
'-------------------------------
Dim loopDimPk As DimPk =
GetDimPkForLoopMemberFilter(si, api, cubeName, loopMemberFilter, dataCellFilter)
Dim membersToLoop As List(Of MemberInfo) =
api.Members.GetMembersUsingFilter(loopDimPk, loopMemberFilter, Nothing)
Dim rankedCells As New List(Of MemberAndCellValue)
'-------------------------------
' Loop members and evaluate cell
'-------------------------------
For Each mi As MemberInfo In membersToLoop
Dim mfb As MemberScriptBuilder =
SubstituteLoopMember(loopMemberFilter, dataCellFilter, mi.Member.Name)
Dim cell As DataCellInfoUsingMemberScript =
BRApi.Finance.Data.GetDataCellUsingMemberScript(
si,
cubeName,
mfb.GetMemberScript
)
If cell IsNot Nothing AndAlso
Not cell.DataCellEx.DataCell.CellStatus.Invalid AndAlso
Not cell.DataCellEx.DataCell.CellStatus.IsNoData Then
rankedCells.Add(
New MemberAndCellValue(
mi.Member,
cell.DataCellEx.DataCell.CellAmount
)
)
End If
Next
'-------------------------------
' Rank results
'-------------------------------
Dim result As New List(Of Member)
Select Case rankType.ToUpperInvariant()
Case "TOP"
result = rankedCells.
OrderByDescending(Function(x) x.CellValue).
Take(rankCount).
Select(Function(x) x.Member).
ToList()
Case "BOTTOM"
result = rankedCells.
OrderBy(Function(x) x.CellValue).
Take(rankCount).
Select(Function(x) x.Member).
ToList()
Case "MAX"
Dim maxItem = rankedCells.OrderByDescending(Function(x) x.CellValue).FirstOrDefault()
If maxItem IsNot Nothing Then result.Add(maxItem.Member)
Case "MIN"
Dim minItem = rankedCells.OrderBy(Function(x) x.CellValue).FirstOrDefault()
If minItem IsNot Nothing Then result.Add(minItem.Member)
End Select
Return result
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
#End Region
#Region "Helpers"
Private Function GetDimPkForLoopMemberFilter(
si As SessionInfo,
api As FinanceRulesApi,
cubeName As String,
loopFilter As String,
dataCellFilter As String
) As DimPk
Dim cube As CubeInfo = api.Cubes.GetCubeInfo(cubeName)
If cube Is Nothing Then Throw New Exception("Invalid cube: " & cubeName)
Dim dimType As DimType = DimType.GetItem(loopFilter.Split("#"c)(0))
Dim mfb As New MemberScriptBuilder(dataCellFilter)
Dim scenario As Member = api.Members.GetMember(DimTypeId.Scenario, mfb.Scenario)
Dim scenarioType As ScenarioType = api.Scenario.GetScenarioType(scenario.MemberId)
Dim dimId As Integer =
cube.Cube.CubeDims.GetDimId(dimType.Id, scenarioType.Id)
Return New DimPk(dimType.Id, dimId)
End Function
Private Function SubstituteLoopMember(
loopFilter As String,
dataCellFilter As String,
memberName As String
) As MemberScriptBuilder
Dim mfb As New MemberScriptBuilder(dataCellFilter)
Dim dimType As DimType = DimType.GetItem(loopFilter.Split("#"c)(0))
Select Case dimType.Id
Case DimType.Entity.Id : mfb.Entity = memberName
Case DimType.Account.Id : mfb.Account = memberName
Case DimType.UD1.Id : mfb.UD1 = memberName
Case DimType.UD2.Id : mfb.UD2 = memberName
Case DimType.UD3.Id : mfb.UD3 = memberName
Case DimType.UD4.Id : mfb.UD4 = memberName
Case DimType.UD5.Id : mfb.UD5 = memberName
Case DimType.UD6.Id : mfb.UD6 = memberName
Case DimType.UD7.Id : mfb.UD7 = memberName
Case DimType.UD8.Id : mfb.UD8 = memberName
End Select
Return mfb
End Function
#End Region
#Region "Helper Class"
Private Class MemberAndCellValue
Public Property Member As Member
Public Property CellValue As Decimal
Public Sub New(m As Member, v As Decimal)
Member = m
CellValue = v
End Sub
End Class
#End Region
End Class
End Namespace
After this changed My cubeview row member filter to this, however when cubeview runs , it runs for a bit and then page comes back completely blank :
U5#Root.CustomMemberList(
BRName = Ranked_list,
MemberListName = Ranked,
RankType = Top,
RankCount = 10,
LoopMemberFilter = U5#Total_Customer.Children,
DataCellToRankMemberFilter =
Cb#Financials
:E#TotalEntity
:C#USD
:S#Actual
:T#2026M1
:V#YTD
:A#A5
:F#EndBal
:O#Top
:I#Top
:U1#Organization
:U2#Project
:U3#None
:U4#None
:U6#None
:U7#None
:U8#None
)
Does anyone know what i could be missing?