April 25, 2014 at 8:36 am
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 ...
May 1, 2014 at 6:18 am
From the Properties Pane, select the Tablix in question and look for "NoRowsMessage", under the No Rows subsection. Add the desired phrase there.
May 15, 2014 at 12:16 pm
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?
May 15, 2014 at 12:21 pm
There is a CountRows function that you can use. If it returns 0 for your dataset, display the No Rows Found message.
May 15, 2014 at 12:33 pm
Sorry...I'm an SSRS newbie...
Where would I access the CountRows function? I don't see it in my dataset properties.
May 15, 2014 at 12:33 pm
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[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 15, 2014 at 12:42 pm
In a textbox, right-click and choose Expression. There you can use the CountRows (and many other) functions
May 15, 2014 at 12:58 pm
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.
May 15, 2014 at 1:07 pm
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
May 15, 2014 at 2:07 pm
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).
May 21, 2014 at 5:40 pm
Just want to mention that I have an old fashioned way of dealing with no data. Be mindful that your report viewers may want to see items that 0 counts, even if they don't know if yet.
I created an example with the adventure works 2008 R2 database :
/* Method 1 */
CREATE TABLE #myTable(productID smallint, name varchar(50), numSold int )
GO
INSERT INTO #myTable
SELECTs.ProductID , p.[Name], count(*)
fromSales.SalesOrderDetail as s Inner Join Production.Product as p
ON s.productID= p.productID
WHEREyear(s.modifiedDATE)=2005 and month(s.modifiedDate)=7 and productSubCAtegoryID<4 /* bikes*/
Group bys.productID, p.[Name]
SELECT * from #myTable
/* Method 2 */
CREATE TABLE #myTable_comprehensive(productID smallint, name varchar(50), numSold int default 0 )
GO
INSERT into #myTable_comprehensive(productID, name)
SELECT productID, name from production.product where productSubCAtegoryID<4 /* bikes again */
--select top 8 * from #mytable_comprehensive
UPDATEt
SETt.numSold = v.sold
FROM(SELECTs.ProductID , p.[Name], count(*) as sold
fromSales.SalesOrderDetail as s Inner Join Production.Product as p
ON s.productID= p.productID
WHEREyear(s.modifiedDATE)=2005 and month(s.modifiedDate)=7 and productSubCAtegoryID<4 /* bikes*/
Group bys.productID, p.[Name]
) as v
INNER JOIN #myTable_comprehensive as t
ON v.productID=t.productID
SELECT * from #myTable_comprehensive
The first query won't tell you which bikes had no sales in this month. By using the second query in my dataset, I also don't deal with the problem of no data being returned, potentially.
Just thought I'd throw this out there to the OP.
----------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply