Skip to content

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

August 11, 2009

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:

OPtional_config

Ok, remember to update your Report Definition Language (RDL) in your Report Manager. After that, you should now have:

Optional

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):

MultiValue_config

Below you can see the “Before” and “After” after we have applied the changes.

SimpleDropdown_VS_Checkbox

I hope this helps. Please come back for more tips and tricks on SSRS Report Authoring.

Creative Commons License
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

kick it on DotNetKicks.com

About these ads
4 Comments leave one →
  1. Deepak permalink
    April 21, 2010 4:27 AM

    “Ok, remember to update your Report Definition Language (RDL) in your Report Manager”

    Not sure how to update the Report Definition Language (RDL) using Report Manager. Where is this Report Manager? Due to this unable to view the changes.

    Any help will be appreciated.

    Thanks
    p.s. I am a newbie.

Trackbacks

  1. How To: Report Authoring in Ssrs 2005 – Part V (Pimp Your … « Wilson's Blog
  2. Twitter Trackbacks for How To: Report Authoring in SSRS 2005 – Part V (Pimp Your Parameters) « Marlon Ribunal's Note On SQL Server [dbalink.wordpress.com] on Topsy.com
  3. DotNetShoutout

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

  • Archives



  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video


  • Software Blogs - BlogCatalog Blog Directory
  • Follow

    Get every new post delivered to your Inbox.

    Join 28 other followers

    %d bloggers like this: