Standards or Best Practices for Error Handling in SSRS

Question

I have encountered a recurring issue with SSRS where "bad" reports go out to clients. The issue is that we have reports with a number of subreports, and one or more subreports will fail to process. When the subreport fails to process, the report sends with an error message in place of the sub report and no actual data.

My question is, is there a way to prevent a report from sending if a subreport fails?

Ideally I would like the outer report to just return an error. Is there a way to have error states in an SSRS report escalate to an outer scope? Is there a coding standard we can enforce that could prevent this issue?

We are currently using both SSRS 2008r2 and SSRS 2016.

score 4 · Accepted Answer · edited Apr 13 '17 at 12:42

As billinkc noted in his comment there is no way to propagate the subreport errors to the main reports.

As evidenced in this MSDN post you could try to use data driven subscriptions to drive the subscription, but that could get messy if there is any complexity involved real quick.

If you look at the SQL Agent jobs for a server using SSRS subscriptions you will see a bunch of SQL Agent jobs with a GUID as name, you could modify those jobs to include an extra step running some data validation steps before actually running the report failing the job before the report is sent out (risking your job definition being overwritten by SSRS) or calling the job from another job after validating the data logic.

You could identify the GUID associated with the report using this query:

SELECT
            b.name AS JobName
            , e.name
            , e.path
            , d.description
            , a.SubscriptionID
            , laststatus
            , eventtype
            , LastRunTime
            , date_created
            , date_modified
    FROM ReportServer.dbo.ReportSchedule a JOIN msdb.dbo.sysjobs b
          ON a.ScheduleID = b.name
            JOIN ReportServer.dbo.ReportSchedule c
            ON b.name = c.ScheduleID
            JOIN ReportServer.dbo.Subscriptions d
            ON c.SubscriptionID = d.SubscriptionID
            JOIN ReportServer.dbo.Catalog e
            ON d.report_oid = e.itemid
    WHERE e.name = 'Sales_Report'

(taken from here)

TL/DR: there is no clean solution but you could get away with some hacks, you would need to implement logic in a data driven subscription or create an extra job/modify the generated job

PS: I didn't test any of these approaches

Standards or Best Practices for Error Handling in SSRS

1 Answers1