Home > SSRS Expert > SSRS – Using a Case Statement in a SQL Where Clause

SSRS – Using a Case Statement in a SQL Where Clause

I rarely use case statements in SQL where clause. But in SSRS reports, I find myself use it occasionally. It’s not hard to code it, it’s only the syntax that is tricky. I am very much against writing complex SQL code for any SSRS reports. A stored procedure will be a better solution for complex logics. Using staging is also common for data you need to bring over from other sources.

Goal

There are times in SSRS reports where a different field will be used in the where clause, depending on user’s selection of one or two report parameters. In this example, when user selects “Power On” as the Report parameter, the [PowerOnDate] will be used in the where clause; with the selection of “Power Off” as the Report parameter, the [PowerOffDate] will be used in the where clause.

Solution

You can write dynamic SQL. I am strongly against dynamic SQL, unless it’s your only solution. You can write a stored procedure with IF statement to construct different SQL statements depending on the parameter. I find a stored procedure is a little overkill in this situation. So I chose to write a straight SQL code with a case statement in the where clause.

image

Here is the code with a case statement in the where clause.

image 

Here is the part that shows the syntax.  

image

Categories: SSRS Expert
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: