Forum Discussion

Jacky_C's avatar
Jacky_C
New Contributor III
8 months ago

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

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 retu...
annotations
Cube View
  • KarlT's avatar
    KarlT
    8 months ago

    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.

KarlT's avatar
KarlT
Contributor III

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's avatar
Jacky_C
New Contributor III
8 months ago

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's avatar
    KarlT
    Contributor III
    8 months ago

    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's avatar
      Jacky_C
      New Contributor III
      8 months ago

      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's avatar
        KarlT
        Contributor III
        7 months ago

        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,