Report Filtering using IN

13 posts, 1 answers
  1. Luc
    Luc avatar
    7 posts
    Member since:
    Apr 2008

    Posted 15 Sep 2008 Link to this post

    Hello,

    I'm trying to filter a report programmatically using the "IN" operator using Reporting 2008.Q2.  I'm adding the filter using this code:

                Filter filterObj = new Filter(pField, FilterOperator.In, sbCrit.ToString());
                Filters.Add(filterObj);

    pField is a string containing the field name in the format ("Fields.[my_field_name]").  I don't think this part is the problem because it works fine when I only have one value and use the Equal operator.

    sbCrit is a StringBuilder I'm using to build the criteria.  I've tried many way to present the criteria and I'm now short of ideas.  I've tried with values enclosed between quotes ("), between apostrophes ('), with the criteria enclosed between
    parenthesis or not, I've tried to put equal signs before every value, and now I'm short of ideas.

    Can someone explain me the correct syntax for an IN criteria into a report?

    Thank you very much
  2. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 15 Sep 2008 Link to this post

    Hi Luc,

    Here is a filter expression auto generated by the Reporting designer in the InitializeComponent method:

    this.Filters.AddRange(new Telerik.Reporting.Data.Filter[] {
                new Telerik.Reporting.Data.Filter("=Fields.MyField", ((Telerik.Reporting.Data.FilterOperator)(Telerik.Reporting.Data.FilterOperator.In)), "=Parameters.Parameter1")});

    where Parameter1 is of type string.

    Best wishes,
    Steve
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  3. DevCraft banner
  4. Luc
    Luc avatar
    7 posts
    Member since:
    Apr 2008

    Posted 15 Sep 2008 Link to this post

    Thanks Steve for the quick answer.  However, this is not the information I need.  After re-reading my original post, I realized it's quite cryptic, and I'm sorry for that.  I'll try to explain my problem in a better way.

    In our application, our customers need to filter reports using multiple values for the same field.  For instance, they can ask for values A, B or C for MyField. In SQL, we'd write the query as (select * from MyTable where MyField in ('a', 'b', 'c').  That's the kind of filter I try to apply to the report.

    Taking your exemple, I tried setting Parameter1 to the values listed down here and it didn't work.  What should I set it to?

    Thanks

    List of values I tried:
    • ('a', 'b', 'c')
    • ("a", "b", "c")
    • 'a', 'b', 'c'
    • "a", "b", "c"
    • (='a', ='b', ='c')
    • (="a", ="b", ="c")
    • ='a', ='b', ='c'
    • ="a", ="b", ="c"
    • =('a', 'b', 'c')
    • =("a", "b", "c")
  5. Answer
    Steve
    Admin
    Steve avatar
    10941 posts

    Posted 16 Sep 2008 Link to this post

    Hi Luc,

    Thank you for the clarification. I now understand what you're trying to achieve, unfortunately such syntax is not yet supported ('a', 'b', 'c'). The Value field of the expression currently looks for an array (what multiple value parameter actually returns). We understand that this is not very convenient and have added support for additional value expressions in our TODO list. Otherwise the last two options that you've tried would be the correct ones, once we have this feature implemented.
    For the time being, please make sure that you pass the value of type array. You can create a user function that takes care of that and invoke the function in the value field.

    Sincerely yours,
    Steve
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  6. Stephan
    Stephan avatar
    71 posts
    Member since:
    Jul 2007

    Posted 26 Jan 2009 Link to this post

    Hi Steve,

    I'm not sure what you are saying. I tried to get the IN-filter working, but withou any succes. I'm using reporting 2.9.8.1105.

    Basically I try to achieve the same as luc. I understand i cannot use a construction like (1,2,3). You say write a custom function which will return an array, but if I use something like:

     Filter reportFilter = new Filter("Fields.Priority", FilterOperator.In,"1,2,3");
     Filters.Add(reportFilter);

    It doesn't work. I have to say the Priority field is of type int. So can you provide me with a sample how to use the IN-filter. I know how to create an array, but an array is not supported as the thirth parameter of the new Filter contructor.

    Any help is apprecitated.
  7. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 29 Jan 2009 Link to this post

    Hello Stephan,

    Generally the IN operator has been created for use with multivalue parameter. Since the last argument of the Filter function expects a string, but we're aiming for an array, we can handle this with user function as I've mentioned below. For example consider the following user function:

     public static string[] RecordCount()
            {
                return new string[] { "1", "2", "3" };
            }

    As you notice the function still returns array of strings, however the sweetest thing here is that we would pass it as expression:

    Filter reportFilter = new Filter("Fields.MyField", FilterOperator.In, "=RecordCount()");
    Filters.Add(reportFilter);

    Of course the same can be achieved (and is meant to be used) through the parameters collections (multivalue param) and the "Edit Filters" dialog (see screenshot). Now if you take a look what the designer has rendered for us, you would notice the pattern :)

    this.Filters.AddRange(new Telerik.Reporting.Data.Filter[] {
                new Telerik.Reporting.Data.Filter("=Fields.MyField", ((Telerik.Reporting.Data.FilterOperator)(Telerik.Reporting.Data.FilterOperator.In)), "=Parameters.Parameter1")});

    Hope this helps.

    Greetings,
    Steve
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  8. Stephan
    Stephan avatar
    71 posts
    Member since:
    Jul 2007

    Posted 30 Jan 2009 Link to this post

    Hi Steve,

    I was a little impatience, so I also opened a support ticket. But I think my problem had to do with the fact I was setting the value for the option in the constructor.

    But I finally understood how I could accomplice my what I want. So in my report definition I set the value for the parameter to string and marked it as multiple values. Then I attach the string-array to this parameter and everything works as I expected.

    Excellent help. Thanks

  9. Fadi Qatanani
    Fadi Qatanani avatar
    3 posts
    Member since:
    Feb 2010

    Posted 02 Mar 2010 Link to this post

    Hi,
    For the benefits of others, I was trying to filter using IN Operator (something like country_code in (001, 0044, 00971))
    It didn't work because as stated above the string (001,004,00971) needs to be an array.

    I've added the following simple function by right clicking on the report .vb file and selecting view code:

        Public Shared Function Str_to_Array(ByVal str1 As Object) As Array
            Return str1.ToString.Split(",")
        End Function

    Next compile the project.
    Next Open the Filter Dialog box and add:
    =Fields.[Country_Code]         IN                 = Str_to_Array("001,004,00971")

    Notice how we used the function Str_to_Array to easily convert text filter string into array.
    Notice as well the we used double quotes "" to surround the text "001,004,00971"

    Hope this helps.

    Regards
  10. Gary
    Gary avatar
    2 posts
    Member since:
    Aug 2011

    Posted 14 Aug 2011 Link to this post

    Hello,

     

    I am writing to ask a follow up question. I am trying to do something similar but I am getting the following error:

    The expression contains object "TCust" that is not defined in the current context. 

    In this error message, the field named TCust represents a customer number. I am also using C#.

    Attached are 4 pictures:

    1. Screen shot of the report parameters.

    2. Screen shot of the edit Filter screen. I deleted this and create it at runtime.

    3. Screen shot error message  - expression contains object that is not defined.


    4. Screen shot of what the report looks like unfiltered.



    Can anyone tell me what is wrong? Thanks.

    I believe I narrowed down the error to the line where I define the filter. The line is:

       this.Filters.AddRange(new Telerik.Reporting.Data.Filter[] {

           new Telerik.Reporting.Data.Filter("=Fields.TCust", ((Telerik.Reporting.Data.FilterOperator)(Telerik.Reporting.Data.FilterOperator.In)), "=Parameters.Parameter1")});

     

    Note again that the field TCust is a field name representing a customer number. And “Parameter.Parameter1” represents the Report Parameters collection with a parameter named “Parameter1”.

    If I take out the line “this.Filters.AddRange …”and put back in the filter at design time, there is no error and the report opens fine with filtering.  (Of course, if I delete the design time filter, the report shows all data and does not filter which is expected.)  So somewhere I need to put in the line so that the report can be filtered.

     

    To give you some background, I am filtering a report by Customer Number, meaning Customer Number could be a customer number in a list of 13200, 20227,24745, 47990.  This list can change and is not static.  For example, the user may want to see the report filtered by one or more customers. 

     

    I first added a parameter with the following settings:

     

    Name    kept its name as Parameter1

    MultiValue property set to true

    Type set to string  

    Value set to 13200, 20227  (These values are just for testing purposes and this list will be set / overwritten at runtime most likely using an array if I can get that far.)

    When I run my report in code, I get the error “The expression contains object "TCust" that is not defined in the current context.”

    I am programmatically setting a filter using code that follows the line InitializeComponent() in the report object.

     

    InitializeComponent().    

       this.Filters.AddRange(new Telerik.Reporting.Data.Filter[] {

           new Telerik.Reporting.Data.Filter("=Fields.TCust", ((Telerik.Reporting.Data.FilterOperator)(Telerik.Reporting.Data.FilterOperator.In)), "=Parameters.Parameter1")});

    I most likely will replace "=Parameters.Parameter1" with a user defined function such as the RecordCount that takes a one dimensional array made up of customer numbers. I am not sure if this is correct: (this will be done later if I can get this to work)

     

    InitializeComponent().    

     new Telerik.Reporting.Data.Filter("=Fields.TCust", ((Telerik.Reporting.Data.FilterOperator)(Telerik.Reporting.Data.FilterOperator.In)), FilteredArrayOfCustomers});

     

     

    Note that I declare an array in code

    string [] FilterArrayOfCustomers and each element in the array is set to a value elsewhere. I actually am building the array of customer numbers (field is called TCust) on a different form just prior to opening the report.   When this is all done, I will be hiding all the parameters (set visible to false) and passing the values programmatically. I have this working for all the parameters except for the filter and its current parameter Parameters.Parameter1.

     

     

     

     

    Before I continue let me point out that I dynamically set the report’s data source in code so I have a NeedDataSource event which I have copied and pasted below.   Again everything works fine except when I try to programmatically add a filter.  By the way, I also tried moving the “ this.Filters.AddRange(new Telerik.Reporting.Data.Filter[] { new Telerik.Reporting.Data.Filter(….” line to this event but that also didn’t work and returned the same error (expression contains object that is not defined in the current context)

     

     

           

            private void rptContractShipmentsByContractAndCustomer_NeedDataSource(object sender, EventArgs e)

            {

                Telerik.Reporting.Processing.Report report = (Telerik.Reporting.Processing.Report)sender;

                this.sqlReportDS.Parameters["@CompanyID"].Value = report.Parameters["CompanyID"].Value;

                this.sqlReportDS.Parameters["@StartDate"].Value = report.Parameters["StartDate"].Value;

                this.sqlReportDS.Parameters["@EndDate"].Value = report.Parameters["EndDate"].Value;

                this.sqlReportDS.Parameters["@Contract"].Value = report.Parameters["Contract"].Value;

                report.DataSource = this.sqlReportDS;

     

            }

     

    In the above, code, note that report is a report variable referencing the actual report object

    sqlReportDS is a reference to a stored procedure in SQL Server that takes 4 parameters which again is working fine.

    At the end of this event, the report’s DataSource is then set to sqlReportDS which again works without any problems except again I can’t get the report filtered at runtime.

     

    Anyway, does anyone know how to fix this error and where I should set up the filter in code.  Again, I narrowed down error to the line that defines the filter because if I delete the line then the error goes away except then the report isn’t filtered.

     

     

     

     

    Thanks.

     

    Gary

     

  11. Gary
    Gary avatar
    2 posts
    Member since:
    Aug 2011

    Posted 15 Aug 2011 Link to this post

    Hi again,

     

    Disregard my earlier posted message as I found a solution. After trial and error, I found a solution. And I was correct that the error message that I was getting  "The expression contains object "TCust" that is not defined in the current context" was related to the datasource.  For those who are interested in my solution, here is what I did.

     

    1. Instead of setting the datasource at runtime, set the datasource at design time.  Think of it as early binding instead of late binding. 

    2. Delete the NeedDataSource event which is no longer needed.

    3. Add at design time the filter.  In my case it is =Fields!TCust In = Parameters.Parameter1.Value. TCust is the customer number field. Make sure Parameter1 is a sting and is MultiValue.

    4. Remove all default values in the report parameters except leave a value in the parameter1 such as 20227 for an existing customer number. This will be overwritten at runtime. Do not put quotes around the 20227 number.  Not sure why but a syntax error occurs if you do.

    5. For the stored procedure parameters (that was set in the SQLDataSource) and report parameters, set the parameter values in the Item Binding Event.  The ItemBinding Event fires before the NeedDataEvent which again is no longer being called since the report's data source is set at design time.

     

     

    i.e. (here is my code I am using). Note rptContractShipmentsByContractAndCustomer is the name of my report object.

     

            private void rptContractShipmentsByContractAndCustomer_ItemDataBinding(object sender, EventArgs e)

            {

               // NeedDataSource event is not used since the report's datasource is set at design time to include the filter which is already set

                Telerik.Reporting.Processing.Report report = (Telerik.Reporting.Processing.Report)sender;

                this.sqlReportDS.Parameters["@CompanyID"].Value = report.Parameters["CompanyID"].Value;

                this.sqlReportDS.Parameters["@StartDate"].Value = report.Parameters["StartDate"].Value;

                this.sqlReportDS.Parameters["@EndDate"].Value = report.Parameters["EndDate"].Value;

                this.sqlReportDS.Parameters["@Contract"].Value = report.Parameters["Contract"].Value;

                //filter is already set so just set its values

                // example report.Parameters["Parameter1"].Value = new string[] { "13200" };

                report.Parameters["Parameter1"].Value = PubVar.FilteredCustomerList;

               

            }

     

     

    Note the variable PubVar.FilteredCustomerList is an array declared in a class named PubVar as:

     

    public static string[] FilteredCustomerList;

     

    This was my workaround to get the array of strings to the filter by declaring an array in a class so that it could be used in the report.  I initialize the array on another form that opens the report with customer numbers that the user selects.

     

    For testing purposes, I am setting parameter1 to a string array with one element 13200.   I later changed it to point to my string array FiltercustomerList.

     

    Now the report opens filtered as expected and is great.

     

    So, I answered my own question.

     

    Also, the order of events fired in the report is as follows:

     

    ItemBinding

    NeedDataSource

    ItemBound

     

    Since ItemBinding event occurs first, that is where I set the filtered parameter1 to my PubVar.FilteredCustomerList an array of strings. 

     

    Anyway, I thought I share as I am not sure if anyone could have answered my question except me.  And I was on the right track. In addition, I guess it is always better to declare at design time a filter rather than programmatically especially if the datasource isn't known.  At least in this manner, there won't be any syntax or runtime errors.

     

    Other solution I was considering were:

     

    1. Using dynamic SQl (see article http://www.sommarsko.se/dynamic_sql.html for more information) to build the filter in statement.

    2. Using a stored procedure that has a table valued parameter. However, passing a table valued parameter to a stored procedure causes problems when I set up the Telerik report because it returns an error stating the table valued parameter is null.  I am trying to pass a list (of a specified table type) (simply a list of customer numbers in a list) to a stored procedure. I was able to get it to work in transact-SQL by manually adding data and executing the stored procedure.  However, when setting up the Telerik report it didn't work when I set up the SQlDatasource. So, I dropped this idea and used filtering in the report which is an easier and more efficient solution.

    Thanks.

    Gary

  12. Shane
    Shane avatar
    1 posts
    Member since:
    Mar 2013

    Posted 08 Aug 2015 in reply to Gary Link to this post

    If you are using the stand alone report designer you can use the array function

     Example

    array("1","2","3")

     

     

  13. sheelu
    sheelu avatar
    1 posts
    Member since:
    Feb 2016

    Posted 29 Mar in reply to Steve Link to this post

    I want to replace multiple codes of variable gender with their values, ex. 1; male, 2; females 3; Trans gender, 4; nonne

    Can you tell me how to write syntax for it. 

    Thanks,

    SB

  14. Stef
    Admin
    Stef avatar
    3047 posts

    Posted 31 Mar Link to this post

    Hello SB,

    Below is a quote from my reply in your other thread on the same question:
    "Please check the Using Report Parameters in Expressions help article. To get the DisplayMember of a report parameter, you can use an expression like Parameters.ParamName.Label, where multivalue report parameters are evaluated as arrays of objects and can be handled by text functions like Join. "


    I hope this information is helpful.

    Regards,
    Stef
    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