January 18, 2012 at 7:57 am
I have an SSRS Report that uses a stored proc.
Inside my stored proc,
I will be checking for input parameters passed by user and then I may want to display an message back to the user
saying "Sorry no records found." or " Error: User entered an invalid date."
How do I send a message back so that it gets displayed back to the user.
What I don't want is to have an alert box with a "OK" and "CANCEL" button which is annoying to the user.
January 18, 2012 at 8:21 am
If you just wanted to return a no rows found there is a property in a report called "NoRowsMessage" that is designed for this purpose, but it will not relay errors or messages from an SP.
If you want to feed back a message from the SP, I would code the SP to return your message in a single field. Then in the report have the report look for a specific return keyword (Error or something like that) so that you can do some conditional formatting on the report such as remove unwanted columns, etc. and then display the message.
January 18, 2012 at 8:51 am
I have noticed if something goes wrong inside the stored proc while running, SSRS will display the
ugly error information. Well, if that can happen i am sure there is a way for us to throw personalized messages back to the user ? What do you think ?
January 18, 2012 at 8:58 am
Does the stored proc include Try ... Catch and RaiseError error trapping?
If not, you could try implementing that and see if it does what you wish.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 18, 2012 at 9:12 am
Wonderful idea let me try that
I need to get familiar with the raiseerror stmt. Any help with syntax
January 18, 2012 at 9:24 am
raiserror('Your master data table has been truncated. Please have a quiet chat with your DBA', 1,1) with nowait
Read BOL to make sure you understand the concepts behind the arguments and decide whether NOWAIT is right for you.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 18, 2012 at 9:25 am
It worked thanks
The RAISEERROR has to go inside the catch block. This is the error message I get(below).
The only thing I don't like is can this message be customized ?
I only included the following stmt in my code ?
RAISERROR ('Error: Reporting Server is down at the moment. Please try later', -- Message text.
16, -- Severity.
1 -- State.
);
So my question is: Is there a way to customize the error message ? ( Take away that part that says "Query execution failed for data set 'Report_Data'." ) ?????
An error has occurred during report processing.
Query execution failed for data set 'Report_Data'.
Error: Reporting Server is down at the moment. Please try later
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply