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

Post author By Steve Novoselac
Post date June 29, 2007
9 Comments on 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: SSRS, SQL Server 2005, SQL Server 2005 Reporting Services, Excel, Reporting Services, Number Formatting, CDec

Tags Excel, SSRS 2005

By Steve Novoselac

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

View Archive

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

Hey,

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.

LikeLike

Hey,

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

LikeLike

Not sure there, it is coming back text because “-” is …well, text.

LikeLike

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.

LikeLike

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

so you will end up with something like

–
3
–
–
1
0
–
1
2
3
–
–

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 (http://blog.stevienova.com/2007/10/10/importing-data-from-excel-gotcha-the-top-8-row-rule/)

LikeLike