Search

Advertisements

SSRS - No data message for report items

This article is about handling display of report items like chart , table or matrix when there is no data
returned from the dataset .

For example :

Consider below query is my dataset :

DECLARE @SalesbyMonth TABLE (Months VARCHAR(20),Sales INT,MonthNo INT)
INSERT @SalesbyMonth SELECT 'January',1000,1
INSERT @SalesbyMonth SELECT 'February',100,2
INSERT @SalesbyMonth SELECT 'March',100,3
INSERT @SalesbyMonth SELECT 'April',200,4
INSERT @SalesbyMonth SELECT 'May',500,5
INSERT @SalesbyMonth SELECT 'June',800,6
INSERT @SalesbyMonth SELECT 'July',500,7
INSERT @SalesbyMonth SELECT 'August',100,8
INSERT @SalesbyMonth SELECT 'September',900,9
INSERT @SalesbyMonth SELECT 'October',600,110
INSERT @SalesbyMonth SELECT 'November',100,11
INSERT @SalesbyMonth SELECT 'December',100,12
SELECT * FROM @SalesbyMonth
WHERE MonthNo = @MonthNo


I have table & column chart on the report body , when I preview the report and enter MonthNo =12
and click on View report




Again if I enter MonthNo =13 and click on View report



So to handle display of report items like chart , table or matrix when there is no data
returned from the dataset:

Go back to Design pane , click on table , press F4 - > Tablix properties - > NoRowsMessage - > write this expression : ="No data for Month :"+Parameters!MonthNo.Value 





Click on chart , press F4 -> Properties - > NoDataMessage -> (Caption) ->
write this expression : ="No data for Month :"+Parameters!MonthNo.Value



Now again when I preview the report and enter MonthNo =13  and click on View report 








Reference links :
http://technet.microsoft.com/en-us/library/dd220407.aspx
http://technet.microsoft.com/en-in/library/cc645968(v=sql.100).aspx

No comments:

Advertisements