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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

Will this normal excel filter (Not the MDS filter option) help resolve your problem?

You can select muptiple items with this regular excel filter.

Answer 8

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.

Answer 9

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

Answer 10

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