A short while ago, I got the request to filter on a field which contained, beside regular values, also NULL values. They wanted to be able to filter on NULL values as well as regular values. Choosing multiple values should be an option as well.
The direct functionality of Report Builder did not offer me a solution. SQL was needed.
My example is based on the Northwind database of SQL Server.
I created two datasets. dsRegion provides the data for the parameter parRegion. The SQL looks like this:
SELECT region FROM customers
UNION
SELECT ' Empty'
ORDER BY region
Before ' Empty' I have put a space to get it at the top when sorted.
The parameter looks like this:
Allow multiple values is checked. Available Values is just the region in the Value field.
The SQL for the second dataset, dsCustomer, looks like this:
SELECT *
FROM
Customers
WHERE ISNULL(region,' Empty') IN (@parRegion)
Mind the space before Empty.
Now, the solution is there. It is possible to filter the records with no Region code as well as one or more regions.
The direct functionality of Report Builder did not offer me a solution. SQL was needed.
My example is based on the Northwind database of SQL Server.
I created two datasets. dsRegion provides the data for the parameter parRegion. The SQL looks like this:
SELECT region FROM customers
UNION
SELECT ' Empty'
ORDER BY region
Before ' Empty' I have put a space to get it at the top when sorted.
The parameter looks like this:
Allow multiple values is checked. Available Values is just the region in the Value field.
The SQL for the second dataset, dsCustomer, looks like this:
SELECT *
FROM
Customers
WHERE ISNULL(region,' Empty') IN (@parRegion)
Mind the space before Empty.
Now, the solution is there. It is possible to filter the records with no Region code as well as one or more regions.
Reacties