Can't display text message in SSRS

Question

Post reply

Can't display text message in SSRS

TomGuide

SSC Eights!

Points: 964
More actions
October 9, 2013 at 4:19 am

#277889

Hi folks,
Hope all are well!
I have a quick question.I would be very greatful if someone helps me on this.
In SSRS I created a report and I am using stored Procedure to get the report data.
My Stored Proc looks like below
Create Proc sptest1
@param1 int,
@param2 int,
@param3 int
As
Begin
If dbo.paramvalidation(@param1,@param2,@param3)='Y'
BEGIN
Select ..
some SQL code
END
If dbo.paramvalidation(@param1,@param2,@param3)='N'
BEGIN
PRINT 'Invlid Message.Try again'
END
END
When I execute the same in SSMS its working fine and displaying the message as 'Invalid Message.Try again' when the function returns value 'N'.
When I use the same in SSRS its not displying the text instead its displaying the empty
data page with only column headers.
I have idea of using NorowsMessage feature in SSRS but am using that when the report displays no data.
Please help me out with this such that I can display some text in SSRS.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

SSRS will not display the text. It expects a dataset with the predefined columns.

Since the resultset doesn't contain any rows, it will show an empty report.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

joeroshan SSChampion Points: 10377 More actions · Answer 2

Koen is right. What you need to do is return something like all null row and

handle this in SSRS (displaying a label on condition)

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

TomGuide SSC Eights! Points: 964 More actions · Answer 3

Many thanks for your replies.

Could you please explain me with an example please??

TomGuide SSC Eights! Points: 964 More actions · Answer 4

Now I can return a NULL row if it goes to second IF condition.How to add a label for that in SSRS??

Many thanks for all your help.

TomGuide SSC Eights! Points: 964 More actions · Answer 5

Am able to display like below in SSRS report result page if it executes second IF statement(i,e when the function returns N)

Attached the screenshot.Please help me out to handle this in SSRS to display some text.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 6

Instead of using

PRINT 'Invlid Message.Try again'

Try

SELECT 'Invlid Message.Try again' AS Col1

,'' AS Col2

,'' AS Col3

,'' AS Col4

,'' AS Col5

,'' AS Col6

etc.

to return you message as data, then your report should know how to handle it.

If your columns on the report are not wide enough you can break up your message like this:

SELECT 'Invlid' AS Col1

,'Message' AS Col2

,'Try again' AS Col3

,'' AS Col4

,'' AS Col5

,'' AS Col6

TomGuide SSC Eights! Points: 964 More actions · Answer 7

Even I tested this one as well.

Than in SSRS it is displaying me a Null row with column headers.

So here I introduced a text box with the text I want to display and I have set the visibility property for that and when the textbox is visible I have hidden the table (which has the columns headers and Null row)

Many thanks for your replies.