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!!!

Passing parameters to SSRS Drill down reports

When passing data between reports there are two possibilities

• Passing Data from Output Data
• Pass the Query FilterData

Please refer to my earlier post for detailed explanation of steps.

 We will look at an example for Drill Down Report Example and Passing paramaters to it. Below Two Simple Procs I created in the Database

STEP 1

CREATE PROC TestProc1
(
@Param1 INT,
@Param2 INT
)
AS
BEGIN
SELECT 100 AS 'Result'
RETURN 1
END

CREATE PROC SUBProc2
(
@VALUE1 INT,
@VALUE2 INT,
@Value3 INT
)
AS
BEGIN
SELECT @VALUE1 as 'Outout1',@VALUE2 as 'Output2', @Value3 as 'Output3'
RETURN 1
END

STEP2
Now I am going to create Report from Proc TestProc1. In the SSRS Report Server Project I created a blank report, Created DataSet and Input Parameters. I would explain it step-by-step below

STEP3 - Next Step is creating dataset using proc TestProc1

 

 STEP 4 - For the input param create a Dataset and assign values as defined below

 

STEP 5  - Parameters and DataSet would be like below



STEP6 - Assign Created Dataset for Parameters as below



STEP 7 - Assigning Default value for Parameter param1



STEP 8 - Assigning input for Param2



STEP 9 -Now for Second Report using Proc SubProc2 Dataset Creation as below



STEP 10 - After providing dataset Input parameter would be listed as below when you specify Refresh Fields in Step 9



STEP 11  - DataSet creation for parameters for Report2



 For Second Parameter Data Set creation

STEP 12 - Assigning Parameters and Dataset

STEP 13 -Assigning Dataset for parameters



STEP 14 - This is the step where we assign Sub Report. Under Output->TextBox Properties->ReportName and Parameters

For Passing Filter Criteria we specify [@Param1], for passing current result we specify [Result]



Its pretty lengthy blog but last step is the important one. It worked finally.

Drill Down Report in SSRS

This is in continuation with my first blog entry link

1. Create a new report based on Wizard



2. Provide the stored proc in the query builder window



In the next window select Tabular form.
3. Choose options for group by based on city as described below



4. Select option for Drilldown



5. Complete the next steps to complete the report
6. Goto Dataset Properties



7. Change it into a stored proc.


8. You will see the report output like the drill down report option. The input parameter and the proc need to be modified to show it as a drop down list.

My First SSRS Report

It was fun learning SSRS and Report Creation. I learnt to create Reports. I am going to post an example of creating a standard report using parameters. Report is going to pass parameters to stored procedures and fetch data.

I am doing it on SQL Server 2008 R2

Example Table and Stored Procs
use tempdb

create table dbo.Earnings
(Name VARCHAR(100) NOT NULL,
SaleDate DATETIME NOT NULL,
SaleValue INT NOT NULL,
City VARCHAR(100) NOT NULL
)

INSERT INTO Earnings (Name,SaleDate,SaleValue,City)
VALUES ('Raja',GETDATE()-1,2500,'Chennai'),
('Raja',GETDATE()-2,2000,'Chennai'),
 ('Raja',GETDATE()-3,4000,'Mumbai'),
('Raja',GETDATE()-30,9000,'Hyderabad'),
('Ram',GETDATE()-1,9000,'Hyderabad'),
('Ram',GETDATE()-2,5000,'Chennai'),
('Ram',GETDATE()-30,8500,'Delhi'),
('Robert',GETDATE()-2,9000,'Hyderabad'),
('Robert',GETDATE()-30,9000,'Hyderabad'),
('Siva',GETDATE(),90,'Bangalore')

CREATE PROC SaleValue
(@FromDate DATETIME,
@ToDate DATETIME
)
AS
BEGIN
SELECT Name, SaleDate, SaleValue, City
FROM dbo.Earnings
WHERE SaleDate >= @FromDate
AND SaleDate <= @ToDate
END

DECLARE @FromDate DATETIME, @ToDate DATETIME
SET @FromDate = GETDATE()-100
SET @ToDate = GETDATE()
EXEC SaleValue @FromDate,@ToDate

CREATE PROC SaleCity
(@City VARCHAR(100)
)
AS
BEGIN
SELECT Name, SaleDate, SaleValue, City
FROM dbo.Earnings
WHERE City like @City
END

DECLARE @City VARCHAR(100)
SET @City = 'Chennai'
EXEC SaleCity @City
 
Creating Report

Step 1 – Create a Data Source Connection

Step 2 – Adding Report. Under Reports ->Add New Item->Select Report. It will create a report.rdl file.

Step 3 – Under Report Data. New->Data Source. Select Shared DataSource and Select it as shown in below pic.

Step 4 – New DataSet. Name, Data Source and Select Stored Procedure as shown below.

Refresh and see parameters as shown below. It is under Parameters tab.

Step 5 – Now If you refresh Parameters would be automatically listed

Step 6 – Under Toolbox drag a table, as shown in screen.

Step 7 – Drag and Drop DataSet Columns as shown below in screen

Step 8 – Click on preview, You will see FromData, ToDate and When you click on view report you will see output.

Note: I added footer, Set Font color to make it little better :-)

Now In the Similar Way I need to create Report for Second Proc that we created. SaleCity Procedure.

I am going to link this as a link to the mail report. When you click on City. It would provide you city level details.

Linking a Report

In the First Report. Under City. Click on Text properties and goto actions tab and fill the sub report details as shown in below screen shot.

Now You can try from Salevalue Report. When you Click on Report it would show City details.

Now, Adding a Little Graph Display. Drag a Chart from Toolbox, Data - SaleValue, Category-City

Final Report listed below...

I am happy I am sharing what I learnt in last few days :-)...

Happy Reading....Will come back with some interesting SSIS stuff....

I below link useful for creating report. I got the link after posting the blog :).

Expression Examples (Reporting Services)