Formatting Issue - Export to Excel

Question

Hi,

I have a SQL server 2008 Reporting services Report, where one of the column on this report is have a decimal values between 0.00 to 7.00 and it also has entries with value as "N/A". When this report is exported to Excel the Numeric values in this column are treated as "Text" and hence users are not able to perform any arithmetic  operations on this column.

Please let me know how I can fix this issue.

Thanks,

Rajiv

Answer 1

Hi Rajiv,

Click on the cell in the report and in the properties window(To open the proprieties window, hit F4 button), set the format property to be as shown below. 

#,##0.00

Hope this helps!

---

Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

Answer 2

Hi Raj !

This issue is due to Values "N/A" since this is a text and Excel cast the whole column as text to accommodate this. You need to replace "N/A" with some numeric value, may be below expression will work for you;

=CDbl(Fields!YourCoulmn.Value)

Or Alternatively you can use below expression;

=IIF(Fields!YourCoulmn.Value="N/A",Nothing,Fields!YourCoulmn)

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham Niaz

Answer 3

Hi Hasham,

Thank you for the reply.

I tried the second option, but when the value is N/A, report shows #Error.

Best Regards,

Rajiv

Answer 4

Hi Raj !

This issue is due to Values "N/A" since this is a text and Excel cast the whole column as text to accommodate this. You need to replace "N/A" with some numeric value, may be below expression will work for you;

=CDbl(Fields!YourCoulmn.Value)

Or Alternatively you can use below expression;

=IIF(Fields!YourCoulmn.Value="N/A",Nothing,Fields!YourCoulmn)

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham Niaz

Lets just change this to below;

=IIF(Fields!YourCoulmn.Value="N/A", 0, Fields!YourCoulmn)

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham Niaz

Answer 5

Hi Hasham,

I am not sure if I can use

"=IIF(Fields!YourCoulmn.Value="N/A", 0, Fields!YourCoulmn)" as I need to show "N/A" on the report.

So I tried conditional formatting as shown below:

"=IIF(Fields!YourCoulmn.Value="N/A", Fields!YourCoulmn.Value, CDbl(Fields!YourCoulmn.Value))"

But this expression returns "#Error" while for "N/A" values.

Thanks,

Rajiv

Answer 6

Rajiv,

Please take a look at this to avoid the error http://social.technet.microsoft.com/wiki/contents/articles/11840.ssrs-iif-function-evaluates-both-true-false.aspx

I guess your requirement is above that; like display N/A in visual report and convert it to some value when generated to excel?

AFAIK  SSRS  doesn't have that flexibility currently to treat both scenario separately. 

Answer 7

HI Rrohithr,

Thank you for the above link, it refreshed my memeroy about the "IIF function evaluates both True & False" issue. I had face this issue sometime back.

I modified my code as follows:

=iif(

IsNumeric(Fields!MyData.Value),

CDec(iif(IsNumeric(Fields!MyData.Value),Fields!MyData.Value,0.0)),

iif(IsNumeric(Fields!MyData.Value),0,Fields!MyData.Value)

)

And this is returning the data in the format that I want on the report. i.e. Both Text & Number are rendered in the same column and excel is identifying the Numbers as "Numbers".

Thanks You,

Rajiv

Answer 8

Oh, so you have changed  your option to display 0.0 instead of N/A in visual report.

Cheers.

Answer 9

Hi Rohith,

No, the above expression returns "N/A" when the value is not numeric.

Thanks,

Rajiv