SSRS Report Execution With Multi-Valued Parameter Through Stored Procedure – Part 2

In the first post of this two part series I outlined some of the deficiencies related to SSRS reports execution through a stored procedure, where multivalued parameters needed to be used in a report. In this post I would like to show how you can execute a report with multivalued parameters by simply typing in individual values, separated by commas into a parameter textbox. This is just an alternative method of filtering the report content. Why would this method be any more useful than allowing checkboxes to appear next to each value in the parameter?  Well, imagine that you have hundreds of values to filter by therefore hundreds of checkboxes to tick. Scanning through all the values to select only the ones applicable to what we want to filter by would be very time-consuming and frustrating. Additionally, this method can be used as a search interface for values which we are not completely certain about. All we need to do is to list them one by one and if the data related to the value is found in the source then it will be displayed in the report output, otherwise it will not come up after the report generation.

To demonstrate it hands-on, let’s create a simple dataset and populate it with some dummy data using the below script.

CREATE TABLE [dbo].[Table_1]
(
	[Item_SKU] [int] NULL,
	[Item_Category] [varchar](50) NULL,
	[Item_Price] [money] NULL,
	[Item_Price_Bracket] varchar (50)
)
ON [PRIMARY]

GO

DECLARE @Item_SKU int
SET @Item_SKU = 1
WHILE @Item_SKU <1001
	BEGIN
		INSERT INTO Table_1
			(Item_SKU, Item_Category, Item_Price, Item_Price_Bracket)
			VALUES
			(@Item_SKU, CASE WHEN @Item_SKU<100 THEN 'Category A'
							 WHEN @Item_SKU>=100 AND @Item_SKU<200 THEN 'Category B'
							 WHEN @Item_SKU>=200 AND @Item_SKU<300 THEN 'Category C'
							 WHEN @Item_SKU>=300 AND @Item_SKU<400 THEN 'Category D'
							 WHEN @Item_SKU>=400 AND @Item_SKU<500 THEN 'Category E'
							 WHEN @Item_SKU>=500 AND @Item_SKU<600 THEN 'Category F'
							 WHEN @Item_SKU>=600 AND @Item_SKU<700 THEN 'Category G'
							 WHEN @Item_SKU>=700 AND @Item_SKU<800 THEN 'Category H'
							 WHEN @Item_SKU>=800 AND @Item_SKU<900 THEN 'Category I'
							 ELSE 'Category J' END,
			@Item_SKU + 0.99, NULL
			)
	SET @Item_SKU = @Item_SKU + 1
END

GO

UPDATE Table_1
SET  Item_Price_Bracket = CASE WHEN Table_1.Item_Price < 500 THEN 'Price Bracket 1'
						  ELSE 'Price Bracket 2' END

When finished, the output should look like the table below (partial view only).

Now, let’s say I want a simple report that in a table format will display all the data contained in the table we have just created but filtered by only certain Item_SKU numbers. Instead of creating a multivalued parameter which will contain no fewer than 1000 rows, we can input multiple ‘Item_SKU’ numbers separated by commas. The only deficiency to this method is that the user will need to know the values for this parameter, rather than relying on the system to generate the filter list. In order to achieve this through stored procedure we can create a temp table within a SP and pass the parameter values from the report into the SP statement as per below.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE   PROCEDURE [dbo].[SP_Select_data_from_Table1]
(
--This variable will hold all our Item_SKUs
@Item_SKU varchar(100)
)

AS
--DROP (if exists) AND CREATE TEMP TABLE TO HOLD SKUs SELECTED BY THE USER
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#Temp_SKUs'))
DROP TABLE #Temp_SKUs
CREATE TABLE #Temp_SKUs (SKUs varchar(100))

--DECLARE SQL STATEMENT AND INSERT SKUs
DECLARE @SQL as varchar (1000)
SET @SQL = 'INSERT INTO #Temp_SKUs (SKUs) Select Item_SKU from Table_1 WHERE Item_SKU IN'
SET @SQL = @SQL + '(' + @Item_SKU + ')'
EXEC ( @SQL )

SELECT * FROM Table_1 WHERE Item_SKU IN (SELECT * FROM #Temp_SKUs)

This piece of SQL is pretty much self-explanatory – we pass the multiple values that the user want to filter by into a temp table created on the fly and then use them in the final ‘SELECT’ statement to get the ‘Table_1’ table data ‘WHERE’ values are in the previously created temp tables which again links to the SSRS parameters. Clean and easy! The report output is as per the image below where the values, separated by commas, are typed into the blank text box and then the report content filtered as per those.

Please also visit the first part to this post which can be found HERE.

Submit Article :- BlinkList + Blogmarks + Digg + Del.icio.us + Ekstreme Socializer + Feedmarker + Furl + Google Bookmarks + ma.gnolia + Netvouz + RawSugar + Reddit + Scuttle + Shadows + Simpy + Spurl + Technorati + Unalog + Wink

http://scuttle.org/bookmarks.php/pass?action=add

Posted in: How To's, SQL, SSRS

Tags: , ,

This entry was posted on Thursday, February 2nd, 2012 at 9:09 am and is filed under How To's, SQL, SSRS. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply