How To: Report Authoring in SSRS 2005 – Part V (Pimp Your Parameters)

Yes this “Report Authoring” series is still alive and kicking. We’ve learned how to add a parameter in our report in Part IV (See Part I, Part II, & Part III).

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