Cube view using complex function Account

[MarioGV]

Hello; Im crating a cube view with 2 rows (maybe more) that one of them is a result of a complex function:

"A#INCOME_STATEMENT.Base.Where(Text1 Startswith On-Campus)" and shows several rows qualified as Text1, now I want to sum those Accounts but don't like to use each Account; it could be possible to use a function in a 2nd row like this:

GetDataCell(A#INCOME_STATEMENT.Base.Where(Text1 Startswith On-Campus))::Name(On-Campus)

is that possible?

Thanks in advanceand best Regards.

Mario GV

[BenvanBergen]

Hello Mario, For something similar I created a member, for me under UD7 using Attributes. That gives the option to select all items with the Text1 On-Campus. For me all data on UD7 is loaded as None, you need to specify where the data is originally available on. Then you can select the UD7 member in the cubeview and not the account.

I hope this helps.

[Omkareshwar]

You can write an XFBR Rule

Within that rule you can pull all the members in a list using this code

Dim Accounts As List(Of String) = BRApi.Finance.Metadata.GetMembersUsingFilter(si, "DimensionName","A#INCOME_STATEMENT.Base.Where(Text1 Startswith On-Campus)",True).Select(Function(c) c.Member.Name).ToList

This will pull aur your relevant accounts in a list then you can loop over that list create an empty string and append all you accounts in this format A#Account1 + A#Account2 and sooo on 

then add that to the getdatacell and return in your cube view 

[ckattookaran]

Why not use a CVR function?

[BenvanBergen]

Hi Celvin, when I use a CVR function it would only sum the first account from the .base drilldown. Is there a way to sum all? That would make it easy.

Thanks

[JackLacava]

Remember you can always hide a row or column, setting the IsRowVisible (or IsColumnVisible) property of Header Formatting. If you set it to CVMathOnly, the row/column will be available for math operations, but won't be visible.

So for example, you could have a row defined with your complex filter, but not displayed; and then you could have another row that gives you the sum of those values.

[Michel_Sabourin]

I had a similar use case, and what I did was create a UD8 member named Ent_By_Text to total up using a text field

Dim viewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId)
If viewMember.IsAnnotationType Then
	' Return an empty string if this is a text-based dataCell.
	Return ""
End If

Dim MGMTid As Integer = api.Members.GetMemberId(DimType.Entity.Id, "MGMT") 'Where "MGMT" is the top-most entity in the hierarchy
Dim baseEnts As List(Of Member) = api.Members.GetBaseMembers(api.Pov.EntityDim.DimPk, MGMTid, Nothing)
Dim povText As String = api.Entity.Text(3)
Dim LocTot As Decimal = 0
If Not baseEnts Is Nothing Then
	For Each CurrEnt As Member In baseEnts
		Dim currText As String= api.Entity.Text(current.MemberId,3)
		If currText = povText Then
			LocTot = LocTot + api.Data.GetDataCell("E#" & CurrEnt.Name & ":T#[" + api.Pov.Time.Name _
				+ "]:V#" & api.Pov.View.Name & ":C#" & api.Pov.Cons.Name & _
				":O#Top:I#Top:F#Top:U1#Top:U2#Top:U3#Top:U4#" & api.Pov.UD4.Name & _
				":U5#Top:U6#None:U7#None:U8#None").CellAmount
			'brapi.ErrorLog.LogMessage(si, "Test-Entity~" & CurrEnt.Name & "~povText~" _
			'	& povText & "~currText~" & currText & "~LocTot~" & LocTot)
		Else
			LocTot = LocTot
		End If 'If TargetCurr = povCurr Then
	Next 'CurrEnt
	If LocTot <> 0 Then
		Return LocTot
	Else
		Return Nothing
	End If
Else
	Return Nothing
End If 'If Not baseEnts Is Nothing Then