Several people have asked about this, but I have not seen a response that works. I have several parameters that I want to use as filters. If a particular filter parameter is not entered, the report should not apply a filter on that criterion. It works fine to create a single value parameter which allows null, and use the following in SQL:
WHERE ( [Field1] = @.Field1_Filter OR @.Field1_Filter IS NULL )
How can the same effect be achieved for a multi-value parameter? If you try to create a multi-value parameter which allows null, the report builder gives an error. I can't use "allow blank value" because some of my parameters are integer parameters. Without the allow null, the report generator requires me to enter at least one value. So I don't know how you can get a value of "not entered" into a multi-value parameter.
I also don't know how you would check for the "not entered". Someone suggested the following SQL:
WHERE ( [Field1] IN (@.Field1_Filter) OR @.Field1_Filter IS NULL )
This generates an error when generating the report if multiple values are entered for the parameter (the normal condition). The error is " An expression of non-boolean type specified in a context where a condition is expected, near ',' ".
How is this supposed to be done?
Thanks for any insight
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
|||Thanks for the suggestions.
furmangg:
Your first suggestion wouldn't work because it would return everything anytime the filter didn't match, not just when the filter is empty.
I didn't think about using the count of the multivalue parameter. I think it's an array, so that could work either in the query (using another query parameter) or in a report filter. But how do I get an empty array in there? I'm using integer parameters. I can't set the parameter to allow null, and when I preview the report and leave the parameter blank, it tells me to enter at least one value. I tried it with string parameters, and the report just doesn't run in preview if you don't enter any values, even if you choose "allow blank".
How do you specify "empty" for a multivalue parameter?
|||Anyone have any ideas? Can someone at least confirm that this is a problem and not some screwy setup on my machine?
|||
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
|||Has anyone found a solution for this as I am having the same problem.
I have a multi-value parameter (Dim1Code) that is populated from a query:
SELECT
Dim1Code,
Dim1Code + ', ' + Description AS Description
FROM
Dim1
For some clients the query returns no data (which is fine) and so the drop down list is empty.
I have another parameter (EmployeeList) based on the following query:
SELECT
E.EmployeeCode,
E.EmployeeCode + ' - ' + E.Lastname + ', ' + E.Firstnames AS Name
FROM
Employee E
WHERE
Dim1Code IN (@.Dim1Code)
If the Dim1Code list is empty then it should return all employees, and I have not been able to find a way to get this working.
I have tried adding a union statement to the first query to also return a null row, but it wont let me set the "Allow Null" property on my parameter (I get an error as this is an invalid option for multi-value parameters).
If I set "Allow Blank" for my parameter and then run the report I am told that I need to select a value for the Dim1Code parameter.
If I union my first select with a hard coded value like "<None>" and change my second select statement to:
WHERE
Dim1Code IN (@.Dim1Code) OR @.Dim1Code = '<None>'
I then get an error when I try to run the report:
"An expression of non-boolean type specified in a context where a condition is expected, near ','"
I have tried the filtering options suggested further up in this thread and none of them worked for me either.
Is there really no way of having a valid "None" or "Blank" or "Null" option for multi-value parameters?
Why are multi-value parameters ALWAYS required to have a value?
|||
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)
