SSRS reports examples for 2012
I wanted to explore the different types of SSRS report hence I would be starting with a simple report. We’ll see creating SSRS report example in 2012.
To have more meaningful format for reports I am going to create a table with data which can help us in looking into different types of reports.
T-SQL Script:
Script File: sales_table_script
IF EXISTS(SELECT 1 FROM SYS.SYSOBJECTS WHERE TYPE='U'AND NAME='SALES')
BEGIN
DROP TABLE [DBO].[SALES];
END
GO
CREATE TABLE [dbo].[SALES](
ID INT IDENTITY NOT NULL PRIMARY KEY,
Organization VARCHAR(100) NOT NULL,
Country VARCHAR(100) NOT NULL,
Zone VARCHAR(20),
Product VARCHAR(100) NOT NULL,
SYear CHAR(4) NOT NULL,
Total_SoldOut INT DEFAULT(0) NOT NULL,
Total_Price MONEY DEFAULT(0.00) NOT NULL);
GO
INSERT INTO [dbo].[SALES](Organization,Country,Zone,Product,SYear,Total_SoldOut,Total_Price)
VALUES('CaBerry','USA','CA','OPhone',2010,14000,5600000),
('CaBerry','USA','WC','OPhone',2011,3000,1200000),
('CaBerry','USA','KL','OPhone',2010,5400,2160000),
('CaBerry','USA','CA','kPAD',2010,72,86400),
('CaBerry','USA','WC','kPAD',2012,56,67200),
('CaBerry','USA','KL','kPAD',2011,8,9600),
('OWNnOW','NewZealand','NZ-E','VM-R332',2010,12,1200000),
('OWNnOW','NewZealand','NZ-W','VM-R332',2011,16,1600000),
('OWNnOW','NewZealand','NZ-S','Router-R319',2010,56,672000),
('OWNnOW','NewZealand','NZ-E','Router-R319',2010,89,1068000),
('OWNnOW','NewZealand','NZ-W','BrC-100A',2012,34,272000),
('OWNnOW','NewZealand','NZ-S','BrC-100A',2011,109,872000);
GO
SELECT * FROM [dbo].[SALES];
GO
IF EXISTS (SELECT 1 FROM SYS.SYSOBJECTS WHERE TYPE='P' AND NAME='usp_Sales_Report')
BEGIN
DROP PROCEDURE [DBO].[usp_Sales_Report];
END
GO
CREATE PROCEDURE[dbo].[usp_Sales_Report]
AS
BEGIN
SET NOCOUNT ON
SELECT 'Organization'= Organization,
'Country'= Country,
'Zone'= Zone,
'Product'= Product,
'Year'= SYear,
'Total_SoldOut'= Total_SoldOut,
'Total_Price'= Total_Price
FROM [dbo].[SALES];
END
GO
EXEC[dbo].[usp_Sales_Report];
Now we have ready with the required data. Create a new SSRS report using “SQL Server Data Tools”.
As we are creating a new report using Wizard just follow the instructions.
A stored procedure is being called here. The result set returned by the procedure will be used in report.
Now the report look like below.
Add an image to the report.
After adding the image update report name as below and execute the report.
It’s just a simple report to retrieve a dataset using a stored procedure. Our main intension is to looking into SSRS functionality rather than query / procedure using for result set which is retrieving from database.
