SQL Server Reporting Services provides several ways to analyze the data; a few of them are creating reports with indicators and Gauges. Indicators are minimal gauges that convey the state of a single data value at a glance and are mostly used to represent the state value of Key Performance Indicator (KPI, a measurable value which has business significance with a specific target or goal that indicates whether things are going good or bad). Indicators can be used in either dashboards or free-form reports but more commonly are used in tabular or matrix reports to visualize data in rows or columns. There are several types of indicators, which you can use in your report as shown below:
Types of Indicators
As shown in the image below, there are basically two types of gauges, viz. radial (which is basically circular; resembling a speedometer and used for expressing the data as a velocity) and linear (which is basically rectangular; resembling a ruler and used for expressing the data as a temperature or scale value). Each of these has one gauge panel and one or more gauges inside it (multiple gauges in a single gauge panel can be used to compare data between fields).
Multiple Guages in a Single Guage Panel
As I said in my last article (Creating Chart Report in SSRS – SQL Server 2008 R2), before you start creating reports with Indicators and Gauge it’s essential to understand and prepare the data to be analyzed so that you can choose the indicator or gauge type appropriately and create reports quickly and efficiently.
In this article, I am going to demonstrate how you can go about creating, modifying and beautifying the report with Indicators and Gauges easily and quickly.
Creating a tabular/tablix Report in BIDS (Business Intelligence Development Studio)
If you are new to SSRS or you are creating an SSRS project for the first time, the first thing that you need to do is to create an SSRS project. To do that, go to START -> All Programs -> SQL Server 2008 R2 (if you have installed SQL Server 2008 R2) and click on SQL Server Business Intelligence Development Studio. On the New Project dialog box select “Report Server Project” template and then specify the name and location for the project as shown below:
In the Solution Explorer, right click on the “Reports” folder and click on Add -> New Item as shown below:
Solution Explorer: Add New Item
In the Add New Item dialog box, select the “Report” template and specify the name of the report that you are creating, as shown below:
Once you have created a blank report as above, the next step is to create a data source, which specifies the source of data for the report. To create a data source, right click on the “Data Sources” folder in the Report Data pane and click on “Add Data Source” as shown below. This will take you to a dialog box where you can either use Shared Data Source or create and\or embedd a new Data Source in the report.
Add Data Source
Next you need to create a dataset; to create it right click on the “Datasets” folder in the Report Data pane and click on “Add Dataset” as shown below.
Clicking on the above menu will take you to a dialog box where you can select the data source and specify the query, fields, filters and parameters to get the data from the source.
Choose a Data Source and Create a Query
This is the query that I have used for this demonstration; this brings the product sales information, sub category and category wise from the AdventureWorksDW2008R2 database:
USE AdventureWorksDW2008R2 SELECT DPC.EnglishProductCategoryName, DPSC.EnglishProductSubcategoryName, DP.EnglishProductName, SUM(FRS.SalesAmount) AS SalesAmount FROM dbo.FactResellerSales FRS INNER JOIN DimProduct DP ON FRS.ProductKey = DP.ProductKey INNER JOIN DimProductSubcategory DPSC ON DP.ProductSubcategoryKey = DPSC.ProductSubcategoryKey INNER JOIN DimProductCategory DPC ON DPSC.ProductCategoryKey = DPC.ProductCategoryKey GROUP BY DPC.EnglishProductCategoryName, DPSC.EnglishProductSubcategoryName, DP.EnglishProductName
Now, as we are done with creating the SSRS project, creating Report, creating Data Source and creating Dataset, the next step is to create a tabular or tablix report to display product sales information. To add a Table to your report, go to Toolbox and under the Report Items pane you will see the Table icon as shown below; drag it to the report designer area. Next, go to the Report Data pane, drag the [EnglishProductName] and [SalesAmount] columns from the dataset that we created earlier and place them to the columns of the table as shown below. You can change the header of the column to whatever you want; for example in my case I have changed it to [Product Name] for [EnglishProductName] column to make it more intuitive.
Toolbox: Report Items
Now I want to add groups to drill down the report from Product Category to Product Subcategory to Product wise sale. On the bottom of the report designer, you will notice a section where you can add Row Groups and Colum Groups. By default, one group (named [Details] which shows product wise sale) is created and we have to add a parent group to it for product subcategory and another parent group for product category. Click on the tiny down arrow icon on the right side of [Details] group, click on Add Group and then click on Parent Group menu item:
Add Parent Group
Clicking on the Parent Group icon, as above, will pop up a dialog box where you need to specify column on which parent group for [Details] group will be created; for example I want product to be grouped first by product subcategory and hence I have chosen [EnglishProductSubcategoryName] as shown below:
Next we have to create the product category group, which will group the product subcategory groups that we just created above. To create a parent group (product category), follow the same process but this time, click on the tiny down arrow icon, which appears next to the [EnglishProductSubcategoryName] group that we created just now and specify [EnglishProductCategoryName] column to be grouped by as shown below:
Okay so far, we have created Product Category, which drills down to Product Subcategory, which further drills down to Products and its sales. So far so good. Now let’s add to display total sale amount for the group we drill down to. To add a row for showing total Product Category sale, click on the tiny icon that appears on the right side of the [EnglishProductCategoryName] group, click on Add Total and then the place where you want it to be displayed; in my case I chose to display it at the end of the each group. Similar to that, you can add to display total sale for each Product Subcategory as well:
Display Total Sale
Now after adding groups and total for each group, the screenshot below is what your report will look like in report designer. I have done some formatting in the header row (changed the background color, made the font to appear bold, changed the column title) of the table so that it looks different than the other rows of the table:
That’s all, we are now good to go and preview the report; when you preview your report, your report will be similar to the screenshot below. As you can see, the first column shows the product category, the second column shows the entire product subcategories belonging to that category, the third column shows all of the products belonging to each product subcategory and the fourth column shows the sale amount for each product. Along with that you will also notice, total sale amount each product subcategory and product category wise:
Preview the Report
SQL Server Reporting Services allows you to embed indicators and gauges in your report to analyze the data and its state. Indicators are minimal gauges that convey the state of a single data value at a glance and are mostly used to represent state value of Key Performance Indicator (KPI).
In this article, I talked about the details of Indicator and Gauge and step by step I created one tablix report with two levels of grouping, which I will be using in the next article (Creating Indicator and Gauge in SSRS – SQL Server 2008 R2 – Part 2) to add different types of indicators and gauges to it.