Usually, when I have experienced this sort of issue after an Excel file exported from SSRS, I find there is something in the data that Excel has issue with. A hidden character, perhaps. The export works, opening the file is when the problem starts.
To deal with this situation I created a user defined function (UDF) in SQL Server to remove any characters that I know Excel will choke on. Below is an example of one I use.
CREATE FUNCTION [dbo].[udf_CleanData]
(
@data text
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @cdata varchar(max) = @data
SELECT @cdata = REPLACE(@cdata, char(20), '') -- double quote
SELECT @cdata = REPLACE(@cdata, char(21), '') -- bullet
SELECT @cdata = REPLACE(@cdata, char(13), '') -- carriage return
SELECT @cdata = REPLACE(@cdata, char(10), '') -- line feed
SELECT @cdata = REPLACE(@cdata, char(18), '') -- single quote right
SELECT @cdata = REPLACE(@cdata, char(17), '') -- single quote left
SELECT @cdata = REPLACE(@cdata, char(22), '') -- dash
RETURN @cdata
END
You can adjust this any way you want to meet your need. This works for what I need, and that is removing all the strange characters in a text field that the dev team thought would be good to use for formatting on the front end application.
Usage:
SELECT dbo.udf_CleanData(Field1) AS CleanField1 FROM Table1
If the hyperlink you are reporting has anything unusual in it, this might remove that. If you are building the hyperlink from various pieces of data, some or all of the fields may need to be scrubbed. This is something where you will have to go through a bit of trial and error.
Other times when I have had this issue, there is an expression in the RDL that ends up calculating to either NaN, or Infinity. The Excel file after export seems to have issue with this except when exporting to an older version of Excel, like 2003.
The best way to handle this situation is adjust your expressions to check for the possibility that the final value could be out of whack, and default the value to something else, or round the value to something reasonable. This is similar to checking that a denominator is not zero before doing division.
=IIf(Fields!Total.Value > 0, Fields!Count.Value/Fields!Total.Value, 0)
There are plenty of questions about this type of thing with good solutions here on SO, so I won't list a bunch of expressions here related to data checking.
The other way to handle this is to always export to an earlier version of Excel, but that may not be any easy option, or an option at all. Usually, I only see that as an option on a report subscription, but not in the development tools.
Good luck to you!
