SSRS: Suppressing hidden row groups on Tablix when exporting to Excel

0
votes

Can Excel render only the visible row groups on a report?

I have a report [SSRS 2017] that has nested row groups on the Tablix. The child groups are hidden by default, toggled by a report field. When you run the report, the default view displays just the summary rows.

Folks naturally try to export this to Excel (to work with just the summary rows) and of course When they export to Excel (collapsed or not) they get the grouped child rows:

enter image description here

Is there any way for SSRS to suppress hidden row groups when exporting to Excel (while still having a working toggle on the web version of the report)?

My fallback is to duplicate the report, remove the child row groups altogether, and just link to the "simplified" version of the report for that purpose.

thanks!

2
reporting-servicesssrs-tablix

2 Answers

0
votes

I haven't done this before but I have seen the theory for this once.

You would want to add an extra column and use it as the Toggle Item. Then set the visibility for the new column based on whether it's an EXCEL export.

=IIF(Globals!RenderFormat.Name="EXCEL" or Globals!RenderFormat.Name="EXCELOPENXML", True, False)

I haven't seen it work, so I don't know if will work the way you want.

0
votes

The solution above does not work if you want to hide detail rows shown by drilling down.

For this case there's another way:

  1. Create a boolean parameter to "suppress details" for example ExcelHide.
  2. Create a copy of the tablix you want to hide the details from.
  3. Set visibility parameter of the ORIGINAL tablix to the value of the parameter. This will HIDE this tablix when the parameter is true.
  4. Set visibility parameter of the COPIED tablix to the negated value (not ExcelHide) of the parameter. This will SHOW this tablix when the parameter is false.
  5. On the COPIED tablix, hide all elements you do not want to export to Excel.

When the report is run you set the parameter so you can show the details for regular operation and hide elements to allow successful export to Excel.