Exporting a Report form SSRS to Excel adds decimal only to 0.00 values?

Question

I have a simple Reporting Services report with several DECIMAL(19,4) values coming from a Stored Procedure. I format these columns with "N2" because I always want exactly 2 decimals. The format works correctly in the report as it always did. As soon as I export it to Excel though, in one of the columns (the last to the right, does it matter?) the number of decimals increases from 2 to 15 but ONLY if the value in the cell is 0 (formatted originally as 0.00, of course). If the cell is empty, it becomes empty and it's OK. If it is different from 0, it is shown properly. Only the value 0 becomes 0.000000000000000 I tried also the format "0.00" but the problems persists. When I open the Excel, it says It had a problem opening it. The cells with this strange all have the green triangle signaling an error. It says "The number in this cell is formatted as text or preceded by an apostrophe". Whatever I am doint to these values I think I am doing also to all other values of the same column so I can't understand why the 0 is treated differently. If I force the Stored Procedure to return a 1 when it should return a 0, it works properly. [EDIT] If I force a cast to DECIMAL(19,4) in the St.Proc. for this value (that originates from an arithmetic calculation), it DOES WORK!! So I have solved my problem but still I would like to know WHY and to remove a horrible useles CAST By the way, the code I am casting to decimal is basically: CASE WHEN B = 0 THEN NULL ELSE 100.0*A/B END with A and B DECIMAL (19,4). The problem arises only when A = 0.

Answer 1

Use ROUND() in SQL. Truncate the floating-point residue directly in your query so SQL Server strips out the invisible decimals before passing the dataset to SSRS:

SELECT ROUND(100.0 * A / NULLIF(B, 0), 4) AS Calculation Result