CODEWITHSUNDEEP
sqldynamicreporting-services
Ola
Ola

Reputation: 1208

Dynamic sql query in SSRS (Sql Server Report Server 2012)

I have created in vs2010 a report with the name "clients", which shows a list of clients with the following attributes:

clientID,firstname,lastname,adres,country,birthday

I have created this report with a datasource and a dataset. In this dataset I have created a query like this:

select firstname, lastname, adres, country, birthday
from clients

This is working!

I would like to add 2 optonal parameters:

param_clientID ,param_birthDay

I would like to use these parameters in a where clausule ONLY if they are filled.

where clientID = param_clientID and birthday = param_birthDay

It should be possible that the clientID is filled, and the birthday parameter not. Otherwise also.

How can I do this?

Upvotes: 1

Views: 1226

Answers (1)

AndrewBay
AndrewBay

Reputation: 928

Adding (optional) parameters to you report is quite easy.

First of all make your parameters nullable.

select firstname, lastname, adres, country, birthday 
from clients
where (clientID = @clientID or @clientID is null) 
and (birthday = @birthDay or @birthDay is null)

For more detailed description:

Upvotes: 3

Related Questions