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!

Advertisements

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

  1. 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.

    Like

  2. 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

    Like

  3. 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.

    Like

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

    Like

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s