Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to show NO DATA FOUND message when there is no data in SSRS


How to show NO DATA FOUND message when there is no data in SSRS

Author
Message
mcfarlandparkway
mcfarlandparkway
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 455
Hello,
My report is grouping with school wise;for each system I have different schools and each school has data;for some schools which don't have data I need to show as NO DATA AVAILABLE on the header

When I click on preview report I have 5 pages I mean 5 schools for that system.
one page has data and when I scroll the next page I don't have at that page I need to show message


The reports which don't have grouping is working fine I am able to see message if there is no DATA
I used a text box on header and used this expression

=IIF(Count(Fields!Region.value, "DataSet")=0,"No Data Found", nothing)

Any help ...
Tee Time
Tee Time
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

Group: General Forum Members
Points: 563 Visits: 465
From the Properties Pane, select the Tablix in question and look for "NoRowsMessage", under the No Rows subsection. Add the desired phrase there.
fstop
fstop
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 314
It looks like the "NoRowsMessage" property is only available on a Tablix. I have a main Stored Proc that supplies most of the data to my report, and I mostly use text boxes to display the data. Is there any way to pop up a message to the user (and terminate the report) if the the stored proc returns no data?
gbritton1
gbritton1
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 840
There is a CountRows function that you can use. If it returns 0 for your dataset, display the No Rows Found message.
fstop
fstop
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 314
Sorry...I'm an SSRS newbie...
Where would I access the CountRows function? I don't see it in my dataset properties.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16380 Visits: 13199
gbritton1 (5/15/2014)
There is a CountRows function that you can use. If it returns 0 for your dataset, display the No Rows Found message.


I've done something similar recently.

SSRS Nugget: displaying placeholders when no rows are returned



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

MCSA SQL Server 2012 - MCSE Business Intelligence
gbritton1
gbritton1
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 840
In a textbox, right-click and choose Expression. There you can use the CountRows (and many other) functions
fstop
fstop
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 314
I created a hidden text box on the report, and entered the following as an expression: =iif(CountRows("CaseData") = 0, "No Data","").

However, during preview the report still errors out probably because that new text box is hit after others that use the CaseData dataset. My goal here is for the REPORT to know the dataset returned no data, not a specific object/field on the report. I'm used to linear programming, and with SSRS I don't really understand in what order things are resolved on the report.

I appreciate your comments thus far. Any other thoughts would be appreciated.
gbritton1
gbritton1
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 840
What errors are you getting? It's actually normal for a dataset in SSRS to return no rows. The Table and Matrix widgets handle that automatically. You'll just see no lines on your table or matrix
fstop
fstop
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 314
When I click "View Report" and enter the case number into the parameter field, it returns an error "The 'CaseID' parameter is missing a value". CaseID is a field in the main report query, and a parameter used by other datasets in the report. The case number I enter returns no data from the main query, as expected. What I'm trying to handle the "no data" condition returned from the query, and pop-up an error message of my own for the user to see and respond to. I would think that SSRS has some internal way of dealing with this condition (other than through only a Tablix).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search