How to create Param to filter data EQUAL to a value or NOT EQUAL to the same value

Marco Onnis 0
Viewed 23

I'm working on a simple report and my dataset looks like this:

ID Date Attribute Customer
1111 2022-03/15 NULL Cust_B
144529 2022-05/20 239 Cust_A
11223 2022-05/20 NULL Cust_C
168236 2022-05/20 66 Cust_A

I would like to create a Parameter to manage rows based on the Attribute column, so that I can:

  • select only rows with Attribute = 239
  • select only rows with Attribute != 239
  • select all rows

The Attribute Column is an INT column and could assume 20 different values (every number is a code for a specific type of attribute)

is it possible to do so with only a single Parameter?

I can work on the query on SQL Server

reporting-services sql-server
1 Answers

Yes, you can achieve this using a single parameter, but it's not that intuitive.

Firstly, you'll want a query like the following. As there are 3 options, I implicitly define the parameter as a bit:

SELECT {Column List}
FROM dbo.YourTable YT
WHERE (@ParameterName = 1 AND YT.Attribute = 239)
   OR (@ParameterName = 0 AND YT.Attribute != 239)
   OR (@ParameterName IS NULL)
OPTION (RECOMPILE);

Then, in SSRS you'll want to parameter as a boolean and allow NULL values.

In the Available Values pane of the parameters, you then need to define the 3 available values, which you'll want to be something like the following:
true - Attribute = 239
false - Attribute != 239
NULL - All Rows

Note, I assume you literally mean != 239, which means that rows where Attribute has the value NULL will be excluded, as NULL != 239 evaluates to UNKNOWN.

Larnu0
Related