SSRS 2005 : Using Stored Procedure with Report Parameters

Requirements:
Having a knowledge of T-SQL
Having a knowledge of SSRS (being able to create a report, a datasource and a dataset)

1. Creating the stored procedure.

The following example receives 2 input variables (a month and a 2-digit year) and returns a set of the previous month until the month of the previous year.

A picture is better than 1000 words!

So now the code.

CREATE PROCEDURE [dbo].[STP Name]
@ActMonth varchar(15),@ActYear varchar(4)
AS
BEGIN

DECLARE @id_Selected_Month tinyint

CREATE TABLE #Tmp_Table
(
[id_Month] [tinyint] NOT NULL,
[Month_Name] [varchar](3) NOT NULL
)

INSERT INTO #Tmp_Table VALUES (1,'Jan')
INSERT INTO #Tmp_Table VALUES (2,'Feb')
INSERT INTO #Tmp_Table VALUES (3,'Mar')
INSERT INTO #Tmp_Table VALUES (4,'Apr')
INSERT INTO #Tmp_Table VALUES (5,'May')
INSERT INTO #Tmp_Table VALUES (6,'Jun')
INSERT INTO #Tmp_Table VALUES (7,'Jul')
INSERT INTO #Tmp_Table VALUES (8,'Aug')
INSERT INTO #Tmp_Table VALUES (9,'Sep')
INSERT INTO #Tmp_Table VALUES (10,'Oct')
INSERT INTO #Tmp_Table VALUES (11,'Nov')
INSERT INTO #Tmp_Table VALUES (12,'Dec')

SET @id_Selected_Month = (SELECT [id_Month] FROM #Tmp_Table WHERE [Month_Name] = LEFT(@ActMonth,3))

CREATE TABLE #Tmp_OrderdedTable
(
[id_Act_Month] [tinyint] IDENTITY(1,1) NOT NULL,
[Month_Name] [varchar](10) NOT NULL
)

INSERT INTO #Tmp_OrderdedTable SELECT [Month_Name] FROM #Tmp_Table WHERE id_Month > @id_Selected_Month

INSERT INTO #Tmp_OrderdedTable SELECT [Month_Name] FROM #Tmp_Table WHERE id_Month <= @id_Selected_Month CREATE TABLE #Tmp_ActMonthTable ( ActMonth_1 [varchar](6) NULL, ActMonth_2 [varchar](6) NULL, ActMonth_3 [varchar](6) NULL, ActMonth_4 [varchar](6) NULL, ActMonth_5 [varchar](6) NULL, ActMonth_6 [varchar](6) NULL, ActMonth_7 [varchar](6) NULL, ActMonth_8 [varchar](6) NULL, ActMonth_9 [varchar](6) NULL, ActMonth_10 [varchar](6) NULL, ActMonth_11 [varchar](6) NULL, ActMonth_12 [varchar](6) NULL ) DECLARE @First_Month_ShortName varchar(6), @Month_ShortName varchar(6), @counter tinyint, @idMonth tinyint, @Output varchar(100), @ColumnName varchar(11) /* Act Month - 1 */ IF(@id_Selected_Month = 1)--if equal to January SET @First_Month_ShortName = (SELECT Month_Name FROM #Tmp_OrderdedTable WHERE [id_Act_Month] = 11)+ ' 0' + CAST((@ActYear - 1)AS varchar(2)) ELSE SET @First_Month_ShortName = (SELECT Month_Name FROM #Tmp_OrderdedTable WHERE [id_Act_Month] = 11)+ ' ' + @ActYear INSERT INTO #Tmp_ActMonthTable (ActMonth_1) VALUES(@First_Month_ShortName) /* Act Month - 2 to Act Month - 11 */ SET @counter = 1 SET @idMonth = 10 WHILE @counter <= 10 BEGIN SET @counter = @counter + 1 SET @ColumnName = 'ActMonth_' + CAST(@counter AS varchar(2)) IF((SELECT [id_Month] FROM #Tmp_Table WHERE [Month_Name] in(SELECT Month_Name FROM #Tmp_OrderdedTable WHERE [id_Act_Month] = @idMonth)) > @id_Selected_Month )
SET @Month_ShortName = (SELECT Month_Name FROM #Tmp_OrderdedTable WHERE [id_Act_Month] = @idMonth) + ' 0' + CAST((@ActYear - 1)AS varchar(2))
ELSE
SET @Month_ShortName = (SELECT Month_Name FROM #Tmp_OrderdedTable WHERE [id_Act_Month] = @idMonth) + ' ' + @ActYear

SET @Output = 'UPDATE #Tmp_ActMonthTable SET ' + @ColumnName + ' = ' + '''' +@Month_ShortName + ''' WHERE ActMonth_1 = ''' + @First_Month_ShortName + ''''
EXEC(@OutPut)

SET @idMonth = @idMonth - 1
END


/* Act Month - 12 */
SET @Month_ShortName = LEFT(@ActMonth,3) + ' 0' + CAST((@ActYear - 1)AS varchar(2))
UPDATE #Tmp_ActMonthTable SET ActMonth_12= @Month_ShortName WHERE ActMonth_1 = @First_Month_ShortName

/* Result */
SELECT * FROM #Tmp_ActMonthTable


/* Free space */
DROP TABLE #Tmp_ActMonthTable
DROP TABLE #Tmp_OrderdedTable
DROP TABLE #Tmp_Table

END

2. Configuring the dataset

• In the Data tab of your report, create a new dataset
• In the Dataset window, change the dataset name, the datasource if needed, the command type to 'StoredProcedure' and write the name of your stored procedure in the 'Query String' pane
• Go to the Parameters tab of the same window and copy the following parameters name and value :
  Name Value
  @ActMonth =TRIM(Parameters!Period.Label)
  @ActYear =RIGHT(Parameters!Year.Label,2)

Period is a list of month and Year a list of year from 2005 until 2009 and are both parameters of the report. The Label function is used to return the parameter caption.

• Click on the OK button, pass to the 'Generic Query Designer' and click on the exclamation point to execute your stored procedure. In the new window enter March as @ActMonth value and 08 as @ActYear value and then click the OK button.

Voila, you have linked the Report parameters to your stored procedure.

Now you can use the fields of your dataset in the Layout tab and see the results in the Preview tab

Have fun!