Conditional filtering and to skip filter on empty report parameters

9 posts, 0 answers
  1. Sekar
    Sekar avatar
    15 posts
    Member since:
    May 2008

    Posted 15 Apr 2009 Link to this post

    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.


  2. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 16 Apr 2009 Link to this post

    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.
  3. DevCraft banner
  4. Steve
    Steve avatar
    7 posts
    Member since:
    May 2009

    Posted 22 Feb 2010 Link to this post

    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
  5. Massimiliano Bassili
    Massimiliano Bassili avatar
    323 posts
    Member since:
    May 2006

    Posted 23 Feb 2010 Link to this post

    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
  6. Steve
    Steve avatar
    7 posts
    Member since:
    May 2009

    Posted 23 Feb 2010 Link to this post

    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

  7. Ofir
    Ofir avatar
    1 posts
    Member since:
    Mar 2014

    Posted 15 Jan 2015 in reply to Steve Link to this post

    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...  

  8. KS
    KS avatar
    165 posts
    Member since:
    Oct 2012

    Posted 20 Jan 2015 in reply to Ofir Link to this post

    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

  9. John
    John avatar
    1 posts
    Member since:
    Apr 2016

    Posted 13 Apr in reply to Steve Link to this post

    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.

  10. Katia
    Admin
    Katia avatar
    299 posts

    Posted 18 Apr Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top
DevCraft banner