Export to Excel bug in SQL Reporting Services

This is my first blog message. Let's me start this habit by sharing a tip I found while working with SQL Reporting Services 2000 SP1 today.

I deployed a report to a server. The report works fine when previewed in HTML and exported as PDF. But when I export to Excel, the following exception occurs:

Reporting Services Error
--------------------------------------------------------------------------------
Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. (rrRenderingError) Get Online Help
Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown.
Stack empty.

After doing a bit of investigation by removing one by one the controls that cause the exception, finally I found the problem.

Let's say I have two textboxes: textbox1 and textbox2

textbox1 contains this expression:
=IIF(ReportItems!textbox2.value IS NOTHING, 0, ReportItems!textbox2.value)

textbox2 is located inside a table footer and contains the following expression:
=SUM(Fields!Payment.Value)

It is possible that textbox2 contains NOTHING if there is no row in the table. As a safeguard, I check for NOTHING condition when I copy the value to textbox1. This sort of situation cause exception when the report is exported to Excel.

An easy fix to this bug is by adding trivial calculation. In textbox2, I change the expression to:
=SUM(Fields!Payment.Value)-0

Adding '-0' will force the textbox to display 0 if the result of SUM(Fields!Payment.Value) is NOTHING. The same result you can achieve by adding '+0'.

Then in textbox1, I remove the safeguard as I am sure that the textbox1 will contain 'some' value. The expression is simplified to:
=ReportItems!textbox2.Value

By doing this, I can export the report to Excel without throwing any exceptions.