Cube View - Column and Row Index

CAIGuySeanF
Contributor

Is it possible to use a variable or dynamic selection on the the index?  For example, I would like it to vary based on the cubeview time, e.g. - an index of 1:(SubPeriodNum).

See example below.

CAIGuySeanF_0-1714401006567.png

1 ACCEPTED SOLUTION

Krishna
Valued Contributor

@CAIGuySeanF  -

1. You have to create Dashboard XFBR String Rule under Business Rules.

2. Call the XFBR in your CV Col Ex XFBR(BRName,FunctionName, Param1 = Value1) Based on the below Code

XFBR(YourRuleName,YearParamRpt9,ParamYear=|!YourParam!|)

Hope this helps. 

				
				'Function Name
				If args.FunctionName.XFEqualsIgnoreCase("YearParamRpt9") Then
                'Get the Parameter from Cube     
                Dim Pov As String = args.NameValuePairs("ParamYear")

                
                'Mon            
					If FinalMon = "2024M3" Then
                
						Return "GetDataCell(CVC(Actuals, 1)+CVC(Actuals, 2)+CVC(Actuals, 3))"
		
					End If
				End If

 

Thanks
Krishna

View solution in original post

8 REPLIES 8

Krishna
Valued Contributor

@CAIGuySeanF  - Based on the Example in the Member filter builder it should work.

Krishna_0-1714404586878.png

 

Thanks
Krishna

Hey Krishna, I'm currently using that logic but want to make it dynamic.  My index needs to change each month as the column is AllPriorInYearInclusive.

For March, I needed the member filter to be..

GetDataCell(
CVC(Actuals, 1)+
CVC(Actuals, 2)+
CVC(Actuals, 3)
)

For April, I don't want to go back into the cubeview to add the 4th index.  I want to be able to automate it to pull all the values or base it off a parameter.

 

Krishna
Valued Contributor

@CAIGuySeanF  - Got it. You can use the XFBR for this based on the Time Period Filter it should return the GetDataCell formula. so you do not need to change every month.

Thanks
Krishna

Do you have some sample syntax that I could leverage?

Krishna
Valued Contributor

@CAIGuySeanF  -

1. You have to create Dashboard XFBR String Rule under Business Rules.

2. Call the XFBR in your CV Col Ex XFBR(BRName,FunctionName, Param1 = Value1) Based on the below Code

XFBR(YourRuleName,YearParamRpt9,ParamYear=|!YourParam!|)

Hope this helps. 

				
				'Function Name
				If args.FunctionName.XFEqualsIgnoreCase("YearParamRpt9") Then
                'Get the Parameter from Cube     
                Dim Pov As String = args.NameValuePairs("ParamYear")

                
                'Mon            
					If FinalMon = "2024M3" Then
                
						Return "GetDataCell(CVC(Actuals, 1)+CVC(Actuals, 2)+CVC(Actuals, 3))"
		
					End If
				End If

 

Thanks
Krishna

Thanks. I needed to modify the code a little bit, but this did the trick!  Appreciate the help!  Sharing my final syntax in the event it helps others.

 

2024-04-29_16-53-12.jpgsyntax.png

JackLacava
Honored Contributor

CubeViews aren't Excel; in most cases, it makes more sense to try and reason in terms of member expansions rather than in terms of cells.

For example, what you want can likely be obtained by having a "MyCol" column defined as:
S#Actuals:T#|CVTime|.AllPriorInYearInclusive
(you can set its visibility to "CVMathOnly" if you don't want to actually display all generated columns), then another column that simply sums up its values with:
GetDataCell(CVC(MyCol))

CAIGuySeanF
Contributor

When I try this approach, the getdatacell returns the value of only the first indexed column.  

So for example, if my CVTime was 2024M3.. the CVMathOnly column would actually generate three columns.. 2024M1, 2024M2, 2024M3.  The secondary column doing the get datacell only brings back 2024M1.