SSRS Parameters and SQL Functions/Stored Procedures

It doesn’t take too long to move from basic SSRS reports built entirely within the development client to more advanced reports that leverage SQL items such as table valued functions and stored procedures.

Most SSRS reports have parameters and quite often these parameters are multi-valued. If you try to pass these multi-valued parameters to your function natively, the following error message will result: Procedure or function dbo.YourFunctionName has too many arguments specified.

It is quite easy to fix this problem, but there are a few steps to it.

Step 1 – Decide on a delimiter.  The solution in summary is to string your multi-value parameters together in a long string and delimit them. I like ‘~’ as nobody seems to ever use the character.

Step 2 – Modify your SQL function to handle the extra parameters.  For example, the following section of code is my function and LocationList is the multi-valued parameter:

 CREATE FUNCTION [dbo].[Z_MyFunction]
(
	-- Add the parameters for the function here
	@LocationList as varchar(100)

)
RETURNS @ResultsTable TABLE
(
	Location varchar(50),
	TransDate smalldatetime,
	Details varchar(50),
	Amount numeric(18,2)
)
AS
BEGIN

	Insert @ResultsTable

		select t2.Description, t1.Date, t1.Details, t1.Amount from TransTable t1
		inner join Location t2 on t1.LocationCode = t2.LocationCode
		where t1.LocationCode in (Select * from dbo.Z_Split(@LocationList,'~'))

	RETURN
END

 

Note that LocationList is a long string. I don’t have that many locations available so 100 characters should be more than enough, even if every location code was selected from the multi-select box in SSRS.

The main body of the function is where the Select statement is. The where clause is obviously where the result set is filtered down. The function dbo.Z_Split returns a table with my Location items split on the delimiter ~.

Step 3 – Ensure the correct values are passed

My dataset uses codes for each location, but my end users prefer to see descriptive names. The query I use for the LocationListing dataset to return the list of possible locations for my parameter therefore looks as follows:

Select LocationCode, Description from Location
 

Which is used in the Available Values area for the parameter properties:

So now the user will see this when selecting values when they run the report:

Step 4 – Modify the parameter on the Dataset
Select your main dataset, right-click and select Properties, then chose the Parameters tab and press the fx button.

Instead of the default [@LocationListing] shown above, use the Join function with your delimiter (~ in my case) to wrap the return value from the multi-select box.

Now the report will run with multiple items selected for a given parameter.