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.

image

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.

image

 

Report Preview:

image

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

@SamuelVanga.

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:


image

Before

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.

image

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.

image

image

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.

image


image

After

Summary:

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.

~Sam.

Why doesn’t my gauge show correct values?

Here is an issue I encountered recently while using gauge in reporting services.
There are three gauges in the report one for each product; bikes, computers, and hardware. The value being showed is YTD sales and all gauges show a value of 100 for YTD Sales as seen below:

However the actual YTD sales are 100, 150 and 200 for bikes, computers, and hardware respectively. See the sample data below:

Okay, why is this happening? I am glad you asked!

Minimum and Maximum values for chart:

Starting and ending values (0,100) on the gauge scale are controlled by minimum and maximum values that can be configured on the general page of scale properties. Right click on scale and select scale properties.

Since maximum value is set to 100, scale doesn’t show beyond 100. And when the actual value (YTD sales in this case) exceeds the maximum value the pointer will be defaulted to point to the maximum value.

To resolve this problem, change the maximum value to the following expression.

=sum(Fields!YTDSales.Value)

~Sam.

Other Related Post: SSRS Error – An item with the same key

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

Downloads:

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.

~Sam.

Create Sparklines in SSRS

When creating a SSRS report, you want to add lines that display trends. Sparklines that were introduced in SQL Server 2008 R2 will allow you to do this easily. But wait there is more. You want to show trends for more than one data point. For example actual sales and target amount. And you want to conditionally format the data point. Show colors green or red depending upon if sales exceeds target.

Below is how the Sparkline with multiple data points and conditional formatting applied to them will look like. Columns represent sales by month and line represents sales quota by month. Column is in green when sales exceeds the quota for a given month and red when sales fall below the quota.

image

Sparklines are simplified versions of charts that can be used to display trends. Different types of Sparkline charts include line, area, bar, etc.

Sean Boon (Blog) described how to build Sparkline reports in an earlier post. If you haven’t read that post yet I highly recommend you do so. I am going to use the same example that Sean used in his post but show a different functionality.

Create dataset:

After opening report server project and creating a data source, create a data set. Right click on shared datasets and choose add new dataset. Following is the query I am using for this example which runs against AdventureWorksDW2008R2 sample database.

SELECT T.CalendarYear,
T.CalendarQuarter,
T.MonthNumberOfYear,
SUM(S.ExtendedAmount)AS Sales ,
COALESCE(SUM(Q.SalesAmountQuota )/ COUNT(SalesAmountQuota),
SUM(Q.SalesAmountQuota )/ COUNT(SalesAmountQuota))/3 AS Quota ,
E.FirstName + ' ' + E.LastName AS Employee
FROM
FactResellerSales AS  S
LEFT OUTER JOIN dbo.dimDate T ON S.orderdatekey = DateKey
JOIN dbo.DimEmployee E
ON S.EmployeeKey = E.EmployeeKey
LEFT OUTER JOIN dbo.FactSalesQuota Q ON
S.EmployeeKey= Q.EmployeeKey AND
T.CalendarYear = Q.CalendarYear AND
T.CalendarQuarter = Q.CalendarQuarter
WHERE S.EmployeeKey IN
(SELECT TOP 10 EmployeeKey FROM FactResellerSales GROUP BY factResellerSales.EmployeeKey ORDER BY SUM(ExtendedAmount) DESC)
GROUP BY T.CalendarYear ,T.CalendarQuarter,T.MonthNumberOfYear, E.FirstName, E.LastName, E.EmployeeKey
ORDER BY Employee, 1

Insert table:

We want to insert a table grouped on Employee so one row for each employee is shown. Sparklines will later be added to this table.

  • Drag and drop table report item from the toolbox to report body.
  • Drag Employee field from dataset and place it in the details row.

image

  • Right click on the employee text box and insert a row group by choosing row group, group properties. In the group properties window choose Employee from the drop down for group on property.

image
image

Insert Sparkline:

We will configure a Sparkline with two data points, apply conditional formatting, and add Sparkline to the above table.

  • Drag and drop Sparkline from toolbox on to the report body. Select column as the Sparkline type and click ok.
  • Add sales, Quota from dataset to values and CalendarYear, CalendarQuarter to category groups.

image

  • Click on Quota from values and choose change Sparkline type. From select Sparkline type page, choose smooth line and click ok.

image

  • Click on Sales from values and choose series properties.

image

  • From the series properties page, choose fill and type the following expression.=iif(Fields!Sales.Value<Fields!Quota.Value,
    “red”,”green”)

Finally drag and drop Sparkline to  a cell next to Employee in the table we created earlier.
image

Leave a comment with any questions.

~Sam.

#SSRS error – An item with the same key has already been added

Here is a simple yet annoying error I ran into while creating a dataset. Below is the query that I was using and it would run just fine when executed from management studio.

SELECT
CalendarYear Yr
, CalendarMonthNum Mth
, CalendarMonthName Mth
FROM
dbo.Orders o

But SSRS doesn’t like those two columns with the same name “Mth” resulting in the error “an item with the same key has already been added”.
To resolve this simply make sure that no two columns in your dataset have the same name.

~Sam