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)