I agree with two others already posted:
- Dynamically name reports
The other poster wanted to do it for scheduled reports, but we have a different problem.
A user wants to use Excel for some data analysis. He/she wants to compare or merge data from the same report with different parameters. The user runs a report (Report.rdl) and exports it to Excel, which starts Excel and loads the report (Report.xls). Then the user clicks back into the report and changes some parameters, clicks View Report, then exports the report to Excel again. BAM! Error! It tries to export to Report.xls again, and Excel complains about having two files open with the same name. (In some cases, Excel 2010 actually hangs and has to be killed with Task Manager.)
If SSRS could either (1) allow us to vary the name at runtime or (2) automatically add a unique value (timestamp?) to the end of the name for each run, this would avoid conflicts.
- Render to xlsx
Come on, how long as the xlsx file been the Excel standard? Since 2007?
Another thing we HATE about the Excel renderer is the dreaded merged cells...
Our users LOVE to export reports to Excel. However, it takes much too much time for our designers to manipulate headings and the tablix so things line up just right to avoid merged cells, which prevent users from easily manipulating the data in Excel.
It seems like the SSRS team has made great efforts to make the exported report render EXACTLY the same in Excel as in HTML/PDF/etc. While those efforts are noble, they are not all that necessary - people aren't exporting the report to Excel so they can print it - they are doing so generally for data analysis and manipulation - they don't care if the headings aren't exactly the same as the printed report.
My recommendation would be to add a report property that turns of "exact" rendering in Excel and avoids all merged cells. However it comes out, it comes out. We'll deal with the formatting.
(There is a "workaround" in SSRS, which is not really acceptable - there is a setting that turns off exporting of headers to Excel altogether and puts them in a comment field in the spreadsheet, across all reports. That's silly.)