Forum Discussion

miked's avatar
miked
Contributor
3 years ago

Displaying Text Values in Cubeview

Hi All,

 

I am wondering if there is a way to display a text value in a cube view cell directly from a cubeview member filter?  In other words, in the same way that you can use a member filter with GetDataCell to force a number to be displayed (ie. A#Net_income:GetDataCell(100) will display 100), can you do something similar for text values like A#Net_Income:GetDataCell(Hello) to display Hello in the cell?  It doesn't work for me and I have also tried using A#Net_Income:V#Annotation:GetDataCell(Hello) and that doesn't work either.

 

Any thoughts?

 

Thx,

Mike

Cube View
  • brooney's avatar
    brooney
    3 years ago

    HI Mike,

    The best way to achieve this would be to create a dynamic member (maybe in the Flow/UD8 dimension) that tells it what to pull (could be logic, could just be 'Return "Hello"'. You would then put that on your CubeView row/column with a view of Annotation (V#Annotation:UD8#Dynamic_Hello).

    Thanks,

    Brian

  • miked's avatar
    miked
    Contributor
    3 years ago

    Related to my earlier post, there is mention in the Design Guide that you can use GetDataCell with XFMemberProperty.  See excerpt below.  This would suggest GetDataCell can accept text values but I wonder if it's a typo and XFMemberProperty only really works with the Name function.

     

    Thx,

    Mike

     

    XFMemberProperty
    This function allows users to specify a Dimension property and display the Member Property
    selection as a row or column header on a Cube View. Use this function with the Name() and
    GetDataCell() portion of a Member Filter.

  • brooney's avatar
    brooney
    New Contributor II
    3 years ago

    HI Mike,

    The best way to achieve this would be to create a dynamic member (maybe in the Flow/UD8 dimension) that tells it what to pull (could be logic, could just be 'Return "Hello"'. You would then put that on your CubeView row/column with a view of Annotation (V#Annotation:UD8#Dynamic_Hello).

    Thanks,

    Brian

    • miked's avatar
      miked
      Contributor
      3 years ago

      yes agreed.  I was just hoping we could do this all from the cubeview to prevent setting up UD8 members to hold dynamic accounts.  Ultimately, we are looking to provide a report of all of the metadata and all of the member properties for each dimension and we like the way it presents in a cubeview.  However, to achieve this we need to display text values and wanted to avoid setting up a ton of member in the UD8 dimension.

       

      My guess is it's a typo in the documentation where it says the XFMemberProperty works with the GetDataCell function.  I don't believe it's correct.  Oh well, if anyone else has any ideas about this let me know.

       

      Thx,

      Mike

  • bilbrandon's avatar
    bilbrandon
    New Contributor III
    3 years ago

    Hi, Mike.

    To clarify, you want a list of members along with the Text1-Text8 values for each? Example below.

    I have done this with a data adapter and it displays in a dashboard. Can send you the code if that would help.

    • miked's avatar
      miked
      Contributor
      3 years ago

      Hi Bill,

       

      I was ultimately hoping to produce a report like the one you describe without the need for adding any dynamic formula metadata members.  Was hoping I could create a cube view solely using XFGetMemberProperty functions and populate the cells with annotation values showing each metadata property of given members.  So my content would look similar to your report above.

       

      Sure, I would be interested in seeing your code.  Much Appreciated!

      Mike

      • bilbrandon's avatar
        bilbrandon
        New Contributor III
        3 years ago

        This is a simple query of the OneStream Application database. Just two tables:

        Member (Aliased below as M)
        MemberProperty (Aliased below as P)

         

        Query:

        Select
        M.Name,
        M.Description,
        Min(Case P.PropertyID When '900100' Then P.TextValue End) Text1,
        Min(Case P.PropertyID When '900200' Then P.TextValue End) Text2,
        Min(Case P.PropertyID When '900300' Then P.TextValue End) Text3,
        Min(Case P.PropertyID When '900400' Then P.TextValue End) Text4,
        Min(Case P.PropertyID When '900500' Then P.TextValue End) Text5,
        Min(Case P.PropertyID When '900700' Then P.TextValue End) Text7
        From Member M
        Join MemberProperty P
        ON M.MemberID = P.MemberID
        Where M.DimID = '13'
        Group By M.Name, M.Description

         

        For my report, I'm interested in UD2. To find the DimID in the Member table, I simply filtered for one of the base UD2 members and made note of the value. This will likely be different in your system. The PropertyID values should be the same in all systems. Text1 = 900100, Text2=900200, etc.

         

        Once the query is working in a data adapter, you can add it to a GridView, then assign the Gridview to a dashboard. Or...something more creative.

        Hope this helps.

         

        Thanks,
        Bil