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
, and suppose that
can take values from a table called
. 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,
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
so that it queries the above dataset.
Next, in your main report
is used to constrain
, you should set things up like so:
SELECT Column2, Column3, ... FROM Table1
WHERE Column1 >=
WHEN -1 THEN 0
AND Column1 <=
WHEN -1 THEN (SELECT MAX(IdCol) FROM ValueTable)
This particular setup works because
ing both inequalities in the case when
@Parameter1 <> -1
will be equal to
. Otherwise, the inequalites merely request that
lies within the range of
, which is always
so it acts as a wildcard.
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