none
Filter Data before Loading (MDS Add-in for Excel) Does Not Work

    Question

  • I’m working with the Excel Plugin; how do I filter on multiple items form a single column? For example I have a Month column and only want to filter for the months of Nov and Dec? Within the Filter dialog box I have tried to use two separate row filters with no luck; so my thinking is that it may need some sort of syntax within a single row?

    I have read through most if not all the tech notes starting with the following ascending to the notes about Filter Operators (Master Data Services). Within this note is a description of the Control Name. Both the “Is Like” and “Is Not Like” appear to be good candidates for this type of filtering; but what I’m not seeing is the syntax to use. The provided links for both are not relevant as far as I can see; I have only been able to get the wild card % to work?

    Filter Data before Loading (MDS Add-in for Excel) http://technet.microsoft.com/en-us/library/hh479634(v=sql.110).aspx

    Filter Dialog Box (MDS Add-in for Excel) http://technet.microsoft.com/en-us/library/hh479638.aspx

    Filter Operators (Master Data Services) http://technet.microsoft.com/en-us/library/gg471525.aspx


    • Edited by KarlRexx Thursday, January 24, 2013 3:57 AM Filter Data before Loading (MDS Add-in for Excel) Does Not Work
    Thursday, January 24, 2013 12:16 AM

Answers

  • Hi

    I found a workaround.

    I don't know why ms has disabled some operators :

    Doing a reflector on the code file from excel add-in : Microsoft/MasterDataServices.EXcelAddInCore.Filter shows the list ofpossible operators:

     public enum SearchTermOperator
        {
          [Description("")] NotSpecified,
          [Description("=")] IsEqual,
          [Description("<>")] IsNotEqual,
          [Description("LIKE")] Like,
          [Description("NOT LIKE")] NotLike,
          [Description(">")] GreaterThan,
          [Description("<")] LessThan,
          [Description(">=")] GreaterThanOrEqual,
          [Description("<=")] LessThanOrEqual,
          [Description("MATCH")] Matches,
          [Description("NOT MATCH")] NotMatches,
          [Description("REGEX")] ContainsPattern,
          [Description("NOT REGEX")] NotContainsPattern,
          [Description("IS NULL")] IsNull,
          [Description("IS NOT NULL")] IsNotNull,
          [Description("IN")] In,
          [Description("NOT IN")] NotIn,
          [Description("DIRECT DECENDANTS")] DirectDecendants,
          [Description("ALL DECENDANTS")] AllDecendants,
        }

    then , in Microsoft.MasterDataServices.ExcelAddInCore.Filter.dll, ms is disabling IN, NOT IN, MATCHES, NOT MATCHES, LIKE, NOT LIKE

     public Collection<SearchTermDataSet.SearchTermOperator> DisallowedOperators
        {
          get
          {
            if (this.excludedOperators == null)
            {
              this.excludedOperators = new Collection<SearchTermDataSet.SearchTermOperator>();
              this.excludedOperators.Add(SearchTermDataSet.SearchTermOperator.NotSpecified);
              this.excludedOperators.Add(SearchTermDataSet.SearchTermOperator.In);
              this.excludedOperators.Add(SearchTermDataSet.SearchTermOperator.NotIn);
            }
            return this.excludedOperators;
          }
        }


    private bool IsOperatorAllowed(SearchTermDataSet.SearchTermOperator operatorCode, bool useList, AttributeDataType dataType, Collection<SearchTermDataSet.SearchTermOperator> allowedOperators)
        {
          bool flag = !this.DisallowedOperators.Contains(operatorCode) && (allowedOperators == null || allowedOperators.Contains(operatorCode));
          if (operatorCode == SearchTermDataSet.SearchTermOperator.ContainsPattern || operatorCode == SearchTermDataSet.SearchTermOperator.NotContainsPattern || (operatorCode == SearchTermDataSet.SearchTermOperator.Matches || operatorCode == SearchTermDataSet.SearchTermOperator.NotMatches) || (operatorCode == SearchTermDataSet.SearchTermOperator.Like || operatorCode == SearchTermDataSet.SearchTermOperator.NotLike))
            flag = flag && !useList && dataType != AttributeDataType.DateTime && dataType != AttributeDataType.Number;
          else if (operatorCode == SearchTermDataSet.SearchTermOperator.GreaterThan || operatorCode == SearchTermDataSet.SearchTermOperator.GreaterThanOrEqual || (operatorCode == SearchTermDataSet.SearchTermOperator.LessThan || operatorCode == SearchTermDataSet.SearchTermOperator.LessThanOrEqual))
            flag = flag && !useList;
          return flag;
        }

    so the workaround is:

    record a query (any query on your entity, nevermind)

    then in "manage queries", export this newly created query and save as test.mdsqx , by example,in the folder of your choice

    then, with your prefered text editor, edit this saved file

    in this xml file, search for the first occurence of <FilterText>

    you will then see your query

    by example : 

    <FilterText>[Name] IS NOT NULL</FilterText>


    and then you just have to replace with your own query :

    <FilterText>[Name] IN ('Nov','Dec')</FilterText>

    or by example 

    <FilterText>[Name] LIKE 'A%'</FilterText>

    or any other query

    then save this updated query , and import it in MDS from "manage query" in excel add-in

    then run this query --> DONE!

    warning: you won't be able to edit this query in MDS add-in Filter Box, as , although it's filtering good, it will throw an error

    "DataGridViewComboBoxCell" value is not valid



    Regards,


    Xavier Averbouch
    Microsoft Community Contributor
    SOAT
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".





    Saturday, February 9, 2013 7:33 AM
    Moderator
  • Hi again,

    I've made a small tool for updating queries with unallowed operators

    very easy to use:

    Load your already generated query

    then choose a new Operator and edit the criteria

    and then save the updated query

    source code and binaries are available (for free) on my blog:

    http://averbouch.biz/?p=440

    Link updated : http://legacy.averbouch.biz/fr/master-data-services-excel-add-in-query-updater/


    Regards,


    Xavier Averbouch
    Microsoft Community Contributor
    SOAT
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".


    Saturday, February 9, 2013 9:55 AM
    Moderator

All replies

  • The list of filter operators in the Excel Add-in depends on  the type of attribute. If it is a domain attribute you would only get 4 operators as options.

    If your month attribute is not a domain attribute, try selecting a Contains Pattern filter operator and use the following regular expression in the criteria Nov|Dec.

    If you month attribute is a domain attribute, the Contains Pattern filter operator will note be in the list of available operators.

    Thursday, January 24, 2013 8:54 AM
  • Using the Month Attribute I get 9 operators, none of which are Contains Pattern filter operator. In SQL I can write a query with the syntax of WHERE (Month IN (‘NOV’,’DEC’)). In the Excel workbook after applying the filter option I just simply select NOV, and DEC from the dropdown selection menu… I’m still confused when using MDS Add-in for Excel filter on how to filter an Attribute for multiple items. The addin works fine for single items like DEC, but not for multiple items within the same attribute such as NOV and DEC.

    **9 Operators**

    Is Equal To

    Is Not Equal To

    Is Like

    Is Not Like

    Is Greater Than

    Is Greater Than or Equal To

    Is Less Than

    Is Less Than or Equal To

    Is Null

    Is Not Null

    Thursday, February 7, 2013 12:42 AM
  • I have just noticed something that I've never noticed before, the list of operators in the Excel Add-in is different from the list of operators in the MDS Administration website. In the MDS Administration in addition to the the 9 operators you mention, there are 5 more:

    Matches

    Does not match

    Contains pattern

    Does not contain pattern

    Starts with

    It is the 3rd in the list that I was suggesting you to use.

    Anybody else can shed some light into why the MDS Administration website has more filter operators for non-domain attributes that the the Excel Add-in, this is really confusing.

    Thursday, February 7, 2013 8:23 AM
  • Hi

    another interesting article on this topic (excel add-in):

    http://www.mssqltips.com/sqlservertip/2748/lets-explore-excel-addin-of-master-data-services-of-sql-server-2012--part-2/


    Regards,


    Xavier Averbouch
    Microsoft Community Contributor
    SOAT
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".


    Thursday, February 7, 2013 4:27 PM
    Moderator
  • Hi,

    "Contains pattern" and "Does not contain pattern" are based on regular expression.

    (http://social.msdn.microsoft.com/Forums/en-US/sqlmds/thread/a7813933-5518-43cc-a3cc-2962b44c245c/)

    And I think that Excel is not natively supporting Regex

    (kind of extension :http://mathfest.blogspot.fr/2010/03/regular-expressions-in-excel.html)

    regex in sql server 2012:

    http://msdn.microsoft.com/en-us/library/ms174214.aspx


    Regards,


    Xavier Averbouch
    Microsoft Community Contributor
    SOAT
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".


    Thursday, February 7, 2013 4:31 PM
    Moderator
  • Hi,

    I am aware that "Contains pattern" and "Does not contain pattern" used regular expressions. "Nov|Dec" is a regular expression, I think I got it right, it does work on the MDS Administrator.

    What I find confusing is that those filter operators are not available in the Excel Add-in, and add-in that behaves more or less as the explorer in the MDS Administration website does. I know Excel might not support regular expressions but all the Excel Add-in is doing is calling a web services that does support regular expressions. Doesn't it? Have I missed something?

    I just do not think you can use regular expressions with Excel, none seem to work. I just do not think that what Karl needs to do can done with Excel. Of course I could have missed something, but I cannot see how you can filter only November and December without getting unnecessary data from the server.

    The team developing the Excel Add-in should really into this.

    Kind regards,

    M


    Thursday, February 7, 2013 5:38 PM
  • Will this normal excel filter (Not the MDS filter option) help resolve your problem?

    You can select muptiple items with this regular excel filter.

    Friday, February 8, 2013 4:11 AM
  • Nope... There are way too many records in the dataset; the preference it to be able to filter and load only the records needed spanning
    two or three months. Most of the documents read about MDS suggests that the MDS Add-In Filter be used for this reason; but none demonstrate the syntax to be applied for such filtering.

    Friday, February 8, 2013 4:12 PM
  • Hi

    I found a workaround.

    I don't know why ms has disabled some operators :

    Doing a reflector on the code file from excel add-in : Microsoft/MasterDataServices.EXcelAddInCore.Filter shows the list ofpossible operators:

     public enum SearchTermOperator
        {
          [Description("")] NotSpecified,
          [Description("=")] IsEqual,
          [Description("<>")] IsNotEqual,
          [Description("LIKE")] Like,
          [Description("NOT LIKE")] NotLike,
          [Description(">")] GreaterThan,
          [Description("<")] LessThan,
          [Description(">=")] GreaterThanOrEqual,
          [Description("<=")] LessThanOrEqual,
          [Description("MATCH")] Matches,
          [Description("NOT MATCH")] NotMatches,
          [Description("REGEX")] ContainsPattern,
          [Description("NOT REGEX")] NotContainsPattern,
          [Description("IS NULL")] IsNull,
          [Description("IS NOT NULL")] IsNotNull,
          [Description("IN")] In,
          [Description("NOT IN")] NotIn,
          [Description("DIRECT DECENDANTS")] DirectDecendants,
          [Description("ALL DECENDANTS")] AllDecendants,
        }

    then , in Microsoft.MasterDataServices.ExcelAddInCore.Filter.dll, ms is disabling IN, NOT IN, MATCHES, NOT MATCHES, LIKE, NOT LIKE

     public Collection<SearchTermDataSet.SearchTermOperator> DisallowedOperators
        {
          get
          {
            if (this.excludedOperators == null)
            {
              this.excludedOperators = new Collection<SearchTermDataSet.SearchTermOperator>();
              this.excludedOperators.Add(SearchTermDataSet.SearchTermOperator.NotSpecified);
              this.excludedOperators.Add(SearchTermDataSet.SearchTermOperator.In);
              this.excludedOperators.Add(SearchTermDataSet.SearchTermOperator.NotIn);
            }
            return this.excludedOperators;
          }
        }


    private bool IsOperatorAllowed(SearchTermDataSet.SearchTermOperator operatorCode, bool useList, AttributeDataType dataType, Collection<SearchTermDataSet.SearchTermOperator> allowedOperators)
        {
          bool flag = !this.DisallowedOperators.Contains(operatorCode) && (allowedOperators == null || allowedOperators.Contains(operatorCode));
          if (operatorCode == SearchTermDataSet.SearchTermOperator.ContainsPattern || operatorCode == SearchTermDataSet.SearchTermOperator.NotContainsPattern || (operatorCode == SearchTermDataSet.SearchTermOperator.Matches || operatorCode == SearchTermDataSet.SearchTermOperator.NotMatches) || (operatorCode == SearchTermDataSet.SearchTermOperator.Like || operatorCode == SearchTermDataSet.SearchTermOperator.NotLike))
            flag = flag && !useList && dataType != AttributeDataType.DateTime && dataType != AttributeDataType.Number;
          else if (operatorCode == SearchTermDataSet.SearchTermOperator.GreaterThan || operatorCode == SearchTermDataSet.SearchTermOperator.GreaterThanOrEqual || (operatorCode == SearchTermDataSet.SearchTermOperator.LessThan || operatorCode == SearchTermDataSet.SearchTermOperator.LessThanOrEqual))
            flag = flag && !useList;
          return flag;
        }

    so the workaround is:

    record a query (any query on your entity, nevermind)

    then in "manage queries", export this newly created query and save as test.mdsqx , by example,in the folder of your choice

    then, with your prefered text editor, edit this saved file

    in this xml file, search for the first occurence of <FilterText>

    you will then see your query

    by example : 

    <FilterText>[Name] IS NOT NULL</FilterText>


    and then you just have to replace with your own query :

    <FilterText>[Name] IN ('Nov','Dec')</FilterText>

    or by example 

    <FilterText>[Name] LIKE 'A%'</FilterText>

    or any other query

    then save this updated query , and import it in MDS from "manage query" in excel add-in

    then run this query --> DONE!

    warning: you won't be able to edit this query in MDS add-in Filter Box, as , although it's filtering good, it will throw an error

    "DataGridViewComboBoxCell" value is not valid



    Regards,


    Xavier Averbouch
    Microsoft Community Contributor
    SOAT
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".





    Saturday, February 9, 2013 7:33 AM
    Moderator
  • Hi again,

    I've made a small tool for updating queries with unallowed operators

    very easy to use:

    Load your already generated query

    then choose a new Operator and edit the criteria

    and then save the updated query

    source code and binaries are available (for free) on my blog:

    http://averbouch.biz/?p=440

    Link updated : http://legacy.averbouch.biz/fr/master-data-services-excel-add-in-query-updater/


    Regards,


    Xavier Averbouch
    Microsoft Community Contributor
    SOAT
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".


    Saturday, February 9, 2013 9:55 AM
    Moderator