question

Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

SSRS Display Error Messages

I wonder if someone can advise me. I am writing a reporting services project that calls stored procedures. In the stored procedures I use RAISERROR if any parameters passed in are invalid. In the report the parameters have to be selected from table sourced data, therefore it should never become an issue with invalid data being passed in. However, IF invalid data does get in the error is pretty nasty looking. The advice I require is should I? 1. Create an error message that sits within the report and is displayed in a 'nice' way or 2. Don't worry about it as invalid data should not be able to be passed in. If you require further info please let me know, I'm keen to get this right as the reports are likely to go external and want to cover every eventuality. Many thanks
ssrserror-message
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave_Green avatar image
Dave_Green answered
I would think very carefully about how you trap it, but perhaps you could trap such an error and log it (to a log table), and present a prettier 'error encountered, consult XYZ' type message to the user? I would also review the constraints on the table-sourced data to check it can't be inconsistent within itself. Of course, it is still possible that the data may change between the report parameters being sourced, and the parameter being sent to the SP, meaning that the parameter may then be invalid. This, or another unexpected error encountered in the SP, may still mean that you get an 'ugly' error from the SP. This is why I suggest you log the detail and present a pretty error message to the user. If the report is a sub-report, perhaps consider putting a check in place to ensure that the child report isn't called directly (but must only be called from the parent report)? This may help to prevent some of the errors.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 from here
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
what if there is a schema change happened for table sourced data of parameters? That could generate an error as well :) How would that be handled? I guess one cannot take care of all the scenarios. In addition, since the parameters are tabled source, and assuming the associated source is not changed (for parameters), a generic procedure/s can be made, which will handle exceptions if generated from underlying procedure/s. Hope this helps. But can you please give some details, how are you implementing a custom error message? It is very interesting.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mrs_Fatherjack avatar image
Mrs_Fatherjack answered
What I've done is instead of using RAISERROR I've take Dave_Green's advice and added a new column in the select statement that remains empty when data is correctly pulled from the stored procedure, however if there is an error it will populate that column with an error message. In the report I have added an additional table that only refers to the new column and in the event of an error the main table is hidden and the new table is displayed showing the error message. As this is a child report, I've also added functionality to display a message if the report is not called from the parent report. This is the stored procedure I've used: CREATE PROC GetRuleResult @iRuleID varchar(10), @sModule varchar(100), @sRuleType AS varchar(100), @iError BIT AS /******************************************************************************************************** *** As this stored procedure is called from a report that can call from different groups we have to use LIKES in the *** where clause. For example, we may wish to return those patients who are affected by iRuleID 6 only, therefore *** we can break tha data down to that level, however if the user wishes for all the patients who are affected by *** by rules in the COPD Module then we will pass a % to the Rule and select the COPD module etc. ********************************************************************************************************/ DECLARE @sMessage AS VARCHAR(500) -- Used for error message /******************************************************************************************************** *** ERROR REPORTING *** As this script is going to be used primarily for reporting error messages have not been handled using RAISERROR but *** instead have been replaced with a message that can be passed back the report. *** As this stored procedure is used in a child repor the @iError parameter is used so that if it is called from a *** parent report this will be set to 1, however the default value is 0 and therefore if the report is run away from the *** parent report this will be set to 0 and display an error message as it is then likely to have been run in error. ********************************************************************************************************/ IF @iError = 1 -- Confirms it's been called from the parent report BEGIN -- Check that all the parameter values are valid, if not display an error message IF EXISTS (SELECT 1 FROM dbo.Rules AS R WHERE CAST(iRuleID AS VARCHAR(10)) = @iRuleID OR @iRuleID = '%') AND EXISTS (SELECT 1 FROM Modules WHERE sModuleName = @sModule OR @sModule = '%') AND EXISTS (SELECT 1 FROM dbo.defRuleType AS DRT WHERE @sRuleType LIKE '%' + sRuleTypeText + '%' OR @sRuleType = '%') BEGIN SELECT RS.sModuleName , RS.dRunDate , RS.sPatientID , RS.iEventID , RS.iRuleID , RS.RuleType , RS.RuleName, '' AS [Message] FROM uvw_RuleSummary RS WHERE iRuleID LIKE '%' + @iRuleID + '%' AND sModuleName LIKE '%' + @sModule + '%' AND RuleType LIKE '%' + @sRuleType + '%' END ELSE -- At least one of of the parameters is incorrect and therefore error handling is required begin /******************************************************************************************************** *** If the Parameters do not pass the tests above send 'NICE' error messages to the screen ********************************************************************************************************/ IF NOT EXISTS (SELECT 1 FROM dbo.Rules AS R WHERE CAST(iRuleID AS VARCHAR(10)) = @iRuleID) AND @iRuleID '%' BEGIN SET @sMessage = 'This Rule does not exist' END IF NOT EXISTS (SELECT 1 FROM Modules WHERE sModuleName = @sModule) and @sModule '%' BEGIN SET @sMessage = ISNULL(@sMessage + '; ','') + 'This Module does not exist' END IF NOT EXISTS (SELECT 1 FROM dbo.defRuleType AS DRT WHERE @sRuleType LIKE '%' + sRuleTypeText + '%') and @sRuleType '%' BEGIN SET @sMessage = ISNULL(@sMessage + '; ','') + 'This Rule Type does not exist' END -- This select statement needs to return dummy data and the error message SELECT '' AS sModuleName , '' AS dRunDate , '' AS sPatientID , '' AS iEventID , '' AS iRuleID , '' AS RuleType , '' AS RuleName, @sMessage AS [Message] END END ELSE -- The second part of the check for whether the report has been called from a parent report BEGIN SET @sMessage = 'This report can only be run as a child report and cannot be run independently' SELECT '' AS sModuleName , '' AS dRunDate , '' AS sPatientID , '' AS iEventID , '' AS iRuleID , '' AS RuleType , '' AS RuleName, @sMessage AS [Message] END GO
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.