Forum Discussion

MarcR's avatar
4 years ago

CubeView - Switch IC and Entity

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

2 Replies

  • TheJonG's avatar
    TheJonG
    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.

     

    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's avatar
    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?