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.

     

aneupane's avatar
aneupane
Contributor
4 years ago

ExcelExpandedOutlineLevelOnRows = 6 controls what level to show when cubeview is open.

If ExcelExpandedOutlineLevelOnRows = 1; cubeview opens with Level 1 shown and others compressed like shown below.

 

 

CAIGuySeanF's avatar
CAIGuySeanF
Contributor
4 years ago

This is great.  Question - there is no way to pull the tree reversed?  I know I can do tree decedents reversed to get the desired excel grouping (totals beneath), but that doesn't allow for me to collapse the tree in the client/app.  Any suggestions?