Developing Reports for Excel

One of the most popular popular formats for SSRS to export to is excel, especially for large data dumps or for further data processing, or just because it is so familiar to business users. Here are some things to consider when developing with this output in mind.

BadColumnAlignment
Bad column alignment

Attention needs to be paid to column alignment. You can cause a report column to be split over two excel columns by having another element above or below finish part-way through the column, which doesn’t look nice and can be impractical. I make sure to keep all column widths to multiples of 70pt to minimise this and then juggle things around if necessary.

GoodColumnAlignment
Good column alignment

For some reason, SSRS also adds a hidden column to the right edge of tables. Not sure why, but seems to be an SSRS bug. Sometimes you might need to add blank columns to a table to control where this hidden column occurs.

Any page breaks will cause separate sheets. This can be useful for showing an area per sheet e.g. a store per sheet, plus an extra one for overall. To do this, select a group, then in Properties go to Group – Page Break – BreakLocation. You may need to experiment a bit to get this how you want it. You can also apply page breaks at the report object level or to a group on the Master report.

PageBreak
Page Break design example

You can set sheet names in the properties of a few different objects, including: Report – Initial Page Name, Tablix – PageName, Group – PageName. This could be at master or subreport level. It’s often useful to make the sheet name dynamic based on the group that you are repeating on each sheet.

DynamicSheetNames
Dynamic sheet names

Charts are rendered as images. They’re not linked to any data in the output spreadsheet, so they won’t update if you change anything. Users sometimes expect that they will be linked, so it’s best to advise early on to avoid disappointment!

Excel may not be able to accurately represent the colours you choose in the report, depending on the version of excel and what colours it has available to it. It’s best to do some tests and try to stick to basic colours.

There is no easy way of exporting excel formulas, but I have posted a workaround here: Exporting Excel Formulas.

If you’re producing more of a detailed data dump rather than a summary report, you probably don’t want so much of the headers, footers and logos on every sheet. You  could just show these once on an introduction sheet.

You can make all these things dynamic depending on what format the user exports to by referencing the built-in RenderFormat fields.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s