Skip to main content
M
miked
Contributor
August 18, 2022
Solved

Displaying Text Values in Cubeview

  • August 18, 2022
  • 3 replies
  • 0 views

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

Best answer by btr200

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

3 replies

M
mikedAuthor
Contributor
August 18, 2022

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.

B
btr200Answer
Newcomer
August 19, 2022

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

M
mikedAuthor
Contributor
August 19, 2022

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

B
bilbrandon
Contributor
September 8, 2022

Hi, Mike.

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

Members_with_Text.png

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

M
mikedAuthor
Contributor
September 9, 2022

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

B
bilbrandon
Contributor
September 9, 2022

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

Terms & ConditionsAccessibility statement