The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

miked's avatar
miked
Contributor
3 years ago
Solved

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 wi...
Cube View
  • btr200's avatar
    btr200
    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

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

  • miked's avatar
    miked
    Contributor
    3 years ago

    Thanks Bill.  Yes, have something similar with SQL as well.  The member property codes are available via VB.net but not in SQL...annoying 🙂

     

    Much Appreciated,

    Mike