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 function and toggle the row expansion mode to get the intended effect, and in rows you have the excel outline settings you can play with. However, there doesn't seem to be a way to manage this using settings. And obviously if you format the cube view when linked them refreshed the column grouping goes away.
Interested to hear and thanks!
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
ExcelOutlineLevel = 6
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.
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.