SQL Clone
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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1945 Visits: 765
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1059 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
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 316
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1413 Visits: 879
There is a CountRows function that you can use. If it returns 0 for your dataset, display the No Rows Found message.
fstop
fstop
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 316
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
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62496 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
gbritton1
gbritton1
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1413 Visits: 879
In a textbox, right-click and choose Expression. There you can use the CountRows (and many other) functions
fstop
fstop
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 316
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1413 Visits: 879
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
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 316
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