CODEWITHSUNDEEP
sql-serverreporting-servicesreport
michaalis
michaalis

Reputation: 131

Can I use different datasets according to the selected parameters using SSRS 2012?

It's the first time that I am using SQL Server reporting services. I have two date type parameters: Start Date & End Date. I can run the report correctly when I fill these two parameters with the corresponding dataset.

However, when the user fills only the Start Date, I would like to show a report from that date and later. e.g. If the user fills only the Start Date my query will be something like that 

WHERE StartDate >= @StartDate

or if the user fills only the EndDate field, my query will be something like that

WHERE EndDate <= @EndDate.

I have the following two questions:

1) Can I use different datasets according to what the user selects using SSRS? e.g if the user do not select anything I would like to run a different query.

2) and if yes how can I handle that ? because filter parameters are mandatory fields and all the time I receive the warning message.

Any advice would be appreciated. Thank you.

Upvotes: 1

Views: 833

Answers (1)

Jeroen
Jeroen

Reputation: 63709

It seems there is a simpler solution to your problem, if I've understood things correctly. You seem to indicate the parameters are optional. In that case, just Allow Null Values for the parameter, and rewrite your WHERE clause as follows:

WHERE (@StartDate IS NULL OR StartDate >= @StartDate)
AND   (@EndDate   IS NULL OR EndDate   <= @EndDate)

In any case, you can't "not execute a dataset" based on the chosen parameters (nor should you usually need to). If you do need something like that, a subreport is probably the way to go. However, based on your question I'd think there's a simpler solution available.

Upvotes: 2

Related Questions