4

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?

John Eisbrener
  • 9,547
  • 6
  • 31
  • 65

3 Answers3

3

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:

<Field Name="i">
  <DataField>i</DataField>
  <rd:TypeName>System.Int64</rd:TypeName>
</Field>

You might then be able to edit the datatype directly in that text stanza and trick SSRS into sorting itself out

Jerb
  • 606
  • 3
  • 9
3

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."

SQLDevDBA
  • 2,244
  • 10
  • 12
0

https://www.tutorialgateway.org/format-numbers-in-ssrs/

Format your text field by Right clicking the text box and select 'text box properties' from the dropdown, then click number, then custom, and put 0 in the custom format box. I figured this out after reading another forum on how to convert fields that are downloaded in excel as scientific notation, into their original format (formatting into text once they are in scientific notation does not work) but you can do the same thing, right click the field, format as number-custom, put 0 in the formatting box. ssrs text box

user254444
  • 1