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.

Advertisements

About John Winford

Based in Vancouver, Canada I’m an IT professional with a number of specialties. First and foremost I am the front-line between the business users I enable and the technical team I represent. Not content to simply push the paper I also get my ‘hands dirty’ when required. I have an extensive amount of experience with technical project management, ERP implementations, BI work, and development across SharePoint, CRM and generic Widows applications.
This entry was posted in Business Intelligence. Bookmark the permalink.

5 Responses to SSRS Parameters and SQL Functions/Stored Procedures

  1. Bidyut says:

    Good stuff, well explained.

  2. sa says:

    Very helpfull!

  3. accessbob says:

    This helped me. Thanks.

  4. kylemit says:

    Where is dbo.Z_Split defined?

    • John Winford says:

      It’s not explicitly defined in this article, but as stated it “…returns a table with my Location items split on the delimiter ~.”. Similar to what is documented here (I no longer have the exact code as it was some years back).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s