917.848.7284Data for Finance and Accounting

Top

How to Export to Excel Without Making a Mess

How to Export to Excel Without Making a Mess

Many, if not most, of the SSRS reports I write end up being exported to Excel. But unfortunately, many reports don’t export cleanly. More often than not, you wind up with merged cells and blank spaces you don’t want.

In this blog post, I’ll show you how to build your report correctly for a cleaner export to Excel.

Exporting to Excel

I’ve used the AdventureWorks database for all my examples.

1. Eliminating Merged Cells from SSRS Export to Excel

Merged cells are one of the great annoyances of exporting to Excel. Here’s a simple example of how to resolve this issue using sales data:

Export to Excel 1

When we export this to Excel, the sales order ID column is a merged column of Columns C and D.

Export to Excel 2

This is a really simple report. So, why do we have this merged column issue?

If we look at the report layout, we can see that the text box from the header aligns to the middle of the column for sales order ID:

Export to Excel 3

This is what’s causing the issue.

While it’s theoretically possible to make sure that all your text boxes align exactly with your Tablix columns, I haven’t tried it as my columns change way to often.

The easier way to fix the problem is to simply hide the text box. To do this, we click on the text box and set visibility to Hide:

Export to Excel 4

When I rerun the report and export to Excel, the sales order column is no longer merged. Instead, it’s only covering Column C:

Export to Excel 5

Now, of course, we do want the header information to print sometimes. For that, we create a parameter called Format for Excel:

Export to Excel 6

And we assign it two values – Yes and No:

Export to Excel 7

Then we hide the text box when FormatForExcel = Yes:

Export to Excel 8

And voila, our columns no longer merge when we export to Excel.

2. Removing Extra Columns and Rows from SSRS Export To Excel

We may have solved the merged column issue, but we may still have the problem of extra columns and rows. This isn’t the end of the world, but it’s not great either. Because, as I’ve said before, you won’t get credit for what’s hard. But you may get credit for what’s pretty.

Going back to Excel, say we have an extra column to the left:

Export to Excel 9

To get rid of extra columns, simply make sure that your Tablix is aligned to the right border of the report.

In this case, we highlighted the table and find location in the properties box:

Export to Excel 10

We set the location to 0in, 0in:

 

Export to Excel 11

If we export again, we have no extra column on the left:

Export to Excel 12

As for extra rows at the top, the news isn’t as good. Even if you hide the headings, as I did before, you’ll still get an extra line. If you’re always going to export to Excel, you can delete the heading. Otherwise, if you really can’t have that extra line, you’ll have to eliminate it manually.

3. Adding Information to the Tab Name in Your Export

The only problem with hiding headers is that your parameter information can go bye-bye.  So what to do instead? You can control data in the tab name.

Again, highlight your Tablix and find the PageName Property:

Export to Excel 13

In this case, I’m going to create a simple expression including the beginning and ending dates:

Export to Excel 14

When we export, we see the information in the tab:

Export to Excel 15

I hope these few examples help you create pretty exports to Excel.

No Comments

Post a Comment