Cube View Running Sum

Mustafa_A
Contributor II

Please see this post from Adam. Posting on his behalf. I also want to know the answer to this.

Mustafa_A_1-1644606989176.png

 

 

8 REPLIES 8

ChrisLoran
Valued Contributor

There are two ways to do this. However you don't state what your "business rule" is for retrieving the top 5 sorted clients.  I assume your clients are in a UD dimension?  Is the ranked list currently using a custom member list, or is it an XFBR string that returns the member names inside a string? This cumulative ranking would be easier to do if the client detail was in a relational table and reported in OS using relational blend. Be careful about data unit sizes when consolidating these.  Anyway, back to the cumulative ranking question:
Option 1) Use your existing custom member list or XFBR to retrieve the top 5 sorted clients, as you are probably doing at the moment. Then you would need a UD8 DynamicCalc for the cumulative element. This DynamicCalc would have to re-generate the top 5 sorted clients (again) in a list in memory, and aggregate the amount, up until the point that the client is the member that is being displayed on this row (from api.pov). For performance , once the DynamicCalc has re-generated the ranked list of clients with their amounts (in a List or Dictionary), then store it in the BRGLobals , so it doesn't have to re-generate the list on every row.
Option 2)  Since you are only looking at top 5, you could design the cube view with 5 fixed "design rows" (excluding totals etc). In each row in your cube view design, say XFBR( HelperRule, GetRankedClient, Rank=1) for the first row, through to XFBR( HelperRule, GetRankedClient, Rank=5) on the last row.  This makes the cube view design more fixed/less flexible, but it makes the accumulation of amounts MUCH easier  because then you can just use the Cube View CVR functions (as documented in the design & reference guide). So row 2 would be a CVR expression of Row1+Row2....  up to Row 5 that would be a CVR expression =Row1+Row2+Row3+Row4+Row5

With more background info (which dimension types your are using etc, what sort of business rule do you have for the ranking already) then I might have a suitable example that best-fits your particular case.

kakouchtim
New Contributor II

Hi Chris,

Many thanks for this your swift feedback. I am actually using a BR for the ranking (I adjusted the XFR_MemberListRanked Business Rule from GolfStream for my specific need). This BR retrieves list of Clients from UD1 and generates inside a string my top 5, by Net Revenues (UD6). 

I guess the Option 1 would be more suitable. What do you think ? Also could you please explain to me what do you mean by "BRGLobals"

 

Thanks again for your help !

Regards,

Adam

ChrisLoran
Valued Contributor

So, in my example in Golfstream I use the Custom Member list  XFR_MemberListRanked, based on the entity dim. In your case you are ranking on the UD1 dim. So bear this in mind.

What you need to do, is share the ranked list generated by XFR_MemberListRanked, so it can be re-used by a DynamicCalc UD8 member in the CubeView cells.

To do this, once you have generated your list of ranked members (in the XFR_MemberListRanked BR), you can store it in the SessionState, like this:

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)

'---- Now remember the topN Members in a Session State, as a comma separated list of names ---
Dim lstMemberNames As New List(Of String)
For Each mbr As Member In topNMems
lstMemberNames.Add(mbr.Name)
Next
Dim strMemberNames As String = String.Join(",", lstMemberNames)
brapi.State.SetSessionState(si, False, ClientModuleType.Unknown, "String", "Rank", "Rank", si.UserName, strMemberNames, Nothing )

Return topNMemList


Again this is based on the Golfstream XFR_MemberListRanked. If you have adapted this to work as an XFBR rule (instead of a Custom Member List rule) then you will need to adapt this accordingly.

Then create a UD8 dynamic calc member, and refer to it in a column in your cubeView , like this:
U8#GetCumulativeRanked:Name("Ranking Cumulative")

The DynamicCalc formula looks like this (REMEMBER change Entity to UD1 in your case)

If lstRankedNames Is Nothing Then
Dim strRankedMemberNames As String = brapi.State.GetSessionState(si, False, ClientModuleType.Unknown, "String", "Rank","Rank", si.UserName).TextValue
lstRankedNames = strRankedMemberNames.Split(","c).ToList()
End If

Dim sbAccumExpression As New Text.StringBuilder

For Each strMbrName As String In lstRankedNames
If sbAccumExpression.Length = 0 Then
sbAccumExpression.Append( String.Format("U8#None:E#[{0}]", strMbrName) )
Else
sbAccumExpression.Append( String.Format("+ U8#None:E#[{0}]", strMbrName) )
End If

If strMbrName.XFEqualsIgnoreCase(api.Pov.Entity.Name) Then Exit For
Next

Return api.Data.GetDataCell( sbAccumExpression.ToString() )


'---- Now put in the section "Helper Functions Header..."
Private Shared lstRankedNames As List(Of String) = Nothing



Hi Chris - Do you happen to have the XML for this updated BR showing exactly where to add the SessionState member list code into the existing BR?

Regardless, this post is very helpful. Thank you!

No I could only put in text , it won't let me upload XLM files as attachments. 
The code text should already be in the previous response, suggest copy/paste from there.

ChrisLoran
Valued Contributor

ChrisLoran_0-1644918334152.png

Formula should look like this.  IMPORTANT: Change lines 33/35 from E# to U1# for your case, and also change line 38 from api.Pov.Entity.Name to api.pov.UD1.Name   , since you are using UD1 for ranking.

IMPORANT: the lstRankedNames must be of type Shared (so it gets shared across multiple instances) , and it must also be declared in the "Helper Functions" area of the code editor.   This assumes you have adapted your XFBR or XFR Business Rule to write the "Rank" list to the SessionState (back in part 1) of this answer.

ChrisLoran
Valued Contributor

The result looks like this:

ChrisLoran_1-1644918512790.png


If you do a LogMessage inside the DynamicCalc , then you will see it generating these member expressions dynamically:

ChrisLoran_2-1644918574921.png

 

ChrisLoran
Valued Contributor

In case anyone is wondering why you can't use BRGlobals to share data between the XFBR/XFR_MemberList rule, and the DynamicCalc, is because the BRGlobals object gets destroyed between generation of the rows/column lists, and display of the data cells. Therefore we are forced to use SessionState in this particular case.   You could however use BRGlobals to save the ranked list in the DynamicCalc once it has been retrieved. The use of a Shared variable achieves the same objective as using BRGlobals (but in a better way)