June 20, 2012 at 2:41 pm
Howdy.
I've got a report that I built that runs fine in Reporting Services, but has a hiccup when exported to Excel.
The report is designed with 3 grouped (collapsible) fields in RS: Snapshot Date, Org, Recovery Code and then a number of fields containing the numerical reporting data.
Snapshot Date toggles Org and Org toggles Recovery Code and this works fine when the report is displayed in Reporting Services, but when the report is exported to Excel, toggling Snapshot Date also expands the 1st data point at the Org level.
Is there a way to insert a place holder or some sort of dummy space to get around this issue, so that all the levels collapse and expand properly in the Excel export?
Thanks
Stuart
June 20, 2012 at 2:54 pm
are you sure excel even supports a show / hide collapsing functionality?
I've never seen it; i think that is a property of html pages only.
Lowell
June 20, 2012 at 3:19 pm
Yes, it is possible, in fact, it's mostly working on this Excel export, as I explained above, it's just the 1st data point at the Org level ("Direct") uncollapses automatically when the Snapshot Date level is uncollapsed and in fact, "Direct" cannot be collapsed/uncollapsed autonomously, while the 2nd data point at the Org level ("Indirect") can be directly toggled.
From MSDN:
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:
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.
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.
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.
Due to an Excel limitation, outlines can be nested up to 7 levels only.
I'm assuming that the portion in bold explains the issue I'm having, but I'm not sure and I'm not sure how to work around it (if indeed it is the issue).
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply