9/09/2014

SSRS Export to Excel Bug using Dynamics NAV (38,20) Precision

Here's a bug we found over the weekend.  When you have a SQL Server Reporting Services SSRS report embedded within a web part in SharePoint, or even in the Visual Studio 2012 designer for that matter, when you export the report to Excel, it throws the following error when opening the report in Excel:


So if you click on the Yes button, you get:


Not very descriptive error message.  So you click on the hyperlink to view the XML file:


Still not very descriptive.  However, there is a clue to solving this:


Take a look at the Green thing a ma bob in the top left hand corner.  And notice the number of decimal places.

 
Hmmm.

Well, if you click the Green button and convert to number, the green goes away, you save the report and reopen it works fine.

But that doesn't solve the root problem.

So going back to Visual Studio SSRS report, you discover that it's treating 0.00 as 0.0000000000000000000.  And why is that?  Because the underlying database is Dynamics NAV.  And it stores number with (38,20) precision.



So how do we fix the problem.  Simple.

You apply formatting to each of the fields which contain a number.

For Numbers, I applied this formula to display 2 decimals, you can vary accordingly:

FormatNumber(Fields!Total_Cost_per_Drop.Value,2)

For Currency this defaults to 2 decimals and a $ sign,

=Format(Fields!Price.Value, "C")

And for Percent, in this case we wanted no decimals so we applied P0, you could do P1 or P2 or P3 also... and it displays the % sign...


=Format(Fields!Price.Value, "P0")

Applying the formulas to each and every field, your downloaded Excel file should open clean with no errors.

I hope this helps...

Babalon