Would the following work
WHERE (
[Field1] in (@Field1_Filter)
OR (select count(*) from table where [Field1] in (@Field1_Filter)) = 0
)
Or if that's too expensive, you might add an extra query parameter and pass in the following expression:
=Parameters!Field1_Filter.Value.Count
Then your SQL could read:
WHERE ( [Field1] IN (@Field1_Filter) OR @Field1_FilterCount = 0 )
Dont use the filter in the SQL query. Instead keep your SQL query as a simple select statement and implement the filter in the dataset Filter using expressions like this:
For example, if your SQL query is "SELECT * FROM Table 1 WHERE ( [Field1] IN (@Field1_Filter) OR @Field1_Filter IS NULL )", change it to "SELECT * FROM Table1"
Now edit the dataset and go to Filter tab.
Under expression type this:
=IIf(Parameters!Field1_Filter.Value <> Nothing and Parameters!Field1_Filter.Count > 0, Fields!Field1.Value, 1)
Under Operator select "IN"
Under Value type this:
=IIf(Parameters!Field1_Filter.Value <> Nothing and Parameters!Field1_Filter.Count > 0, Parameters!Field1_Filter.Value, 1)
This way, when the filter parameter is null/nothing or count = 0, 1=1 will be evaluated which means no filter condition is applied.
Shyam
If you set allow null or allow blank for your multi-valued parameter, one of those values must be null or blank. For example, if your parameter is getting its values from a query, make the query to return one more row with blank value or null value. Or if your list of values is non-queried, add another value to the list with blank or null value.
Shyam
How about the following for the parameter query:
SELECT
Dim1Code,
Dim1Code + ', ' + Description AS Description
FROM
Dim1
union all
SELECT
'' as Dim1Code,
'-All Codes-' AS Description
WHERE (select count(*) from Dim1) = 0
Then the main query would read:
SELECT
E.EmployeeCode,
E.EmployeeCode + ' - ' + E.Lastname + ', ' + E.Firstnames AS Name
FROM
Employee E
WHERE
Dim1Code IN (@Dim1Code)
OR '' in (@Dim1Code)