SSRS NoRows – Display a custom message when there is no data

In this short post, I’ll discuss NoRowsMessage property. When there is no data for a data region, the message you specify for the NoRowsMessage property will displayed to the user instead of a blank report.

I created a report with a tablix and configured the NOROWS property from the tablix properties window.


As with any other properties, you can give a static text or use expressions. The text I input here will be displayed when there is no data.



Report Preview:


For the record, NO, I wouldn’t use the message seen in the above image for my users and you shouldn’t too. 🙂



Data Labels, Axis Interval, Rotate Label Text: SSRS Nugget

Charts present summary data in a visually appealing format. Following is a chart for sales over month:



I frequently use a few options along with charts to get most out of them.

Show data labels:

It’ll be easier for the users to quickly read, if the chart shows the actual numbers along with bars. Steps: From the chart data pane, click data value, and choose Show Data Labels.


Display all categories on horizontal axis:

Notice in the above chart, all the categories (Months in our example) are not displayed. By default, auto fit property determines the interval of horizontal axis labels. Changing the interval to 1 will force the axis to display all categories. Steps are depicted in the below screen prints.



Rotate Labels:

Sometimes you may notice the labels are rotated automatically to better fit them, other times labels overlap with each other. Labels can be forced to rotate by disabling auto-fit and changing the label rotation angle to 90 degrees.





In this post, I’ve looked at simple properties that can extend chart’s functionality: Showing data labels, configuring axis interval, and rotating axis labels.


Create Drillthrough Reports

Use Actions!

Your user wants a summary report, containing sales for calendar year, for example. When the user clicks on the report, another detail report should be visible, containing sales for each month of the year, suppose. These type of reports are called drillthrough reports. How a drillthrough report is different from a drilldown report is for some other time.

I use reporting services 2008R2 and AdventureWorksDW2008R2 as the data source for this example.

Step1: Create detail report

I’ve created a report called Sales Detail. It consists of Year, Month, and Sales Amount in a table.

Step2: Add parameter to the detail report

The detail report must only show data for the year on which the user clicks on the summary report. For example, if user clicks on 2006, detail reports should show only data for 2005.

So add a parameter for Calendar Year in the detail report.
drillthrough report

Step3: Create summary report

I’ve created a summary report called Sales Summary. Below is a preview of the report. Notice the underlines for Year. They represent links and encourage users to click on them.
drillthrough report

Step4: Add Actions in the summary report

Add action for the Calendar Year textbox. Property configuration will look like the image below.
drillthrough report


Complete solution can be downloaded from my SkyDrive account.

Please use the email link on the right, if you have any comments or questions.

I’ll leave you with a link to books online for more information on drill through reports.