Doug Lane

SQL Server Entertainer

Reporting Services 2012 Bug Causes Export to Excel Failure

by

Finally, FINALLY, I get to tell my boss the report subscriptions broke and it’s the server’s fault.

Here’s what happened: I took a report that ran perfectly on a SQL 2008 server and deployed it to Reporting Services on a SQL 2012 server. I tried to export it to Excel and got this delightful little message.

ExcelSSRSError
This particular report had no parameters and no custom code, so I was confident in ruling out an error on my part. I tried to work around it by exporting from the print layout, but still got the same error.
I then checked the Reporting Services log files and found this:

WARN: Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException: An error occurred during rendering of the report. —> Microsoft.ReportingServices.OnDemandReportRendering.ReportRenderingException: An error occurred during rendering of the report. —> System.ArgumentOutOfRangeException: Length cannot be less than zero.

While the detail of that message didn’t help me deduce anything new, it did give me more precise phrasing with which to search. That led me to find this confession connect item from Microsoft: SSRS 2012 Export to Excel Footer bug. This is the Connect Team’s response to the item:

TheyBrokeExcel

Although there aren’t any workarounds added to the item, there’s one outlined in the initial item description. It says to place footer text in an image and drop that into the footer instead of a text box. I tried a number of other workarounds using background images, tables, and rectangles, but this is the only one I’d say was reasonably successful.
A few things to keep in mind if you’re going to need this workaround:
  • Save your text-as-image at a print quality resolution. 300 dpi is good. My company recently standardized headers and footers in our reports and we discovered the chunky, almost unreadable output of a 72 dpi image.
  • You still have 255 characters that will render. As the Connect Team comment says, some of that will be lost to the formatting tags. Still, if you put information like report date or page numbers, they’ll likely fit and render properly. It’s the long text like disclaimers and footnotes that you’ll need to use an image for.
  • This is only for SQL Server 2012. I’ve tested it and versions 2008 and 2008 R2 don’t fail this way. Consider passing broken reports through your 2008/2008 R2 report servers if that’s an option.

I like this workaround as a general practice anyway. At my company, we’re using images for the disclaimer in our report footers. If the text needs updating, we simply update the image and the reports that call it pick up the new image automatically. This technique is very simple to implement. Just do the following (I’m using SSDT):

  1. Right-click on the Reporting Services project name and choose Add > Existing Item.
  2. Change the file type drop-down to “All Files”.
  3. Find the image you want to use and click “Add”. Unfortunately, there isn’t a Miscellaneous folder like there are in SSIS and SSAS projects. Instead, your image will get added to the Reports folder.
  4. Add a new image to the report. The Image Properties window will appear.
  5. Change the “Select the image source” drop-down list to External.
  6. Choose the image from the “Use this image” drop-down list. Images attached to the project will be at the top of the drop-down list.
  7. Click “OK”.

The Connect item is “Closed as Fixed”, meaning they made sure future versions won’t have this issue. However, this doesn’t mean there’s a fix available for it right now. For the time being, the workaround is the best way to get the export to Excel function to not error out. This is a big enough deal that I hope the fix is coming in the next service pack, and we won’t have to live with it until the next full product version.