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 12»»

How to show NO DATA FOUND message when there is no data in SSRS Expand / Collapse
Author
Message
Posted Friday, April 25, 2014 8:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 1:06 PM
Points: 55, Visits: 109
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 ...
Post #1565122
Posted Thursday, May 1, 2014 6:18 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:50 PM
Points: 531, Visits: 448
From the Properties Pane, select the Tablix in question and look for "NoRowsMessage", under the No Rows subsection. Add the desired phrase there.
Post #1566685
Posted Thursday, May 15, 2014 12:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:51 PM
Points: 45, Visits: 236
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?
Post #1571470
Posted Thursday, May 15, 2014 12:21 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
There is a CountRows function that you can use. If it returns 0 for your dataset, display the No Rows Found message.
Post #1571473
Posted Thursday, May 15, 2014 12:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:51 PM
Points: 45, Visits: 236
Sorry...I'm an SSRS newbie...
Where would I access the CountRows function? I don't see it in my dataset properties.
Post #1571478
Posted Thursday, May 15, 2014 12:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:46 AM
Points: 12,959, Visits: 10,730
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1571479
Posted Thursday, May 15, 2014 12:42 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
In a textbox, right-click and choose Expression. There you can use the CountRows (and many other) functions
Post #1571486
Posted Thursday, May 15, 2014 12:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:51 PM
Points: 45, Visits: 236
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.
Post #1571494
Posted Thursday, May 15, 2014 1:07 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
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
Post #1571498
Posted Thursday, May 15, 2014 2:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:51 PM
Points: 45, Visits: 236
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).
Post #1571522
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse