SSRS export report to excel without rendering in browser and remove hidden columns

2
votes

How I can direct export report from SSRS to Excel (xls, xlsx) without necessary rendering it in web browser because report is too big and rendering is very slow?

And how I can remove hidden columns and rows from this exported report.

1
sql-serverreporting-servicesssrs-2016
If my answer isn't what you were looking for, please include some screenshots/configuration and what you have tried so far so the community can provide a better answer. – scratt
What about delivering this as an Excel subscription? – Harry

1 Answers

1
votes

In order to export the report directly, you need to use a URL that will perform the export to your format. Here is the Microsoft Docs reference.

Here an example of the URL to export as XLSX:

http://myrshost/ReportServer?/myreport&rs:Format=EXCELOPENXML

The hidden columns and rows may be related to tablix rows and columns that are set to hidden. The Excel formatted report also includes small blank cells for any whitespace you have in your design. To my knowledge, you cannot remove them without affecting the layout of the Excel file. When I know a report will be exported to Excel, I include only the table/Tablix with no whitespace on the report. That produces a pretty clean Excel file.