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.
[…] We will see how to deal with drill down reports using SSRS 2012. For this report you can create a table and populate data from the post. […]