Click here to Skip to main content
Click here to Skip to main content

Catch-all clause in combo box filters for SQL Reporting Services

, 3 Oct 2011
Rate this:
Please Sign up or sign in to vote.
A (hopefully) simple way to implement a wildcard selection in combo box filters for Microsoft SQL Reporting Services
Here's the scenario. Suppose you want to create a report for a data set in Microsoft SQL Server Reporting Services, using report parameters to filter it. Let's call the parameter @Parameter1, and suppose that @Parameter1 can take values from a table called ValueTable. You wish to allow the user to pick an option for this parameter, but also you wish to allow them to pick a 'wildcard' option, which means "I don't care what the value of this parameter is."
 
This is a bit tricky. To accomplish this, ValueTable must have an identity primary key. Given this constraint, create a new data set and populate it as follows:
SELECT -1, '(All)' UNION
SELECT IdCol, DescriptionCol FROM ValueTable
You should set up @Parameter1 so that it queries the above dataset.
 
Next, in your main report dataset, if @Parameter1 is used to constrain Column1 from Table1, you should set things up like so:
SELECT Column2, Column3, ... FROM Table1
WHERE Column1 >=
(CASE @Parameter 
   WHEN -1 THEN 0 
   ELSE @Parameter1
END)
AND Column1 <=
(CASE @Column1
   WHEN -1 THEN (SELECT MAX(IdCol) FROM ValueTable)
   ELSE @Parameter1
END)
This particular setup works because ANDing both inequalities in the case when @Parameter1 <> -1 ensures that IdCol will be equal to @Parameter1. Otherwise, the inequalites merely request that @Parameter1 lies within the range of IdCol, which is always true so it acts as a wildcard.
 
HTH,
George

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

George Tryfonas
Engineer
Greece Greece
I am a software developer (mainly in C# and T-SQL) for a project management company in Athens, Greece. I have been working with computers since early 1987. I am adept at Pascal, C, C++, Java (my MSc was sponsored by Sun Microsystems), Lisp, Scheme, F#, C# VB.Net, Perl and some others that are too obscure to mention. When I want a quick and dirty solution to a programming problem I use a functional language, such as Haskell, Scheme or, more recently, F#.
 
I also play the keyboards and compose music.
 
---------------------------------------------------------
 
MSc Distributed Systems and Networks - University of Kent at Canterbury
BEng Computer Systems Engineering - University of Kent at Canterbury

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 3 Oct 2011
Article Copyright 2011 by George Tryfonas
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid