Displaying Text Values in Cubeview

[miked]

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

[miked]

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 · Accepted solution]

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]

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]

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]

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]

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]

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