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.
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
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?