SSRS Exporting Report to Excel - Keep Formatting on Export Round 2
Jun.29, 2007 in
Geeky/Programming
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
Simlar Posts
August 16th, 2007 at 8:41 am
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.
April 22nd, 2008 at 4:02 am
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
April 22nd, 2008 at 6:34 am
Not sure there, it is coming back text because “-” is …well, text.
April 22nd, 2008 at 6:37 am
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.
April 22nd, 2008 at 6:47 am
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/)