Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can't display text message in SSRS Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 4:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:12 AM
Points: 30, Visits: 76
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.

Post #1503005
Posted Wednesday, October 9, 2013 5:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1503018
Posted Wednesday, October 9, 2013 5:20 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:10 AM
Points: 615, Visits: 1,259
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
Custom cleanup script for backups
Post #1503023
Posted Wednesday, October 9, 2013 5:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:12 AM
Points: 30, Visits: 76
Many thanks for your replies.
Could you please explain me with an example please??
Post #1503038
Posted Wednesday, October 9, 2013 6:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:12 AM
Points: 30, Visits: 76
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.
Post #1503051
Posted Wednesday, October 9, 2013 6:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:12 AM
Points: 30, Visits: 76
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.







  Post Attachments 
untitled.doc (6 views, 27.00 KB)
Post #1503054
Posted Wednesday, October 9, 2013 7:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 2,818, Visits: 2,552
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
Post #1503088
Posted Wednesday, October 9, 2013 7:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:12 AM
Points: 30, Visits: 76
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.
Post #1503105
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse