What are the best strategies to debugging SSRS?

Question

I am looking for ways to track down where an SSRS error is occuring.

I have a report that is about 90 columns wide with multiple formulas. The problem that I am having is that on one of the formulas there is a divide by Zero error. I have implemented the answer from Robert Harvey in this question but I still get the error. I know that the answer works as I have tested it on a small report.

So the question is: How on earth do you identify where an error is occuring in SSRS when SSRS only reports that an error occured?

EDIT The error as displayed is

An error occured during local report processing
An error has occoured during report processing
Cannot read next data row for the dataset MainReport
Divide By Zero error encounted

Answer 1

EDIT
Rather than use the IIF statements and others, I'd reccomend doing the following... Add a custom function to your report, go to report properties and the code tab. Create the following. To specifically identify the field that is throwing the error, you can change this to return a string and maybe "#OOOOOOPS" so it sticks out on the report.

Public Function SafeDivision(ByVal top As Decimal, ByVal bottom As Decimal) As Decimal
   If bottom = 0 Then
      Return 0
      Else : Return top / bottom
   End If
End Function

After adding this function, go to the expression view for all of the fields that you have where division will occur. You can execute this newly created function by typing in:

=Code.SafeDivivision(CDbl(1.24), CDbl(0))

ORIGINAL
If you run the report within visual studio, does it tell you which specific textbox/label/field the computation failed in? That should help pinpoint where the issue is coming from, but you could also make sure that you never perform the division with 0 in the denominator by looking at the code below...

//myBottom would be the value of the denominator 
//myTop would be the value of the numerator 

= IIF(myBottom <> 0, myTop / myBottom, "")

Answer 2

Follow Up
The problem ended up being the SQL Query. As part of the SQL query, I had as In clause like the following.

Select ID, Name, price/kg as unitpice from products where ID In (@ProductIds)

SSRS uses exec sp_executesql and string.replace to inject the ID's into the IN clause.

The problem I had was I would run the query in SQL Studio Manager. with all the variables entered and it would work as expected. but under SSRS it would fail with the divide by Zero error, and there was no real clear indication where the error occured.

After I made a copy of the report and deleted items one by one until there was litrally nothing left on the report to render and it still produced the error, I turned my attention to the SQL Query.

It was there that error was occuring. I only found it after using SQL Profiler to find out what SSRS was actually running.

After that it was relativly easy fix of deleting the offending line and returning the seperate parts of the divide and run it through the function that RSolberg suggested.