Advanced Object Filtering

This article describes how to use the "Filter" textbox within Tabular Editor - an incredibly useful feature when navigating complex models.

Filtering Mode

As of 2.7.4, Tabular Editor now lets you decide how the filter should apply to objects in the hierarchy, and how search results are displayed. This is controlled using the three right-most toolbar buttons next to the Filter button:

image

  • image Hierarchical by parent: The search will apply to parent objects, that is Tables and Display Folders (if those are enabled). All child items will be displayed, when a parent item matches the search criteria.
  • image Hierarchical by children: The search will apply to child objects, that is Measures, Columns, Hierarchies, etc. Parent objects will only be displayed if they have at least one child object matching the search criteria.
  • image Flat: The search will apply to all objects, and results will be displayed in a flat list. Objects that contain child items will still display these in a hierarchical manner.

Type anything into the Filter textbox and hit [Enter] to do a simple case-insensitive search within object names. For example, typing "sales" in the Filter textbox, using the "By Parent" filtering mode, will produce the following results:

image

Expanding any of the tables will reveal all measures, columns, hierarchies and partitions of the table. If we change the filtering mode to "By Child", the results will look like this:

image

Notice how the "Employee" table now appears in the list, since it has a couple of child items (columns in this case), that contain the word "sales".

When typing in a string in the Filter textbox, you can use the wildcard ? to denote any single character, and * to denote any sequence of characters (zero or more). So typing *sales* would produce exactly the same results as shown above, however typing sales* will only show objects whose name starts with the word "sales" (again, this is case-insensitive).

Searching for sales* by parent:

image

Searching for sales* by child:

image

Flat search for sales* (toggle info columns [Ctrl]+[F1] to show detailed information about each object):

image

Wildcards can be placed anywhere in the string, and you can include as many as you need. If that's not complex enough, read on...

You can also use Dynamic LINQ to search for objects, which is the same thing you do when creating Best Practice Analyzer rules. To enable Dynamic LINQ mode in the filter box, simply put a : (colon) in front of your search string. For example, to view all objects whose name end with "Key" (case-sensitive) write:

:Name.EndsWith("Key")

...and hit [Enter]. In "Flat" filtering mode, the result looks like this:

image

For case-insensitive search in Dynamic LINQ, you can either convert the input string using something like:

:Name.ToUpper().EndsWith("KEY")

or you can supply the StringComparison argument, like:

:Name.EndsWith("Key", StringComparison.InvariantCultureIgnoreCase)

You are not restricted to searching within the names of objects. Dynamic LINQ search strings can be as complex as you like, to evaluate any property (as well as sub-properties) of an object. So if you want to find all objects having an expression that contains the word "TODO", you would use the following search filter:

:Expression.ToUpper().Contains("TODO")

As another example, the following will display all hidden measures in the model that are not referenced by anything else:

:ObjectType="Measure" and (IsHidden or Table.IsHidden) and ReferencedBy.Count=0

You can also use Regular Expressions. The following will find all columns whose name contains the word "Number" OR "Amount":

:ObjectType="Column" and RegEx.IsMatch(Name,"(Number)|(Amount)")

Note, that the display options (the toolbar buttons directly above the tree), may affect the results when using "By Parent" and "By Child" filtering mode. For example, the above LINQ filter only returns columns, but if your display options are currently set to not show columns, nothing will be displayed.