Here’s another installment in my series of posts on SSRS consultant tips for super users and advanced beginners. (See my post Put Parameters in Your Query, Not Your Filter for the first installment.)
Part of what I enjoy most about working as a SSRS consultant, and performing SQL Server consulting, is the variety. Some months, I spend a lot of time writing T-SQL. Others, I’m deep into Excel pivot tables. And then others I’m writing SSRS reports.
Truth be told, if I could spend all my time doing solution architecture and T-SQL coding, I’d be happy. But that’s not my current life.
Every once in a while, when I go back to SSRS, I find I’ve forgotten a few helpful but not crucial things. So, I empathize with folks who don’t use SSRS full time and need a moment to get their heads back into it.
In that spirit, I’m going to use this post to point out one of those “helpful but not crucial” things: the importance of rounding your data instead of just changing the format.
Rounding Numbers Before Exporting to Excel
To illustrate, let’s use a simply example from the AdventureWorks Database.
Writing a report, we see that the total line amount is set to a numeric field with a total length of 38 with 6 decimal digits.
If we create a simple report (as below), we see all six decimal digits show up:
Let’s start by changing the formatting.
We change the properties for my Total Sales column to show only two decimals:
And when we run the report again, we think we’ve fixed the problem:
But have we?
Exporting a Formatted Number From SSRS to Excel
Let’s see what happens when we export from SSRS to Excel. (I work mostly with back office types, so I fully expect almost all of my reports to wind up in Excel at some point.)
In this case, the numbers look fine—but there’s a lot more detail behind the scenes?
And any finance person who looks at that number is going to ask questions.
(In this case, everything is formatted correctly in Excel. But once I did this in Dynamics NAV (which defaults to 18 decimal precision), and the field showed up with all 18 digits in the cell itself. Not a good thing.)
So what should you do instead?
Make sure that what’s in the cell matches the formatting.
You can do this in many ways, but the easiest is to create an expression that rounds the number.
In this case, let’s start by going to the reports dataset. On the fields page of the dataset properties, let’s create a new calculated field called TotalSalesRounded. The expression is simple:
=Round(Fields!TotalSales.Value,2)
Where “2” is the accuracy I want:
Then we replace TotalSales with TotalSalesRounded in Tablix:
The report looks the same in SSRS. But when we export it to Excel, the extra decimals are gone:
Rounding Your Numbers to Make Sure Everything Adds Up
I want to make one more point about how important rounding in the formula can be.
Let’s modify the report to show both the “rounded” and “just formatted” columns.
When we run the report, the columns look correct all the way down:
But if we go to the grand total row, we see something strange:
As you can see, the grand totals are different.
Why? Because the “just formatted” column adds the actual values and then formats the result.
In contrast, the rounded column works with the rounded value from the start.
As you can imagine, this “small” difference can become a big one, especially when rounding to whole dollars, thousands or millions.