CODEWITHSUNDEEP
sqlreporting-services
AfricanHeart
AfricanHeart

Reputation: 21

SQL Parameters, How to give * (ALL) a default parameter?

I have a query:

 SELECT Job,Age,Name + + Surname AS FullName

Now I am filtering (in ssrs) per full Name. How do I set a default value in the full name drop down field to show all candidates by default?

Drop down field looks as follows:

EVERYONE
JON
Patric
KELLY
STEVE

Upvotes: 0

Views: 77

Answers (3)

Ross Bush
Ross Bush

Reputation: 15175

You can pullback your data similiar to the query below: 

SELECT * FROM
(
 SELECT OrderBy=2,FilterValue=SurName, Job,Age,Name + + Surname AS FullName
 UNION
 SELECT OrderBy=1,FilterValue=NULL, NULL,NULL,NULL + + '<ALL>' AS FullName
)
ORDER BY
    OrderBy,FullName

Then send in the FilterValue into your filter with a where clause similiar to:

SELECT * FROM MyTable
WHERE
    (@FilterValue IS NULL) OR (SurName = @FilterValue)

Upvotes: 1

Hogan
Hogan

Reputation: 70523

You probably want something like this:

WHERE upper(Name) = upper(@InParm) OR @InParm = '*'

Upvotes: 0

JohnHC
JohnHC

Reputation: 11195

Create a new multi-value parameter

Create a new dataset:

select Name + Surname as FullName
from MyTable

Set default of new parameter as query > new dataset

Set available to the same

In your main dataset, use:

select <columns>
from MyTable
where Name + Surname in(@NewParameter)

Upvotes: 0

Related Questions