locked
lost collapsing columns when export to excel RRS feed

  • Question

  • I have created a report with row grouping, works correctly working in Report Server Browser, 
    after export the report to excel, when clicking on the + in Excel only 1 or 2 of about 100 lines appears, or there is no + head to click for other groups

    a similar report works when exported to excel.

    any solution what to do or whats possibly wrong?

    thank you
    Sunday, November 23, 2014 9:14 AM

Answers

  • Hi Karl,

    According to your description that you are experiencing the issue when you export the report to excel, drill down report not display all the fields after expand the "+"and some group which should have "+" not shown, right?

    I have tested on my local environment and can’t reproduce the issue, the issue can be caused by the incorrect setting of the visibility and also can be related to the excel rendering.

    Microsoft Excel has limitations with how it manages hidden and displayed report items when they are exported. Groups, rows, and columns that contain report items that can be toggled are rendered as Excel outlines. Excel creates outlines that expand and collapse rows and columns across the entire row or column which can cause the collapse of report items that are not intended to be collapsed. In addition, Excel's outlining symbols can become cluttered with overlapping outlines. To address these issues, the following outlining rules are applied when using the Excel rendering extension:

    1. The report item in the top-left corner that can be toggled can continue to be toggled in Excel. Report items that can be toggled and share vertical or horizontal space with the report item that can be toggled in the top-left corner cannot be toggled in Excel.
    2. To determine whether a data region will be collapsible by rows or columns, the position of the report item that controls the toggling and the position of the report item that is toggled are determined. If the item controlling the toggling appears before the item to be toggled, the item is collapsible by rows. Otherwise, the item is collapsible by columns. If the item controlling the toggling appears beside and above the area to be toggled equally, the item is rendered with row collapsible by rows.
    3. To determine where the subtotals are placed in the rendered report, the rendering extension examines the first instance of a dynamic member. If a peer static member appears immediately above it, the dynamic member is assumed to be the subtotals. Outlines are set to indicate that this is summary data. If there are no static siblings of a dynamic member, the first instance of the instance is the subtotal.
    4. Due to an Excel limitation, outlines can be nested up to 7 levels only.

    Article about the show and hide in the excel for your reference:
    http://msdn.microsoft.com/en-us/library/dd255234.aspx

    Thanks for your understanding.

    Regards
    Vicky Liu

    • Edited by Vicky_Liu Monday, November 24, 2014 11:46 AM
    • Proposed as answer by Vicky_Liu Monday, December 1, 2014 1:06 AM
    • Marked as answer by Vicky_Liu Tuesday, December 2, 2014 1:09 AM
    Monday, November 24, 2014 11:45 AM

All replies

  • As a general rule, I tend not to expect much from reports exported to excel. Certainly not heavily formatted reports.

    There are a few ways of dealing with it, depending on the reports construction... These are the two I use most often.

    1) Create a simplified version of the report that is just a simple tablix with no bells & whistles that is designed solely for export to Excel. This allows users work with the data just like they are used to. No merged or hidden cells to contend with and no stepped groups to create aggravation.

    2) Add an "Expand All" parameter that can be used to auto-expand all of the report's drill down all at once, before they export to Excel.

    HTH,

    Jason


    Jason Long

    Sunday, November 23, 2014 5:33 PM
  • Hello Jason, thank you for your answer, 

    for the user, the look with arrows ...  is very important also in excel 
    so i have made the table new and now it works
    no idea whats wrong before


    Monday, November 24, 2014 7:25 AM
  • Hi Karl,

    According to your description that you are experiencing the issue when you export the report to excel, drill down report not display all the fields after expand the "+"and some group which should have "+" not shown, right?

    I have tested on my local environment and can’t reproduce the issue, the issue can be caused by the incorrect setting of the visibility and also can be related to the excel rendering.

    Microsoft Excel has limitations with how it manages hidden and displayed report items when they are exported. Groups, rows, and columns that contain report items that can be toggled are rendered as Excel outlines. Excel creates outlines that expand and collapse rows and columns across the entire row or column which can cause the collapse of report items that are not intended to be collapsed. In addition, Excel's outlining symbols can become cluttered with overlapping outlines. To address these issues, the following outlining rules are applied when using the Excel rendering extension:

    1. The report item in the top-left corner that can be toggled can continue to be toggled in Excel. Report items that can be toggled and share vertical or horizontal space with the report item that can be toggled in the top-left corner cannot be toggled in Excel.
    2. To determine whether a data region will be collapsible by rows or columns, the position of the report item that controls the toggling and the position of the report item that is toggled are determined. If the item controlling the toggling appears before the item to be toggled, the item is collapsible by rows. Otherwise, the item is collapsible by columns. If the item controlling the toggling appears beside and above the area to be toggled equally, the item is rendered with row collapsible by rows.
    3. To determine where the subtotals are placed in the rendered report, the rendering extension examines the first instance of a dynamic member. If a peer static member appears immediately above it, the dynamic member is assumed to be the subtotals. Outlines are set to indicate that this is summary data. If there are no static siblings of a dynamic member, the first instance of the instance is the subtotal.
    4. Due to an Excel limitation, outlines can be nested up to 7 levels only.

    Article about the show and hide in the excel for your reference:
    http://msdn.microsoft.com/en-us/library/dd255234.aspx

    Thanks for your understanding.

    Regards
    Vicky Liu

    • Edited by Vicky_Liu Monday, November 24, 2014 11:46 AM
    • Proposed as answer by Vicky_Liu Monday, December 1, 2014 1:06 AM
    • Marked as answer by Vicky_Liu Tuesday, December 2, 2014 1:09 AM
    Monday, November 24, 2014 11:45 AM