SQL Server: Designing Key Performance Indicator Report in SSRS 2008

In this article, we will see how to display ‘Key Performance Indicators’ based on the different conditions in SQL Server Reports. For this demonstration we will use the ‘Northwind’ database.

Embedding ‘Key Performance Indicators’ in a report is a common requirement which is requested by every CFO, CEO and stakeholders of the business, in each and every industry.

SQL Server provides different ways to embed the indicators in a Report. We will use one of them while designing our report. So, let’s start by creating a SQL Server Report using Visual Studio 2008. The steps to create a report using Visual studio 2008 are available in my previous article SQL Server Reporting Services Tutorial: Designing a Basic Report using SSRS 2008 and VS 2008 BI Studio

I am using the Northwind database tables to fetch the data. Shown below is a query which I will be using for designing the report –



The above query selects the employee city, order required date, total orders taken by an employee and the total sales grouped by City, Product name and order required date. This query fetches the data only for the ‘LONDON’ city. The result is as shown below –



Once you create a project using ‘Report Server Project Wizard’ template, it will display a wizard. Let’s configure the report using the wizard as below –

Name the data source as ‘NorthwindDataSource’ and configure the connection to Northwind database by clicking ‘Edit’ button as shown below –



Click on the ‘Next’ button. This step will ask you to write a query which will fetch data from the Northwind database. Copy the above query. Now let’s make a small change in our query. Instead of making ‘City’ value fixed, let us allow the user to enter the value.

So replace E.City=’LONDON’ with E.City=@EmployeeCity as shown below –



Now click the ‘Next’ button. In this step we will select the report type. So let’s select ‘Tabular’ report and click on the ‘Next’ button.

In this step, we will design the table. So, select ‘Employee_City’ from available field section and click on ‘Page >’ button. Now let’s select ‘Order_Year’ field and click on ‘Group >’ button. Finally add ‘Total_Orders’ and ‘Total_Sales’ in details section by clicking ‘Details >’ button. Your ‘Design Table Window’ should look like below –



Click on the ‘Next’ button. In this step, we will choose the layout of the table. Make a choice of ‘Stepped report’. Check the check box ‘Include Subtotal’ and ‘Enable Drilldown’ as shown below –



Now click on the ‘Next’ button. Choose the table style as per your requirement. Click on ‘Next’ button.
In this step we will provide the deployment location as shown below –



Now let’s click the ‘Next’ button. In this step, we will give a title to our report ‘City wise Yearly Sales’. Click the finish button. Now your report should look like below –



Now click on ‘Preview’ tab and view the report. Enter city name ‘LONDON’ and click on ‘View Report’ button. You will see the yearly total sales and total orders for all the products.

But now I don’t want user to enter the city everytime. So instead let’s give a choice to them to choose the city using a dropdown box. For this demonstration, you will have to read my previous article – Design Parameterized Report using SSRS 2008.

For getting all distinct employees cities, I am using the following query –

SELECT DISTINCT City FROM Employees

After adding a parameter in your report, it should look like below –



Now let’s add the indicators to our report. You can add indicators as ‘Colors’ or ‘Images’. If you want, you can even make use of gadget images for showing indicators. For this demonstration, we will use different colors to show the yearly total sales. The conditions are mentioned below –

1) If the yearly total sale is more than or equal to 1, 75,000 then it should highlight the cell with green color.
2) If the yearly total sale is less than 1, 75,000 and greater than 85,000 then it should highlight the cell with yellow color.
3) And less than 85,000 then it should highlight the cell with red color.

For this, we will have to write the condition as shown below –

=IIF(Sum(Fields!Total_Sales.Value) >= 175000, "Green", IIF(Sum(Fields!Total_Sales.Value) < 85000, "Red", "Yellow"))

To add this expression, go back to the design mode of our report. Then right click on ‘Total sales’ textbox and go to ‘Textbox Properties’ as shown below –



From the ‘Textbox Properties’ window select ‘Fill’ section from the left side and click on fill color expression button as shown below –



Now copy the above expression in the set expression box and click ‘OK’ button as shown below –



Click ‘OK’ button to close the ‘Textbox Properties’ window. Go to ‘Preview’ tab and choose the city. Now click ‘View Report’ button and your report should look similar to the following –



Summary – In this article we have seen how to add Key Performance Indicators to our report based on our business conditions. Download the source code