Create a report builder 3.0 (ssrs) filter on boolean list of values in IN clause

So the latest requirement in my report building project is to provide the user with a checkbox (boolean) choice to include activities in the report which were marked as to be excluded from reporting when they were entered. I know it seems to sort of defeat the purpose of having such a flag in the first place but it is still something that the requirements definition says should be available to the report user. So in the report we add a boolean parameter to provide this option that looks something like this

Capture

and in the report itself looks like this…

Capture

The issue arises when we look to utilise this to filter the report data. In effect we are saying that by default we only want records where the dataset flag is equal to False but if the user selects True for the parameter we want the dataset to return records where the flag is equal to both True and False. Looking at the options for using dataset filters I could see a simple way of achieving this so I took a step lower down and implemented this requirement as part of the WHERE clause utilising the parameter within the SQL query. Basically I wanted a WHERE clause that in the default case was a pure ‘= 0’ and in the second case was an IN clause. Whilst a clause such as

 ActivityData.ExcludeFromReports IN (0,1)

would work in SQL I couldn’t find a way of having the parameter in Report Builder pass the (0) or (0,1) to the query as the validation parsing that occurs when trying to create this blocks it given that the field in the dataset is Boolean.

Here is the clause that I finally came up with that provides the filtering functionality described above..

AND (ActivityData.ExcludeFromReports <= 
        (CASE WHEN @IncludeExcludedActivitiesParameter = 1 THEN 1 
              WHEN @IncludeExcludedActivitiesParameter = 0 THEN 0 
         END))

Simple but elegant! We know that boolean can only be 1 or 0 so when we want all records returned we look for those where the ExcludeFromExceptions flag is less than or equal to 1 (hence both settings) otherwise we only return those records where the ExcludeFromExceptions flag is less than or equal to 0.

Advertisements

Leave a comment

Filed under Business Intelligence

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