CubeView - Switch IC and Entity

MarcR
VIP

Hi all, 

In a CubeView we want to see selling and buying members in one row.

So for Column 1 is sales where the Entity is the selling entity and IC is the retrieving entity.

in the same CubeView Column 2 is stock where the Entity is the Buying Entity and IC the selling entity.

So for Column 1 i want to see E#Entity:I#IC and for column 2 E#IC:I#Entity.

This is straightforward in an xfGetCell in excel but then i cannot do row suppression.

I haven't found a way (yet) to get the current row's Entity and populate the IC dimension with that value using e.g. an xfbr rule.

 

Thanks in advance, 

Marc

Marc Roest
OneStream consultant @Finext
2 REPLIES 2

TheJonG
New Contributor III

I think the way to do this is to use an XFBR Rule to create a long string of Entity/IC member filters that will be based on some parameters defined in Name-Value pairs. The rule will loop through a defined list of entities and then loop through all IC Entities. Within the second loop (IC Entities) it will check a Data Cell for a POV defined by the Entity, IC, and Account and if there is data it will create the desired Member Script for both E#Entity:I#IC & E#IC:I#Entity. 

The below XFBR Rule script will do it or at least get you started. The XFBR Rule is called from the Rows of the Cube View with Entity assigned as the primary Dimension. The columns should be the IC Account pair which are also passed into the XFBR Script via the NV Pairs. I ran this in GolfStream with Houston selected as the EntityParent which would run it for all base entities of Houston. This value should be subbed with whatever your entity parent is.

TheJonG_1-1646939996575.png

 

XFBR(ICMatching, ICMatching, Cube = |CVCube|, EntityParent = Houston, Time = |POVTime|, Scenario = |CVScenario|, Account1 = 60100, Account2 = 42000)

If args.FunctionName.XFEqualsIgnoreCase("ICMatching") Then
					
					'Get information passed in from the Cube View
					Dim cvCube As String = args.NameValuePairs.XFGetValue("Cube")
					Dim entityParent As String = args.NameValuePairs.XFGetValue("EntityParent")
					Dim cvTime As String = args.NameValuePairs.XFGetValue("Time")
					Dim cvScenario As String = args.NameValuePairs.XFGetValue("Scenario")
					Dim ICAccount1 As String = args.NameValuePairs.XFGetValue("Account1")
					Dim ICAccount2 As String = args.NameValuePairs.XFGetValue("Account2")
					'Create a string builder
					Dim memFilter As New Text.Stringbuilder()
					Dim isfirstItem As Boolean = True
					
					'Get a list of entities and 
					Dim entityList As List(Of MemberInfo) = BRApi.Finance.Metadata.GetMembersUsingFilter(si, "HoustonEntities", "E#" & EntityParent & ".Base", False)
					Dim icEntitiesList As List(Of Member) = BRApi.Finance.Members.GetBaseMembers(si, dimpk.GetICDimPk, dimconstants.ICEntities)
					'Dim icEntitiesList As List(Of MemberInfo) = BRApi.Finance.Metadata.GetMembersUsingFilter(si, "Intercompany", "I#ICEntities.Base", False)
					'BRApi.ErrorLog.LogMessage(si,icEntitiesList.Count.XFToString)
					For Each entity As MemberInfo In entityList 
						For Each icEntity As Member In icEntitiesList
						'Check if this Entity/IC Combo has data
						Dim dataCellAmount As Decimal = brapi.Finance.Data.GetDataCellUsingMemberScript(si,cvCube,"E#" & entity.Member.Name & ":C#Local:T#" & cvTime & ":S#" & cvScenario & ":A#" & ICAccount1 & ":U1#Top:F#None:O#Top:I#" & icentity.Name & ":U2#Top:U3#Top:U4#Top:U5#None:U6#None:U7#None:U8#None").DataCellEx.DataCell.CellAmount
						If dataCellAmount <> 0.00 Then
							'Build the member filter with the entity/ic combo
							If Not isfirstItem
								memfilter.Append(",E#[" & entity.Member.Name & "]:I#[" & icentity.Name & "]:Name(Entity: |MFEntity|, IC: |MFIC|), E#[" & icentity.Name & "]:I#[" & entity.Member.Name & "]:Name(Entity: |MFEntity|, IC: |MFIC|)")
								Else
								memfilter.Append("E#[" & entity.Member.Name & "]:I#[" & icentity.Name & "]:Name(Entity: |MFEntity|, IC: |MFIC|), E#[" & icentity.Name & "]:I#[" & entity.Member.Name & "]:Name(Entity: |MFEntity|, IC: |MFIC|)")
							End If
							
						isfirstItem = False
						End If
						Next
					Next
					'BRApi.ErrorLog.LogMessage(si,memFilter.ToString)
					Return memFilter.ToString
				End If

 

 

 

Ludvigsen
New Contributor

Did you find a solution to this as I am creating a report with similar requirements where it needs to be in the columns and not added rows as TheJonG solutions gives?