Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSRS Exporting Report to Excel – Keep Formatting on Export Round 2

Earlier I blogged about exporting SSRS (SQL Server Reporting Services) Reports to Excel and keeping formatting. Well, my previous solution works, but not always (doh!)

I found in a new report that I created that the formatting was taking on all columns. The solution? Wrap those expressions in CDec() function and it works. It was for some currency and percentage formatted columns.

Nothing can ever be easy!

Technorati tags: , , , , , ,

By Steve Novoselac

Director of Digital Technology @TrekBikes, Father, Musician, Cyclist, Homebrewer

9 replies on “SSRS Exporting Report to Excel – Keep Formatting on Export Round 2”


I am generating report to percentage of one decimal. If I use the format(object,”P1″) to get the pecentage for single digit, exported excel gives error sign.

Any solution for that.



When I use an IIF expression like :

“=IIF((sum(Fields!Something.Value)+ sum(Fields!SomethingElse.Value)) = 0 , “-“,(sum(Fields!Something.Value)+sum(Fields!SomethingElse.Value)) )”

The value is constantly converted to Text. The following options don’t work:
– converting the last part to the CInt()
– Giving the format of the textbox C0 or N0

When i replace the “-” with “0” it works fine but i really need the “-” to be placed in the textbox.

So if you have any ideas please let me know

Thanks in advance


Yeah true but in the IIF statement i converted the other part of the IIF (sum(Fields!Something.Value)+sum(Fields!SomethingElse.Value)) to a CInt.

So it apparently gets confused by the IIF.


well no. you are saying IF myresult = 0, then I want a “-” else I want myvalue

so you will end up with something like




Excel, sees the – and even though the #’s are int’s, the – will never be a numeric value, so it casts the whole thing as text. One thing that might work but would be a total hack is to make the first 10-15 rows just a 0 or something. Excel looks at the first 8 rows or so to figure out the type , see (


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.