Conditional filtering and to skip filter on empty report parameters

[Sekar]

Hi there
  I would like to setup filter expression for report so that if report parameters are given the detail section should filter by the parameter.
The expression goes like this

    Expression          Operator      Value
=Fields.ProductName      ==          =Parameters.ProductName

But i also want to list all the Products in the detail if no product name is specified. Please guide me how to write expression for that.

I tried all these below option

          Expression           Operator      Value
        =Fields.ProductName       ==     =Parameters.ProductName
 AND  =Parameters.ProductName     <>     ''

I also tried to do this filter check by code but raises me another question. Right now i am binding the datasource for the report in Report's constructor. And this constructor is not getting called when the Preview button is clicked. (Strange! I was expecting this to be called on every postback, not sure how it is working). So if i change any parameter value, this dynamic filter is not going to take effect.
Question 1:
Please let me know how i can control the filter to apply or not to apply on Preview button click event?
I am expecting to apply a logic to the filter like this
If (Parameters.ProductName != string.Empty) then filter the ProductName given in Parameter
else apply no filter and show all of them from the datasource.
   
Question 2:
The other way i tried to solve this is to filter the datasource in code before binding it. And here question is how can i rebind the datasource of the report on Preview Click (though initially i do that using Report Constructor)

Question 3:
And last question, the Filter expression editor by default ANDs all the expression added. Is there any way to add OR logic instead of AND.

[Steve]

Hi Sekar,

Thank you for contacting us and up to your questions:

1. You can handle this gracefully with a proper expression in the filter:
   
   Expression              Operator        Value
   ===================================
   
   =Fields.MyField             =        =IsNull(Parameters.ProductName, Fields.ProductName)
2. You cannot do this if you bind the report in the constructor, as clicking the Preview button does not go through the constructor, once the report has been loaded and cached in memory. You need to bind the report in NeedDataSource event, in order to change the underlying query.
3. Indeed we currently lack OR logical operator for which we apologize. The workaround is to combine your statements to achieve the desired effect i.e. if you have the following rule:
   
             Expression   Operator     Value
            ==========================
             =a                    =              =1
   (Or)    =b                    =              =10
   
   You can transform it like this:
   
                 Expression          Operator     Value
            ==============================
             =a = 1 Or b = 10         =              =true

Hope this helps.

Regards,
Steve
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Steve]

Hello,
Somewhat like Sekar, I would like to report on all records if the parameter is blank, except that I want to allow multiple values. I would prefer not to use null since that puts a Null check box in the UI.  I have tried...

=Fields.[Clinic_Number] In = IIf(Len(Parameters.ClinicNumber) > 0, Parameters.ClinicNumber, Fields.[Clinic_Number])

but I always end up with an error when I invoke the report

Error: One or more parameters are not set or have invalid values.

I've tried leaving the Value of  Parameters.ClinicNumber blank, and tried CStr("") all to no avail.  Btw - I tried all sorts of permutations of a Null valued parameter even though that isn't what I really want. 

I know there must be a way to do this; what am I missing?

Thanks,
Steve

[Massimiliano Bassili]

First to clarify that the IsNull function does not add Null checkbox in the UI, it is the AllowNull property of a parameter that does that. Second the error you have mentioned does not incline a problem with the filter, rather you have not set a value for a parameter. Check if all your params have Visible=true, or at least they have a default value.

Cheers

[Steve]

What I really wanted to do was initialize my multi-value parameter to the list of possible values that existed in my data table (as was listed in the UI for the parameter on the Silverlight ReportViewer).

 

After doing more research I found that perhaps my multi-value parameter had to be set to a collection of string values.  I found no way to do that from the UI dialog.  More research suggested using an ArrayList and setting the value in my report code.  I attempted to do this in the constructor for my report, but this caused an exception.  I happened across an example where the parameter was initialized with a static list of values using new object[] {"123", "456"}.  I test this and it worked!  Now all I had to do was copy my ArrayList into an object[] and assign that to the parameter.  Below is the working code.

 

public partial class HighRiskClientsReport : Telerik.Reporting.Report
    {
        public HighRiskClientsReport()
        {
            /// <summary>
            /// Required for telerik Reporting designer support
            /// </summary>
            InitializeComponent();

            // TODO: This line of code loads data into the 'clientDataSet.ClientDataSetTable' table. You can move, or remove it, as needed.
            try
            {
                this.clientDataSetTableAdapter1.Fill(this.clientDataSet.ClientDataSetTable);

                ArrayList clinicNumbers = new ArrayList();
                foreach (DataRow row in this.clientDataSet.ClientDataSetTable.Rows)
                {
                    if (!clinicNumbers.Contains(row["Clinic_Number"].ToString()))
                    {
                        clinicNumbers.Add(row["Clinic_Number"].ToString());
                    }
                }
                // not sure why, but these variations caused an InnerException so I added the code below and it works
                //this.ReportParameters["ClinicNumber"].Value = clinicNumbers;
                //this.ReportParameters["ClinicNumber"].Value = (object)clinicNumbers;

                // working code
                object[] obj = new object[clinicNumbers.Count];
                for (int n = 0; n < clinicNumbers.Count; n++)
                {
                    obj[n] = clinicNumbers[n];
                }
                this.ReportParameters["ClinicNumber"].Value = obj;
            }
            catch (System.Exception ex)
            {
                // An error has occurred while filling the data set. Please check the exception for more information.
                System.Diagnostics.Debug.WriteLine(ex.Message);
            }
        }
    }

Thanks for the help,
Steve

[Ofir]

I think this answer must be on the filters documentation page - this scenario is quite common for reports and this solution helped me a lot after I almost gave up because there isn't an apparent "OR" condition in the filter dialog, and I can't access the filtering logic with any hook...  

[KS]

Hi,

The expression should be a mix of operators and conditional functions like:
= Fields.X is Null OR Parameters.Parameter1.Value is Null Or Fields.X in Parameters.Parameter1.Value

The rest of the filter should be:
Equals (=)
=True

-KS

[John]

Hi there,

 

I am trying to do the exact same thing, but with Telerik Report Designer. 

 

How would I set this: 

 

Expression              Operator        Value
===================================

=Fields.MyField             =        =IsNull(Parameters.ProductName, Fields.ProductName)

 

 

From the Standalone report designer? I don't quite understand Where I should be writing that.

 

When I write my query and proceed, I get a list of Parameters used in my report, I get `Configure data source parameters` window with three columns

 

Name: has list of @Parameters

DbType: has DbTypes (int32, DateTime etc)

Value: values

 

I click on Values and select 'New Report Parameter' but don't see where I can enter the expression anywhere.

 

I want something simple as 

 

Select * from Product

Where ProductTypeId in (@ProductTypeIds)

 

I can configure @ProductTypeIds to be multivalue and it all works fine, but sometimes I just need to select all products (ProductTypeId is nullable) so I would like to not have the filter at all.

[Katia]

Hi John,

In order to filter report results based on the parameter's value, you need to add the rule to report's filters collection. From report's settings -> Filters you will be able to access Edit Filter dialog where you can add the required filter.
More detailed information is available in How to: Add filtering to Report help article.

Data source parameters can also be mapped to report parameters - Using Parameters with the SqlDataSource component. In this case, you should specify the parameter from report's parameters collection as a value on "Configure data source parameters" step. You must ensure that the parameter names that you use in your SQL command correspond to the names of the parameters in the associated collection.


About the specific case, please this and this forum discussions.

Regards,
Katia
Telerik