CubeView

[Ritesh]

Hello Team - I am working on a cube view where I would want the resulting values (variance amount) to sort with the highest value first.

Wondering if anyone else has attempted this.

Thanks, Ritesh

[RachealCrowder]

Hi Ritesh,

I would recommend reviewing snippets for "List Ranked".  This will guide you through how to write in a Finance Business Rule to attach to your cube under the FinanceFunctionType MemberList.  

Kind regards,

Racheal Crowder

'------------------------------------------------------------------------------------------------------------
'Reference Code: XFR_MemberListRanked
'
'Description: Use a business rule retrieve a list of cells and then rank them by the Top or Bottom (N) values.
'
'Usage: This member list is used to return the list of TopN cells across entites.
'
' Example member filters to be added in cubeview column member filter property.
' TopN: E#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = (Top,Bottom,Min,Max), RankCount = X, LoopMemberFilter = E#Root.Base, DataCellToRankMemberFilter = CB#:E#... )
' E#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = Top, RankCount = 10, LoopMemberFilter = E#Root.Base, DataCellToRankMemberFilter = Cb#GolfStream:E#Houston:P#Houston:C#USD:S#Actual:T#2011M3:V#YTD:A#60000:F#None:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#None:U6#None:U7#None:U8#None)
'
'Created By: OneStream
'
'Date Created: 06-01-2016
'------------------------------------------------------------------------------------------------------------
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 Is = FinanceFunctionType.MemberListHeaders
Dim memListHeaders As New List(Of MemberListHeader)
memListHeaders.Add(New MemberListHeader("Ranked"))
Return memListHeaders

Case Is = FinanceFunctionType.MemberList
Dim listName As String = args.MemberListArgs.MemberListName

If listName.Equals("Ranked", StringComparison.InvariantCultureIgnoreCase) Then
Dim memListHeader As New MemberListHeader(args.MemberListArgs.MemberListName)
Dim topNMems As List(Of Member) = Me.GetRankedMembers(si, api, args)
Dim topNMemList As New MemberList(memListHeader, topNMems)
Return topNMemList
End If

End Select

Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function

#Region "Data Ranking Helpers"

Private Function GetRankedMembers(ByVal si As SessionInfo, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As List(Of Member)
Try
'Create lists for ranking
Dim rankedCells As New List(Of MemberAndCellValue)

'Get the Passed in parameters
Dim rankType As String = args.MemberListArgs.NameValuePairs("RankType")
Dim rankCount As Integer = args.MemberListArgs.NameValuePairs("RankCount")
Dim loopMemberFilter As String = args.MemberListArgs.NameValuePairs("LoopMemberFilter")
Dim dataCellMemberFilter As String = args.MemberListArgs.NameValuePairs("DataCellToRankMemberFilter")
Dim cubeName As String = New MemberScriptBuilder(dataCellMemberFilter).Cube

'Excute the looping filter and retrieve the data cells to rank
Dim loopDimPk As DimPk = Me.GetDimPKForLoopMemberFilter(si, api, cubeName, loopMemberFilter, dataCellMemberFilter)
Dim loopMembersToRank As List(Of MemberInfo) = api.Members.GetMembersUsingFilter(loopDimPk, loopMemberFilter, Nothing)
If Not loopMembersToRank Is Nothing Then
'Loop over all members and get the cells to be ranked
For Each loopMember As MemberInfo In loopMembersToRank
'Substitute the Entity and get the Data Cell
Dim mfb As MemberScriptBuilder = Me.GetSubstitutedDataCellMemberFilter(si, loopMemberFilter, dataCellMemberFilter, loopMember.Member.Name)
Dim dataCell As DataCellInfoUsingMemberScript = BRApi.Finance.Data.GetDataCellUsingMemberScript(si, cubeName, mfb.GetMemberScript)
If Not dataCell Is Nothing Then
'Do NOT include invalid/No data
If Not (dataCell.DataCellEx.DataCell.CellStatus.Invalid) And (Not dataCell.DataCellEx.DataCell.CellStatus.IsNoData) Then
rankedCells.Add(New MemberAndCellValue(loopMember.Member, dataCell.DataCellEx.DataCell.CellAmount))
End If
End If
Next
End If

'Prepare the list of ranked Members
Dim rankedEntities As New List(Of Member)

'Order the Cells based on Rank Type
If rankType.Equals("Top", StringComparison.InvariantCultureIgnoreCase) Then
'Sort Descending and return Top N ranked members
Dim rankedResults = From s In rankedCells Order By s.CellValue Descending
Dim itemCount As Integer = 1
For Each memAndCell As MemberAndCellValue In rankedResults
If (itemCount <= rankCount) Then
rankedEntities.Add(memAndCell.EntityMember)
itemCount = itemCount + 1
End If
Next

Else If rankType.Equals("Bottom", StringComparison.InvariantCultureIgnoreCase)
'Sort Ascending and return Top N ranked members
Dim rankedResults = From s In rankedCells Order By s.CellValue Ascending
Dim itemCount As Integer = 1
For Each memAndCell As MemberAndCellValue In rankedResults
If (itemCount <= rankCount) Then
rankedEntities.Add(memAndCell.EntityMember)
itemCount = itemCount + 1
End If
Next

Else If rankType.Equals("Min", StringComparison.InvariantCultureIgnoreCase)
'Sort Descending and return first item (Minimum)
Dim rankedResults = From s In rankedCells Order By s.CellValue Ascending
For Each memAndCell As MemberAndCellValue In rankedResults
rankedEntities.Add(memAndCell.EntityMember)
Exit For
Next

Else If rankType.Equals("Max", StringComparison.InvariantCultureIgnoreCase)
'Sort Ascending and return first item (Maximum)
Dim rankedResults = From s In rankedCells Order By s.CellValue Descending
For Each memAndCell As MemberAndCellValue In rankedResults
rankedEntities.Add(memAndCell.EntityMember)
Exit For
Next
End If

'Return only the ranked members
Return rankedEntities

Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function

Private Function GetDimTypeForLoopMemberFilter(ByVal si As SessionInfo, ByVal loopMemberFilter As String) As DimType
Try
'Get the Dim Type of the Loop Filter
Dim filterParts As List(Of String) = StringHelper.SplitString(loopMemberFilter,"#", StageConstants.ParserDefaults.DefaultQuoteCharacter)
If filterParts.Count > 0 Then
Return DimType.GetItem(filterParts(0))
Else
Return DimType.Unknown
End If

Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function

Private Function GetDimPKForLoopMemberFilter(ByVal si As SessionInfo, ByVal api As FinanceRulesApi, ByVal cubeName As String, ByVal loopMemberFilter As String, ByVal dataCellMemberFilter As String) As DimPK
Try
'Get the cube to query
Dim cubeToQuery As CubeInfo = api.Cubes.GetCubeInfo(cubeName)
If cubeToQuery Is Nothing Then
Throw New Exception("Invalid cube name provided [" & cubeName & "]")
End If

'Build the DimPK for the Dimension used by the Loop Filter
Dim loopDimType As DimType = Me.GetDimTypeForLoopMemberFilter(si, loopMemberFilter)
Dim loopDimId As Integer = SharedConstants.Unknown

'Get scenario type that we are looping over so that we can get the proper dimension the cube that we are querying
Dim mfb As New MemberScriptBuilder(dataCellMemberFilter)
Dim scenario As Member = api.Members.GetMember(DimTypeId.Scenario, mfb.Scenario)
Dim loopScenarioType As ScenarioType = ScenarioType.Unknown
If Not scenario Is Nothing Then
loopScenarioType = api.Scenario.GetScenarioType(scenario.MemberId)
Else
Throw New Exception("DataCell member filter must include a valid Scenario Member for ranking.")
End If

'Get the Dimension ID for the looping dimension
Select Case loopDimType.Id
Case Is = DimType.Entity.Id
loopDimId = cubeToQuery.Cube.CubeDims.GetEntityDimId()

Case Is = DimType.Consolidation.Id
Throw New Exception("Consolodation is NOT a valid looping dimension for ranking.")

Case Is = DimType.Scenario.Id
Throw New Exception("Scenario is NOT a valid looping dimension for ranking.")

Case Is = DimType.Time.Id
loopDimId = SharedConstants.Unknown

Case Is = DimType.View.Id
Throw New Exception("View is NOT a valid looping dimension for ranking.")

Case Is = DimType.Account.Id
loopDimId = cubeToQuery.Cube.CubeDims.GetDimId(loopDimType.Id, loopScenarioType.Id)

Case Is = DimType.Flow.Id
loopDimId = cubeToQuery.Cube.CubeDims.GetDimId(loopDimType.Id, loopScenarioType.Id)

Case Is = DimType.Origin.Id
Throw New Exception("Origin is NOT a valid looping dimension for ranking.")

Case Is = DimType.UD1.Id
loopDimId = cubeToQuery.Cube.CubeDims.GetDimId(loopDimType.Id, loopScenarioType.Id)

Case Is = DimType.UD2.Id
loopDimId = cubeToQuery.Cube.CubeDims.GetDimId(loopDimType.Id, loopScenarioType.Id)

Case Is = DimType.UD3.Id
loopDimId = cubeToQuery.Cube.CubeDims.GetDimId(loopDimType.Id, loopScenarioType.Id)

Case Is = DimType.UD4.Id
loopDimId = cubeToQuery.Cube.CubeDims.GetDimId(loopDimType.Id, loopScenarioType.Id)

Case Is = DimType.UD5.Id
loopDimId = cubeToQuery.Cube.CubeDims.GetDimId(loopDimType.Id, loopScenarioType.Id)

Case Is = DimType.UD6.Id
loopDimId = cubeToQuery.Cube.CubeDims.GetDimId(loopDimType.Id, loopScenarioType.Id)

Case Is = DimType.UD7.Id
loopDimId = cubeToQuery.Cube.CubeDims.GetDimId(loopDimType.Id, loopScenarioType.Id)

Case Is = DimType.UD8.Id
loopDimId = cubeToQuery.Cube.CubeDims.GetDimId(loopDimType.Id, loopScenarioType.Id)

Case Else
Throw New Exception("Looping Dimension Type is Invalid.")

End Select

Return New DimPk(loopDimType.Id, loopDimId)

Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function

Private Function GetSubstitutedDataCellMemberFilter(ByVal si As SessionInfo, ByVal loopMemberFilter As String, ByVal dataCellMemberFilter As String, ByVal memberName As String) As MemberScriptBuilder
Try
'Substitute the Entity and get the Data Cell
Dim mfb As New MemberScriptBuilder(dataCellMemberFilter)
Dim loopDimType As DimType = Me.GetDimTypeForLoopMemberFilter(si, loopMemberFilter)

Select Case loopDimType.Id
Case Is = DimType.Entity.Id
mfb.Entity = memberName

Case Is = DimType.Consolidation.Id
mfb.Consolidation = memberName

Case Is = DimType.Scenario.Id
mfb.Scenario = memberName

Case Is = DimType.Time.Id
mfb.Time = memberName

Case Is = DimType.View.Id
mfb.View = memberName

Case Is = DimType.Account.Id
mfb.Account = memberName

Case Is = DimType.Flow.Id
mfb.Flow = memberName

Case Is = DimType.Origin.Id
mfb.Origin = memberName

Case Is = DimType.UD1.Id
mfb.UD1 = memberName

Case Is = DimType.UD2.Id
mfb.UD2 = memberName

Case Is = DimType.UD3.Id
mfb.UD3 = memberName

Case Is = DimType.UD4.Id
mfb.UD4 = memberName

Case Is = DimType.UD5.Id
mfb.UD5 = memberName

Case Is = DimType.UD6.Id
mfb.UD6 = memberName

Case Is = DimType.UD7.Id
mfb.UD7 = memberName

Case Is = DimType.UD8.Id
mfb.UD8 = memberName
End Select

Return mfb

Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function

#End Region

#Region "Helper Classes"

Public Class MemberAndCellValue
Public EntityMember As Member = Nothing
Public CellValue As Decimal = Decimal.Zero

Public Sub New(ByVal entityMember As Member, cellValue As Decimal)
Me.EntityMember = entityMember
Me.CellValue = cellValue
End Sub
End Class

#End Region