Wednesday, August 12, 2015

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
GO
   


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: 


24) In Gauge Type window, choose 90 Degrees Northeast gauge and click OK button as shown below:


25) Right click on 90 Degrees Northeast gauge and click on KPI in unspecified as shown below:



26) In Gauge Data, right click on RedialPointer1 and click on Scale Properties from Gauge panel as shown below:



27) In Redial Scale Properties, we need to set the Maximum value. For this, click on the function of Maximum field as shown below:



28) In function expression window, set the following expression as shown below: 


=Max(Fields!KPI.Value, "SalesPersonKPI")



 29) Click OK button on the expression and Redial Scale Properties also as shown below:


30) After doing the needful formatting settings for Indicator and Gauge, preview of the report as shown below:


This completes the tutorial section of the simple KPI's report.

Other Drill Reports in SSRS
1) To learn the Row Drill Down Report, Click here.
2) To learn the Row Drill Through Report, Click here.
3) To learn the Column based Drill Down Report, Click here.
4) To learn the Chart based Drill Down Report, Click here.

How can you create your first sub report. Click here.
To view the live implementation of KPI in SSRS, please visit at

No comments:

Post a Comment

Popular Posts