patternMinor
Why is this SSRS Report rounding a BIGINT Value to the nearest Thousandth when exporting to Excel?
Viewed 0 times
thisssrswhythethousandthexcelexportingnearestvaluereport
Problem
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
For instance, this is an example of what I'm seeing.
The proper value:
The value shown in the Excel File:
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:
...the Placeholder Properties:
... and the Value of the field itself:
I can "fix" it by converting the field to a string by wrapping the field value in 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:
480758225903628660The value shown in the Excel File:
480758225903629000It'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:
...the Placeholder Properties:
... and the Value of the field itself:
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?Solution
Like MguerraTorres suggests, SSRS has probably got confused somewhere, and the value suggests it's getting cast to some kind of floating point datatype somewhere along the line.
Try checking the datatype SSRS is assigning by looking at the xml in the .rdl file raw text.
for a simple bigint column I'm seeing an SSRS field definition something like this:
You might then be able to edit the datatype directly in that text stanza and trick SSRS into sorting itself out
Try checking the datatype SSRS is assigning by looking at the xml in the .rdl file raw text.
for a simple bigint column I'm seeing an SSRS field definition something like this:
i
System.Int64
You might then be able to edit the datatype directly in that text stanza and trick SSRS into sorting itself out
Code Snippets
<Field Name="i">
<DataField>i</DataField>
<rd:TypeName>System.Int64</rd:TypeName>
</Field>Context
StackExchange Database Administrators Q#183844, answer score: 3
Revisions (0)
No revisions yet.