"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

July 13, 2011

Adding KPI in SSRS Reports

After a long break back to SQL :). This post is about learning on adding KPI in SSRS report. This is extension of our first SSRS report. I learnt adding KPI from post.

Lets get started

Step 1 - Downloaded Red, Yellow, Green Images. Under Report Data->New->Images. Add the images.



Step 2 - Add a new column KPI at the end of the table


Step 3 - Under Toolbox -> Drag and drop Image option under KPI. Click on Image Properties



Step 4 - For Image Enter below expression

= SWITCH
(
 Fields!SaleValue.Value > 10000, "green",
 Fields!SaleValue.Value >= 2000 AND Fields!SaleValue.Value <= 10000, "yellow",
 Fields!SaleValue.Value < 1000, "Red"
)

Step 5 - For Tool Tip enter below expression

= SWITCH
(
 Fields!SaleValue.Value > 10000, "Green -> 10K",
 Fields!SaleValue.Value >= 2000 AND Fields!SaleValue.Value < 10000, "yellow 2K-10K",
 Fields!SaleValue.Value < 1000, "Red - < 10K"
)

Step 5 - Below is report with KPI listed


I'm extending this report to add Chart in the Same Report. Getting started with steps for the same

Step 1 - From Tool box Select Chart Option


Step 2 - Select the type of chart you want to display in report


Step 3 - Drag and Drop Fields from DataSet


Step 4 - Below is the sample display of report


Step 5 - Below is output of report. Changed the background to gray for better UI.


Step 6 - Followed tips provided in post to give this chart professional look. Below is the new chart layout


To add interactive reporting please follow steps in post

Please follow steps provided in post for configuring reporting services in sql server 2008. After Reporting services is setup. Steps provided below for deploying them. Configure the properties Report Server URL and Deploy using below snapshot.


After this step provide Report Server URL


After Deploying Open the browser in admin mode and access the report URL



More Reads

Let’s Get Visual: The Art of Report Design
SQL Server Reporting Services Conditional Formatting
Dashboard Design Best Practices (repeating on 6/9 at 11:45am)

Happy Learning!!!

No comments: