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?

Improve this question
1
  • I suspect it's not much to do with the database datatype, but with the SSRS datatype and its precision. I'd go ahead and try the conversion to string and do it that way if that's working fine for you. That or you can try switching it to "Custom" in the placeholder properties and seeing if that works.
    – SQLDevDBA
    Commented Aug 18, 2017 at 14:51

3 Answers 3

Reset to default
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."

Improve this answer
3
  • 1
    It wasn't you. You probably read the first revision of the question properly, but I updated the question after I figured out it was related to the excel export (as my users are only dealing with the excel output and not the rdl directly). Sorry about that, and I'll give this a shot and let you know my findings.
    – John Eisbrener
    Commented Aug 18, 2017 at 17:41
  • 1
    Seems like the conversion is the only approach I've found that works, so I'm marking this as the answer. I'll probably backout this change and see if this still occurs in 2016 when we migrate these reports over later this year.
    – John Eisbrener
    Commented Aug 18, 2017 at 17:49
  • Glad something worked. Another approach I'd take (and have taken in this scenario) is to export as a CSV (Pipe or Comma delimited). This allows you to export the text objects and anyone can open with Excel and still have correct values.
    – SQLDevDBA
    Commented Aug 18, 2017 at 18:59
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

Improve this answer
3
  • 1
    I'm upvoting you because it's another factor I've not checked, though sadly the XML looks to be correct as well so this isn't the fix. Thanks though!
    – John Eisbrener
    Commented Aug 18, 2017 at 15:18
  • 1
    Doh! Hmm.. well it's a BIGINT at the server end, 8 byte int at the SSRS end.. so what's in between? - what connection type are you using? Is that messing it up somehow?
    – Jerb
    Commented Aug 18, 2017 at 15:24
  • It's the standard shared SQL Server data source connection. Nothing fancy about it.
    – John Eisbrener
    Commented Aug 18, 2017 at 15:56
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.