SSRS Report – Why Rounding BIGINT Value to Nearest Thousandth in Excel Export?

excelssrsssrs-2012

I have a SQL Server Reporting Services (SSRS) 2012 report that is populated via stored procedure (SP). The SP returns various fields, one of which is a BIGINT datatype. When I execute the SSRS report directly the value is properly displayed, but when I export the output to Excel, it gets rounded to the nearest thousandth.

For instance, this is an example of what I'm seeing.

The proper value: 480758225903628660

The value shown in the Excel File: 480758225903629000

It's rounding 8660 to 9000. Truly frustrating, and Microsoft's documentation mentions nothing about this behavior. I've also dug everywhere on the report that I can think of that may affect this, such as the Text Box Properties:

enter image description here

…the Placeholder Properties:

enter image description here

… and the Value of the field itself:

enter image description here

I can "fix" it by converting the field to a string by wrapping the field value in a CStr() or .ToString() function, but that also treats the output as a string and not a number. Is there a way to export this to Excel so that the rounding doesn't occur but still keeps this value numeric?

Best Answer

OHHH Man I completely misread your initial question. This is 100% an excel issue. You NEED to convert that value to text/string before you export it to Excel.

You will get the same exact issue if you try this experiment:

  • Query the procedure via SSMS and display the results in a grid
  • Copy the results and paste them directly into Excel.

Noting this confirms it has nothing to do with SSRS, but with Excel.

However if you:

  • Query the procedure via SSMS and display the results in a grid
  • *Change the destination column in Excel to Format type: TEXT
  • Copy the results and paste them directly into Excel.

You will note that the value comes through correctly and not rounded. *It's very important that you change the format of the column BEFORE you paste the values so that you can see it in action. It's basically converting to scientific notation and then displaying that value, which is always rounded.

*Thanks Jerb for making me re-read the original question. I had skimmed over and not noticed "but when I export the output to Excel, it gets rounded to the nearest thousandth."

Related Question