Forum Discussion
Solved
Group Columns/Excel Outline in Columns on Cube Views
Hello:
Has anyone had any success with grouping columns in a cube view in a report linked to excel, or grouping them as they export? Of course in the data explorer version you can use the tree ...
Hi James,
I did something similar lately.
Like you mentioned, I am not able to group Column as well.
But I am able to group Rows Dynamically by leveraging 3 components
1. Row member: member.Tree [I believe member.TreeDescendants also work]
2. Use RowE1IndentLevel + ExcelOutlineLevel in formatting
I have used following to make it dynamic
if (RowE1IndentLevel = 0) Then
ExcelOutlineLevel = 1
Else if (RowE1IndentLevel = 1) Then
ExcelOutlineLevel = 2
Else if (RowE1IndentLevel = 2) Then
ExcelOutlineLevel = 3
Else if (RowE1IndentLevel = 3) Then
ExcelOutlineLevel = 4
Else if (RowE1IndentLevel = 4) Then
ExcelOutlineLevel = 5
Else
ExcelOutlineLevel = 6
End If3. In Default Cube Formatting
ExcelExpandedOutlineLevelOnRows = 6
ExcelMaxOutlineLevelOnRows = 6 (6 is max)
As result, this is what you get in excel. best part if this is dynamic.
Hope this helps.
aneupane
OneStream Employee
OneStream EmployeeHi James,
I did something similar lately.
Like you mentioned, I am not able to group Column as well.
But I am able to group Rows Dynamically by leveraging 3 components
1. Row member: member.Tree [I believe member.TreeDescendants also work]
2. Use RowE1IndentLevel + ExcelOutlineLevel in formatting
I have used following to make it dynamic
if (RowE1IndentLevel = 0) Then
ExcelOutlineLevel = 1
Else if (RowE1IndentLevel = 1) Then
ExcelOutlineLevel = 2
Else if (RowE1IndentLevel = 2) Then
ExcelOutlineLevel = 3
Else if (RowE1IndentLevel = 3) Then
ExcelOutlineLevel = 4
Else if (RowE1IndentLevel = 4) Then
ExcelOutlineLevel = 5
Else
ExcelOutlineLevel = 6
End If
3. In Default Cube Formatting
ExcelExpandedOutlineLevelOnRows = 6
ExcelMaxOutlineLevelOnRows = 6 (6 is max)
As result, this is what you get in excel. best part if this is dynamic.
Hope this helps.
That's pretty cool. So if your tree structures go down to six levels, you can dynamically group your tree structures leveraging the hierarchy within the dimension itself. Is that the intended effect?
- aneupane
That's correct.....the indent level is picked from hierarchy indentation (provided by OS when .Tree is used). And we are using the indent value to generate the excel ExcelOutlineLevel.
Hi James,
I'm trying your code but does't work for member expansion. I'm using A#acoount.tree.
Do you know a way to play with the ident level inside a tree?Hi Carolina:
The code that aneupane references is not a member expansion, but rather, conditional formatting applied to a row with a .tree expansion, in addition to the default cube view formatting mentioned above. if you follow the steps, when you export the report to excel the outline levels will appear as mentioned. Let me know if that makes sense.
This looks like a great solution. Do you know if this functionality works when exporting to Excel (rather than using a Cube View connection)?
I think this only works when exporting. When you use a linked cube view connection I don't think it works.
Does anyone know how to get this to open all the way expanded as the default in excel but still hold the groupings? When I do ExcelExpandedOutlineLevelOnRows = 1 or ExcelExpandedOutlineLevelOnRows = 6 I get the same results of the top account only.
Thanks!
Thank you! It works for me in most cases, but I have an extra difficulty:
My Tree (used in Entity dimension) is:
Level 1: Group A
Level 2: Entity 1, Entity2, Entity3 ....
I would like to have different "cross dim" inside the .Tree function. I mean:
Level 1: Group A ---> C#Local:O#BeforeElim
Level 2: Entity 1, Entity2, Entity3 .... ---> C#Share:O#Top
Hopefully, I get the desired result with a POV = C#EUR:O#BeforeElim. However, I would like to know if it is possible to manage different "cross dim" inside a .Tree formula.
Hi ,
Thank you much for the detailed explanation.
However I try to copy and paste the same thing in Cub view and launch excel nothing appears to be reflected can you please guide me where I am going wrong.
Thank You
Is it possible to do the same in the PDF view? If so, can you share your code?
