Wednesday, March 21, 2012

Dataset Filtering with multiple expressions

I would like to know how to dynamically change the And/Or value on the Filter tab within a dataset.

Here are my two expressions within the filter:

Code Snippet

=Switch(Parameters!TotalCostFilterOp.Value = "=",Fields!tcost.Value = Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = ">",Fields!tcost.Value > Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = ">=",Fields!tcost.Value >= Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "between",Fields!tcost.Value >= Parameters!TotalCost.Value AND Fields!tcost.Value <= Parameters!TotalCost2.Value,Parameters!TotalCostFilterOp.Value = "ie", ISNothing(Fields!tcost.Value), Parameters!TotalCostFilterOp.Value= "<",Fields!tcost.Value < Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "<=",Fields!tcost.Value <= Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "<>",Fields!tcost.Value <> Parameters!TotalCost.Value)

Code Snippet

=Switch(Parameters!CompanyFilterOp.Value = "%",LCASE(Fields!company.Value) like "*" & LCASE(Parameters!Company.Value) & "*", Parameters!CompanyFilterOp.Value = "=",Fields!company.Value = Parameters!Company.Value, Parameters!CompanyFilterOp.Value = "ie", ISNothing(Fields!company.Value), Parameters!CompanyFilterOp.Value = "?", LCASE(Fields!company.Value) LIKE LCASE(Parameters!Company.Value), Parameters!CompanyFilterOp.Value = "<>", Fields!company.Value <> Parameters!Company.Value, Parameters!CompanyFilterOp.Value = "?%", LCASE(Fields!company.Value) LIKE LCASE(Parameters!Company.Value) & "*")

The operator for both these is set to "=", and the value is set to "=true".

Based on user selection in a report wizard, the 4th column (And/Or) may need to be changed from 'and' to 'or', or vice-versa.

Is there anyway to accomplish this programatically?

Thanks in advance for you help!

James Dean wrote:

I would like to know how to dynamically change the And/Or value on the Filter tab within a dataset.

Based on user selection in a report wizard, the 4th column (And/Or) may need to be changed from 'and' to 'or', or vice-versa.

Is there anyway to accomplish this programatically?

Hi,

If you look at the rdl code you would see how it is, i.e. for a OR you would see

<Filters>

<Filter>

<Operator>In</Operator>

<FilterValues>

<FilterValue>Test</FilterValue>

<FilterValue>Test2</FilterValue>

</FilterValues>

<FilterExpression>=Fields!Testnull.Value</FilterExpression>

</Filter>

</Filters>

Someone else in this forum may be able to show you the way to programatically alter the RDL however I am not sure that you have time to do this.

Could not you use a stored procedure instead?

Data filters makes creation of data driven subscriptions easier, however for direct use, it will have to pull all the data before applying the filter and as you know filters have limitations.

Philippe

|||

No I cannot use a stored procedure to do the filtering, because the users are using a wizard to select which parameters they want to use and how they want to apply it to the data. So the dataset is created with a stored proc that gets all the data, and filters on the report are used to change the data based on the user's choices.

Thanks.