Hi

I am using the evaluation version of Enesys RS Data Extension v 3.3.34

I have written a <sqlQuery> for my report which includes a Where Clause specifying the parameters:

Select ProductID, ProductName, ProductType  FROM Products WHERE ProductName = @ProductName! AND ProductType = @ProductType!

I want the parameters to be optional i.e. All the products should be displayed by default in my rdl report. Currently it treats the parameters as mandatory. So basically I want the where clause to be applied only after I choose either one or both of the Parameters.

Can you please give an idea, how can I acheive this.

Thanks

Rajat

In case you would like to experiment before the release of the KB article,

Select     ProductID,
                ProductName,
                ProductType 
FROM Products WHERE ProductName = @ProductName! OR @ProductName! = 'ALL'
                                        AND ProductType = @ProductType! OR @ProductType! = 'ALL'

The reason for using ‘ALL‘ is that we are considering that you would certainly use a dropdown list to let the user select product name and type. In that respect, ‘ALL’ is a meaning full value for selecting all products and/or types. In the KB we’ll show you how to achieve the same filtering at the list level rather than in a “sqlQuery” which is much faster when the filter would only return a few items.

Adding ‘ALL’ to the parameters values can be easily done by using something like the following query in another dataset:

SELECT ‘ALL’ as ProductName

UNION

SELECT DISTINCT ProductName FROM Products

Regards,