How To: Report Authoring in SSRS 2005 – Part V (Pimp Your Parameters)
Now we’re going to explore a couple of techniques on how we use our Parameter.
MAKE THE PARAMETER OPTIONAL
In order to make our parameter as optional, we will make a little modification on our Stored Procedure:
ALTER PROCEDURE [dbo].[Report_Department] @department NVARCHAR(50) AS BEGIN SET NOCOUNT ON; SELECT e.EmpFirstName, e.EmpLastName, e.EmpBirthDay, e.EmpSSN FROM Employee AS e JOIN Department AS d ON e.DeptID = d.DeptID WHERE d.DeptName = COALESCE (@department, d.DeptName) END
You may have noticed that I used the TSQL COALESCE Funtion in the where clause. See also about adding the “ALL” Parameter in this blog. We also need the same configuration we had for the the ALL parameter:
Ok, remember to update your Report Definition Language (RDL) in your Report Manager. After that, you should now have:
MAKE YOUR PARAMATER MULTIVALUED
We need some *split* tricks here by using a User Defined Function (UDF). I was trying to remember the source of the script but, unfortunately, I cannot recall. I have posted the TSQL Script below. Go ahead and create the Table-Valued Function into the database. If you recognize the script and know the coder, please let me know so I can add the credits.
Create Function [dbo].[Split]( @InputText Varchar(4000), -- The text to be split into rows @Delimiter Varchar(10)) -- The delimiter that separates tokens. -- Can be multiple characters, or empty RETURNS @Array TABLE ( TokenID Int PRIMARY KEY IDENTITY(1,1), --Comment out this line if -- you don't want the -- identity column Value Varchar(4000)) AS ----------------------------------------------------------- -- Function Split -- -- • Returns a Varchar rowset from a delimited string -- ----------------------------------------------------------- BEGIN DECLARE @Pos Int, -- Start of token or character @End Int, -- End of token @TextLength Int, -- Length of input text @DelimLength Int -- Length of delimiter</code> -- Len ignores trailing spaces, thus the use of DataLength. -- Note: if you switch to NVarchar input and output, you'll need to divide by 2. SET @TextLength = DataLength(@InputText) -- Exit function if no text is passed in IF @TextLength = 0 RETURN SET @Pos = 1 SET @DelimLength = DataLength(@Delimiter) IF @DelimLength = 0 BEGIN -- Each character in its own row WHILE @Pos <= @TextLength BEGIN INSERT @Array (Value) VALUES (SubString(@InputText,@Pos,1)) SET @Pos = @Pos + 1 END END ELSE BEGIN -- Tack on delimiter to 'see' the last token SET @InputText = @InputText + @Delimiter -- Find the end character of the first token SET @End = CharIndex(@Delimiter, @InputText) WHILE @End > 0 BEGIN -- End > 0, a delimiter was found: there is a(nother) token INSERT @Array (Value) VALUES (SubString(@InputText, @Pos, @End- @Pos)) -- Set next search to start after the previous token SET @Pos = @End + @DelimLength -- Find the end character of the next token SET @End = CharIndex(@Delimiter, @InputText, @Pos) END END RETURN
Again, if you recognize the code and know the coder of the script, please let me know so I can add the credits. I might have the author name on the original script that I used but I have used this a dozen times and might have modified and removed the author credits in the script (Honestly I can no longer remember).
Then after we have created the function, we modify our Stored Procedure like the one you will see below. I’m sure this was added in the tutorial that came with the script, again, mea culpa. Let me know if you know the site or source of the code.
ALTER PROCEDURE [dbo].[Report_Department] @department NVARCHAR(50) AS BEGIN SET NOCOUNT ON; SELECT e.EmpFirstName, e.EmpLastName, e.EmpBirthDay, e.EmpSSN FROM Employee AS e JOIN Department AS d ON e.DeptID = d.DeptID WHERE d.DeptName IN (SELECT [value] FROM dbo.split(@department, ',')) END
Take note of how we have used the Split function. We also need to set the parameter property to accept multivalue (see Part IV, Adding Parameters):
Below you can see the “Before” and “After” after we have applied the changes.
I hope this helps. Please come back for more tips and tricks on SSRS Report Authoring.
How To: Report Authoring in SSRS 2005 by Marlon Ribunal is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.
NEXT >> SSRS Weekday EMail Subcription