Forum Discussion

JamesKirkby's avatar
JamesKirkby
New Contributor III
4 years ago

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

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,

  • Ashok's avatar
    Ashok
    New Contributor III
    2 years ago

    we are using but see this wierd issue with .Tree rows. Last row does not ge grouped properly. has anyone seen it? is there any workaround? 

    • WiskiOne's avatar
      WiskiOne
      New Contributor II
      2 years ago

      Hi Ashok,

      I have experienced the same problem. My last row is a Total row, and this problem desappears for previous grouped rows. If it is not your case, I would try to add a blank row at the end (format: font an background color white).

      Let me know if that makes sense.

    • Ashok's avatar
      Ashok
      New Contributor III
      2 years ago

       

      example where it does not group last row properly. 

  • 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
      3 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? 

  • aneupane's avatar
    aneupane
    Contributor
    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.

     

    • SusanDarragh's avatar
      SusanDarragh
      New Contributor
      31 days ago

      Is it possible to do the same in the PDF view?  If so, can you share your code?

    • NoorMohd's avatar
      NoorMohd
      New Contributor
      6 months ago

      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 

       

    • WiskiOne's avatar
      WiskiOne
      New Contributor II
      2 years ago

      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.