Is there a View dimension member could display both text and number?

[Jacky_C]

Hi all,

I was not able to find a detailed guidance on how/when to use different view dimension members, so I would like to seek for advice here.

In a cube view, I have a member formula which return either a text or number based on different conditional.

For example, if conditional A is met, return a calculated value (sum of Account A and Account B), else return "Not applicable" text. 

I notice that we can utilize V#Annotation to display the text, but then it cannot display the number. So, I would like to know if there is a view member to allow display of both number and text. 

Thank you so much!

[KarlT · Accepted solution]

Hi Jacky, you could achieve this through a dynamic calc member by just retrieving the relevant View data cell (YTD holds the value and Annotation hold the text) depending on that condition. This way the data is stored in the appropriate location and you just grab the bit you need on the reporting end using the dynamic calc member.

[Jacky_C]

Hi KarlT, thanks for your reply. I have a dynamic calc member created but not quite sure if I set up the formula and the cube view correctly. 

Dim A As DataCell = api.Data.GetDataCell("A#100:V#YTD")
Dim B As DataCell = api.Data.GetDataCell("A#200:V#YTD")

If conditional A is met Then
  Return api.Data.GetDataCell(A.cellamount + B.cellamount)
Else
  Return "N/A"
End if

With the above member formula, I am not sure how should I set up the view dimension in the cube view. It would great if you can provide some guidance on this. Thank you. 

[KarlT]

Ah okay, i understand. The issue is the cell POV means that if you have it as V#YTD you can't see the text value, and if it's set to V#Annotation you can'y see the values.

One option would be to set the POV of the CV row/column to V#Annotation and then return the CellAmountAsText in your dynamic calc. But this will lose any number formatting in the CV i believe.

The only other way i can think would be to use an XFBR in the row/column to check the condition and return different view members also...

[Jacky_C]

Thanks for your suggestion. I am wondering is it possible to check the cell amount in XFBR rule? Because the conditional I was using is to check two accounts are positive or not.

I have applied the option one and it did work to display the results ("-" and calculated results) in cube view. But same as what you have mentioned, it loses number formatting and even the color formatting in CV. I am thinking is there a formatting to check the cell text such that I could retain the color formatting (i.e., positive figures = black, negative figures = red). 
thank you.

[KarlT]

HI Jacky,

You could certainly get some cell values in the XFBR (using api.data.GetDataCell) to help determine what you return as your member filter, but remember that it is only executed once for each row in the cube view definition.

Regards,