Can't display text message in SSRS

  • 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.

  • 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

  • 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]

  • Many thanks for your replies.

    Could you please explain me with an example please??

  • 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.

  • 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.

  • 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

  • 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.

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

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