Drill Down Reports in SSRS with examples

Posted on May 1, 2013 by uday arumilli
UDAYARUMILLI_REPORTS

Drill Down Reports in SSRS with examples

We will see how to deal with drill down reports in SSRS with examples. For this report you can create a table and populate data from the post.

Let’s create a report using SQL Server 2012 Data tools. Create SSRS report using SSRS report wizard.

Sales Table Creation Script: sales_table_script



Substitute the query in design query window.

SELECT	SYear AS 'Year',
		Country,
		Organization,
		Zone,
		Product,
		Total_SoldOut,
		Total_Price
FROM	sales
ORDER BY [Year]





Now the report looks like below.

Do required alignments and update the report name and now the report looks like below.

Preview the report to make sure it’s been working fine and retrieving data.

Now we’ll go ahead and design drill down report base don year.

Under row group add a parent group for the column year.



Preview the report to have a drill down report.

Now edit the group details as below.

Now preview the report.

Expand the year check the view.

To give more clear view we will remove the column “Year”  (repeated values).

Preview the report; it will be the final view of the drill down report.

            This is the way to implement drill down view for SSRS reports.

Posted in MSBI, SQL Development, SQL Server DBA, SSRS | Tagged , , , , , , , , , , , | 5 Comments

How to create Parameterized report in SSRS 2012

Posted on April 24, 2013 by uday arumilli


How to create Parameterized report in SSRS 2012

udayarumilli_sql_server_2012_ssrs

In the previous post we have created a simple report. We are going to use the same report to demonstrate the parameterized report.

 

 

Under the same report solution copy and paste the Simple_Report and rename it as “Parameterized_Report”.

Now we can modify the report to accept parameters.

 

Now create a new stored procedure to accept the parameters as below.

 

USE[DBAConnect]
GO
CREATEPROCEDURE[dbo].[usp_Sales_Report_Parameterized](
@OrganizationVARCHAR(100)=’ALL’,
@CountryVARCHAR(100)=’ALL’,
@ZoneVARCHAR(20)=’ALL’,
@ProductVARCHAR(100)=’ALL’,
@YearCHAR(4)=’ALL’)
AS
BEGIN
SETNOCOUNTON
DECLARE@FilterVARCHAR(1000),@SQLVARCHAR(8000)
SET@Filter=”

 

IF (@Organization<>’ALL’)
BEGIN
SET@Filter=’ AND Organization = ”’+@Organization+””
END

 

IF (@Country<>’ALL’)
BEGIN
SET@Filter=@Filter+’ AND Country = ”’+@Country+””
END

 

IF (@Zone<>’ALL’)
BEGIN
SET@Filter=@Filter+’ AND Zone = ”’+@Zone+””
END

 

IF (@Product<>’ALL’)
BEGIN
SET@Filter=@Filter+’ AND Product = ”’+@Product+””
END

 

IF (@Year<>’ALL’)
BEGIN
SET@Filter=@Filter+’ AND SYear = ”’+@Year+””
END
SET@SQL=’
SELECT”Organization”=Organization,
”Country”=Country,
”Zone”=Zone,
”Product”=Product,
”Year”=SYear,
”Total_SoldOut”=Total_SoldOut,
”Total_Price”=Total_Price
FROM[dbo].[SALES]
WHERE1 = 1 ‘+@Filter

 

PRINT@SQL
EXECUTE (@SQL)
END

 

I have written a stored procedure to accept parameters and retrieve result set dynamically. Now we’ll change the report. Follow below steps.

Change the procedure execution statement to
“EXEC [usp_Sales_Report_Parameterized]  @Organization”



Add a new data set to support List of Organizations as an input to the report.

Use the query to retrieve the input set
SELECT’ALL’ASOrganization  FROMSALES
UNION
SELECTDISTINCTOrganizationASOrganizationFROMSALES;


Now map the newly created dataset to the parameter “Organization”.




Now preview the report, by default “ALL” is selected for Organization hence all records retrieved.

Now select an organization from the list.


Now let’s include all parameters as below.
  • @Country
  • @Zone
  • @Product
  • @Year

 

Parameters are added. Now add datasets to provide inputs for parameters as below.

 

DataSet :
Country:
SELECT’ALL’ASCountry
UNION
SELECTDISTINCTCountryASCountryFROMSALES

 

Zone:
SELECT’ALL’ASZone
UNION
SELECTDISTINCTZoneASZoneFROMSALES

 

Product:
SELECT’ALL’ASProduct
UNION
SELECTDISTINCTProductASProductFROMSALES

 

Year:
SELECT’ALL’AS[Year]
UNION
SELECTDISTINCTSYearAS[Year]FROMSALES

 

As we have done mapping between dataset and parameter for “Organization” follow those steps and map all datasets to corresponding parameters. Finally it looks like as below.

Now preview the report to check the output. As we designed by default all parameters are shown as “ALL”

Now select Organization and Country values.

Now select Zone and Product values as below.

This is a simple way to implement parameterized reports.

 

Posted in MSBI, SQL Development, SSRS | Tagged , , , , , , , , , , , , | 1 Comment

SSRS reports examples for 2012

Posted on April 17, 2013 by uday arumilli

SSRS reports examples for 2012

UDAYARUMILLI_REPORTS_1

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.

Posted in MSBI, SSRS | Tagged , , , , , , , , , , | 1 Comment