Microsoft Business Intelligence (Data Tools)|SSRS - KPI Reports (Indicators and Gauges)

SSRS reports are designed to display graphs, charts and KPI’s (Key Performance Indicators) very beautifully in the different kinds of the dashboards. A Key Performance Indicators (KPI) is any measurable value in our report that has business significance, for example, Total Sales. If we want to display the current state of a KPI's on a report, we can use one of three different approaches:

Highlight cell values using a background color that shows the state of the KPI.
Replace cell values with an image that shows the state of the KPI.
Insert a gauge.

Key Performance Indicators are typically displayed in a scorecard with directional, Symbols, shapes or rating indicators which are mostly either red, yellow or green icons. We are going to use Gauge in KPI's report also.

For example; Sales admin wants to see the KPI performance of his sales team. To meet the requirement, we will use the sales amount against target to get the KPI's values for each sales person. To generate the KPI's Sales Person Report, we will use the following data in the KPI Report as shown below:

SalesPerson	SalesPersonName	Sales	SalesTarget
1505	Caroline Reynolds	8391451.95	10000000.00
421	Garrick McPherson	6591451.95	10000000.00
3203	George Goeddeke	2063468.23	10000000.00
325	Jim Mollencop	3727894.74	10000000.00
3301	John Prymak	7391451.95	10000000.00
408	Leslie Roberts	2283584.43	10000000.00
324	Linda Barrett	7891451.95	10000000.00
2403	Lori Murphy	2168793.69	10000000.00
302	Martin Montano	2120988.66	10000000.00
1404	Michael Moore	6391451.95	10000000.00
2207	Sally Oldach	8285374.40	10000000.00

We need to display the above information in our SSRS report and Report header should be like as “Sales Person KPI” and report will be look like as shown below:

After click on the preview button, report will display KPI as shown below-

To create List Item based Drill Down report, we will follow the following steps as shown below:

1) In our existing report server project, we will add a new report as “Sales Person KPI” as shown below:

2) Now, we need to create a dataset for the report as shown below:

3) Add Dataset will launch Dataset properties window. We need to set the name as SalesPersonKPI for the Dataset and choose a dataset embedded in our report and choose Query Type Text as shown below:

select

SalesPerson,

SalesPersonName,

Sales,

SalesTarget,

KPI= Case when Sales>8000000 then 9

when Sales between 6000000 and 8000000 then 7

else 5 end

from

KPISalesPersion

order by SalesPersonName

4) After click on the OK button, a dataset has been added in the report data with parameters also as shown below:

5) Now right click on the report, add report header to the report and done the needful formatting as shown below-

6) After adding the report header, we need to add the Table to the report by the help of right click on the report as shown below :

7) Now, we need to add the dataset to the newly added Tablix as shown below:

8) Now, fill the Sales Person name, Sales Amount and Sales Target to the report as shown below:

9) Click on the preview button to view the report and report will show the following data after do the needful formatting as shown below:

10) Now we need to insert four blank columns between sales and target to put the indicator and gauge as shown below:

11) Preview the report as shown below:

12) Now we need to add our first flag indicator to our report. For this, we need to right click on our first blank column and choose indicator from insert as shown below:

13) Select Indicator Type Window, choose flags from Symbols and click OK button as shown below:

14) Now, right click on the flag indicator and click on Indicator Properties as shown below:

15) In Indicator Properties window, fill values KPI and click OK button as shown below (this value is coming from our database):

16) Preview again the report as shown below:

17) Now right click on the second blank column of table and choose indicator again. In indicator Type window, choose five stars from Ratings and click OK button as shown below:

18) Now, right click on the star indicator and click on Indicator Properties. In Indicator Properties window, fill values KPI and click OK button as shown below :

19) Preview again the report as shown below:

20) Now, right click on the third blank column of table and choose indicator again. In indicator Type window, choose 3 Up/Down Tringles from Directional and click OK button as shown below

21) Now, right click on the directional indicator and click on Indicator Properties. In Indicator Properties window, fill values KPI and click OK button as shown below ::

22) Preview again the report. In our report has three indicator such as Symbols – Flags, Ratting – 5 Stars and Directional – 3 Up and Down Triangles as shown below:

23) Now, we need to add the Gauge in our report. For this, we need to right click on the forth blank column of the table and choose Gauge as shown below: