Problems when exporting to Excel

When you make a report in Reporting Services, you often want to export it to Excel. Reporting Services quite often messes up the layout. For example, it often inserts columns that do not belong there, and you often get rounding problems.

Problem 1: columns are inserted
After you have exported a report from your Report Manager to Excel, you often want to do calculations with it. The problem is that Reporting Services often inserts columns, which makes it hard to do calculations.

The cause is the use of Report Items, for example in the header of your report. You may have a report title in your header, for example.

You will see that Excel splits the columns exactly where the Report Item ends. So in the example above, Excel inserts an extra column where the arrow is.

The fix is to make sure that the end of the Report Items falls exactly on a column split of, for example, a table, as in the picture below.

Problem 2: I get more digits after the decimal point than I specified

In the “format” field of a cell you can set what the output of a cell should look like. You use ‘c’ for output with a euro sign (currency), D2 for 2 decimals and P2 for a percentage with 2 digits. Say you enter P2 for a cell and the value of this cell is 0.4222. Then the output is “42.22%”, because we said we wanted 2 digits after the decimal point. When we then export the result to Excel, we often see that there are suddenly 3 digits after the decimal point! This is because Excel does not look at the P2 you set, but at the result of the dataset.

The fix is to Round in the cell or in the dataset. If we want the number with 2 digits after the decimal point, we have to Round to 4 digits after the decimal point (otherwise you get 0.42 and it rounds to 42%). You Round with the following Expression: =Round(Fiels!Omzet.Value,4)
Of course you can also do this in your dataset. Just be careful not to get rounding differences when you then use this number in calculations.