Forum Discussion

JamesKirkby's avatar
JamesKirkby
New Contributor III
4 years ago
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 ...
Excel Add In
Reports
  • aneupane's avatar
    aneupane
    4 years ago

    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 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.

     

MarkBird's avatar
MarkBird
Contributor III
2 years ago

Hi JamesKirkby 

Appreciate this is an old post, but I've been struggling to get the outline's between excel and cube views to work properly and came across this.

May have not been possible back then, but you can group columns in a similar way to grouping rows.

Either manually apply the outline level to the Header Format of each column: E.g. 'ExcelOutlineLevelCol = 2'

Or apply some logic like the ones mentioned below: "If (ColE1IndentLevel = 1) Then ExcelOutlineLevelCol = 2...."

Another option that you could follow is to apply a naming convention to your columns and then apply something like this:

If (ColName StartsWith 'C1_') Then
    ExcelOutlineLevelCol = 1,
If (ColName StartsWith 'C2_') Then
    ExcelOutlineLevelCol = 2,
End if

Then you just need to set the following on the Default Header Format of your cube view:

ExcelExpandedOutlineLevelOnCols = 1,
ExcelMaxOutlineLevelOnCols = 6,