Managing Multi-Select Parameters in SSRS

Managing Multiple Choice Parameters $(document).ready(function () { $(".rmRootLink:contains('subscribe')").attr('href', '/Subscribe/tabid/444/List/1/CategoryID/99/Level/a/Default.aspx?code=WP211XHB '); }); Windows IT Pro For Admins SuperSite For Tech Enthusiasts SQL Server Mag For DBAs DevProConnections For Developers SharePointPro Connections For IT & Dev SQL Server Magazine

The Smart Guide to Building World-Class Applications

About Advertise Subscribe Follow Us RSS Why Join My Account Register Sign in Search InstantDoc IDGO Browse By: Author | Issue

Homenewsblogsbusiness intelligencedatabase administrationdatabase developmentPuzzled by T-SQLSQL SelectSQL Server BISQL Server Questions AnsweredTool TimeT-SQL and PowerShell Scriptingproducts & reviewsawards centralbuyer's guidereviewsnewslettersEventsRegister for DevConnections Conferences & WorkshopseLearning SeminarsRoadshowsVirtual EventsWeb Seminarscommunityfollow usForumsuser group centralresourcesebooksessential guidespodcaststip guidestrials & downloadsvideos & moreweb seminarswhite papersmoreHP MicrosoftConverged ApplicationsConverged Applications BlogMicrosoft UKsubscribe var dartrnum=Math.floor(Math.random() * 100000);document.write(' document.write(' Home » SQL Server Reporting Services (SSRS) » Managing Multi Select Parameters in SSRS

Browse By Category Business IntelligenceDatabase AdministrationKevin Kline: Thoughts from a SQL Server Old TimerDatabase DevelopmentT-SQL & PowerShell Scripting Doublelick Ads document.write(' February 11, 2011 11:08 AMManaging Multi-Select Parameters in SSRS How to create flexible parameter-driven queries bookmarkreprintsEmailPrintCommentsRating: (1)William VaughnSQL Server MagazineInstantDoc ID #128849Download the Code Here

DBAs and administrators who use SQL Server Reporting Services (SSRS) can sometimes run into a difficult situation: Users want to be able to choose more than one option in a report's pick list, but the report uses a parameter-driven query. In Hitchhiker's Guide to SQL Server 2000 Reporting Services (Addison-Wesley Professional, 2004), Peter Blackburn and I discuss how to cobble together a solution, but there have been a number of improvements to address this problem since that book was published. For example, SSRS's Report Processor has added support for this scenario. So, it's time to walk through an example of how to manage multi-select parameters using this new support.

For this demonstration, I created a new business intelligence (BI) project with Visual Studio 2008 SP1. (Without SP1, this example doesn't work because SP1 re-enables the BI functionality that was last supported in Visual Studio 2005.) You can also use the SSRS 2008 R2 upgrade to Visual Studio 2008. I won't bore you with step-by-step instructions on how to set up the BI project so that we can concentrate on how to build the report project.

To begin, you need to create a new BI Report Project, create a shared data source named dsAdventureWorks against the dsAdventureWorks2008 sample database, and add a report to the project. You can add this report with or without the Report Wizard. If you add this report with the Report Wizard, be aware that it contains a bug that prevents you from naming the dataset it creates. The problem presents itself if you have the temerity to change the dataset name using the Report Data window. If you encounter a problem like this, you'll have to manually edit the .rdl file to rename the dataset it references.

After you add the report, you need to:

Build a parameter-driven query that creates the initial dataset.Build a query to populate the pick list.Configure the parameter for the parameter-driven query.Test the report.

(You can download the completed project files by going to the top of this page and click the Download the Code Here button.)

Building the Parameter-Driven Query

To create the initial dataset, you can use the query shown in Listing 1. This query returns a set of rows from the AdventureWorks2008 Production.Products table along with the associated product photos in related tables. It returns rows based on a single parameter: the product's color, which will be presented to users in a drop-down list of acceptable (i.e., known) colors, including NULL.

As callout A in Listing 1 shows, the Products table query uses an IN clause that's coded to accept a parameter. This isn't going to be kosher as far as the T-SQL compiler is concerned because an IN clause can't contain a parameter. But don't worry—the Report Processor deals with this issue when the report is interpreted and rendered. How is this done? Well, the Report Processor cheats. It substitutes a generated IN expression (which contains a delimited list) into the query on each execution instead of passing the parameter as such. Unfortunately, it means that if you try to reference the parameter elsewhere in the query (e.g., when checking to see if a specific value is chosen), the delimited string is inserted. This severely limits the use of the parameter for other purposes.

The multi-select parameter is actually handled behind the scenes as a Value array. Each selected parameter value is added to this array. The first parameter in the ColorWanted parameters collection array is referenced by

=Parameters!ColorWanted.Value(0))

This permits you to reference each of the selected values individually. Thankfully, you don't have to leverage the parameter to provide a Select All option, as the Report Processor does this for you.

After the parameter-driven query executes, the results are exposed as a dataset (dsProductsByColor) in the Report Data window. As Figure 1 shows, each column is visible and ready to be dragged into a report.

FirstPrevious1234NextLast Add a Comment

There are no comments to display. Be the first one!
You must log on before posting a comment.

Are you a new visitor? Register Here document.write('Related ResourcesThe Essential Guide to SQL Server 2005 BI
Essential Guide by DellThe Essential Guide to Reporting Services Tips & Tricks
Essential Guide by 90 DegreeA Jump Start to SQL Server BI
EBook by MicrosoftMoving SQL Server to the x64 Platform
EBook by AMDMore Copy Paste fails with large result sets.

In SQL 2008 Management Studio. The copy fails on with large result set. I can select and copy small portions and paste in excel, notepad, word ect.......222-96701

More Posts Monitoring encryption keys created by TDE using SQL Audit Query Correlation to Resource Usage - % of X Upgrading from 2008 to 2008 R2 with SSIS document.write(' document.write(' GOOGLE LINKS SPONSORED LINKSdocument.write(' FEATURED LINKSdocument.write(' Downloads

Do you have to create custom code just to determine who has what permissions? Try Security Explorer, an enterprise security management solution for SQL, SharePoint, and more. As SQL expert Robert Davis reports, “DBAs who deal with compliance regulations will find this tool indispensible.”

Tip Guides

Migrating to Windows 7 comes with one major drawback: Win7 “application obsolescence.” This tip guide explains how VMware ThinApp can encapsulate applications in self-contained executables and how you can avoid the ten most common Win7 application migration pitfalls.

Web Seminars

Despite the promise of cloud computing, IT administrators may be hesitant to entrust the performance of their business-critical processes to a 3rd party. View this webinar to learn how you can reap the benefits of cloud computing while maintaining absolute control over your data.

eLearning Series

Windows IT Pro brings the experts direct to you to share their real-world perspective, experience, and expertise. During each event, three sessions stream in real time, allowing you to learn, to ask questions, and to get solutions.
Upcoming event: SQL Server Consolidation

SQL Server Magazine

DevProConnections ITTV Left-Brain SharePointPro Connections SQL Server Magazine SuperSite for Windows Windows IT Pro © 2011 Penton Media, Inc.

Home About Us Advertise Customer Service Privacy Statement Subscribe/Renew Terms Of Use

Windows is a trademark of the Microsoft group of companies. Windows IT Pro is used by Penton Media Inc. under license from owner.

function AttachAds() { var midpos = $("#wrapper").offset(); var midwidth = $("#wrapper").width(); var leftwidth = $("#leftad").width(); $("#leftad").css({ "left": (midpos.left - leftwidth) + "px", "top": midpos.top + "px" }); $("#rightad").css({ "left": (midpos.left + midwidth) + "px", "top": midpos.top + "px" }); } $(window).resize(AttachAds); AttachAds(); Sponsored Introduction Continue on (or wait seconds) ×

document.write('

View the original article here