Hessel de Walle's Blog

The other day I ran in to a problem with Report Builder . I had a number of multi value parameters with a lot of values. And there was a single value parameter for selecting items too. The report rendered fine in design mode. But as soon as I published it, it was no longer rendering properly. The mutli value parameters also contained values which would not give any result at all given a certain item number. But then again, in design node no problem. The solution I finally came up with was to interrelate the multi value parameters to the single value parameter by using a SQL   subquery. Let's look at this example I created based on the NorthWind database. My main dataset: SELECT        Customers.CustomerID, Customers.CompanyName, [Order Details].OrderID, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount,                          ...

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 . N...