Customizing rulebase and resultset grids for grouping, filtering, sorting, searching and displaying

Applies to
ApexSQL Enforce

Summary
This article describes how to group, sort and filter the rules and the results in the ApexSQL Enforce

Description

When ApexSQL Enforce is started, after the rulebase is loaded, the main window is divided in two parts. On the left side is the Rulebases tree view Object Explorer pane and on the right, is the Rules list, where rules, by default, are grouped by the category column.

Rulebase tree

In the Rulebase tree, the rules are grouped by nodes, by the two types of targets that can be run against; the Database node and the SQL script node.

A Database, in this context, means that the rulebase can be pointed to and run against a live database, via the connection dialog. SQL Script, means that the rulebase can only be run against a SQL script.

Under the Database node, there are several sub-nodes (the child nodes: Programmability, Security, Services broker, Storage, Event notification, Synonym, Table and View), that are stored as nested collections. The number of rules, for that node, are presented after the name of the node. For example, “Table (7)” means that there are 7 rules for the Table data type.

The SQL script node is divided into two sub-notes; SQL script and Batch. The Batch sub-node contains several other sub-nodes, which are divided on DDL statements, DML statements, Execute statements, Assign to variable, Begin transaction statement, Declaration, Error in script, SET statement and Try catch statement sub-nodes.

To run all rules from one node, in the Rulebase treeview Object Explorer pane, right click on the node and from the context menu choose the Run all [node] rules … command.


When a rules node is selected in the Rulebases tree view Object Explorer pane, all existing rules of the same type of the node, in our case ‘Create/Alter procedure statement’ will be shown in the Rules grid. In this way, we can use the treeview as a filter for our grid

Defaults

By default, the Rules grid is grouped by Category and sorted by Severity in descending order.

The Resultset grid is grouped by Result and sorted by Severity in descending way.

Grouping

By default, all listed rules in the Rules grid, are grouped by Category E.g. Naming conventions. To group the listed rules by any other column, drag and drop that column’s header into the group panel.

Another way to do this is to right click a column header and from the context menu, choose the Group by this column command.

When the grouping is done by columns, these columns automatically will be hidden from the Rules grid as the Category names can instead be seen in the grouping headers.

Grouping can be done by multiple columns at the same time. To do that, hold the Ctrl keyboard key, and drag a column header, and drop it in the group panel.

There are numerous combinations of this type of grouping, and every time when column order for grouping is changed, the results will be different:

To exclude a column from the grouping panel, click on the column header, drag and drop it into the Rules grid. Or right click on the column header in the grouping panel, and from the context menu choose the Ungroup command.

To clear all grouping, not column by column, but all at once, go to the View tab, and under the Grouping tab click the Clear all command. This action will leave the group panel completely empty.

Sorting

Sorting can be done in two ways:

  • By clicking on the column header
    • Click once on the column header to sort the rules in the selected column in ascending order or again for descending order
  • Right click on the selected column header, and from the context menu, click the command Sort ascending, if you want to sorting order be ascending, or the Sort descending option, if you want the sort order to be descending

No matter which way is chosen, all the rules, in the rest of the columns, will be sorted depending on the order chosen in the selected column.

To clear all sorting, restore it to the default sorting by Severity, either in the Rules or the Resultset grid, click the right click on the column header and from the context menu choose the Clear sorting command.

Displaying results

After the checked rules have been run, the Resultset grid will be shown.

Click the Options command in the Tools section of the main ribbon, to open the Options dialog. Under the Results tab, check all results type that will be shown in the Resultset grid.

Even if all results are rendered after a rulebase is processed, they can be filtered out using the column filter. To see all results, except rules that passed, click the filter icon in the right corner of the Results column header, and from the value list check all other result types but the Pass.

After the filter form is closed, every result type will be present in the Resultset grid, but the ones that passed.

Filtering

Every column in the Resultset grid has its own filter, located on the right side of the column header. Using the column filters, for example, can help in highlighting failed rules easier by hiding all passed rules. Depending on a column, every filter has its own list of objects. Only the checked objects will be shown in the Resultset grid.

One more way to filter the results is to add the Auto filter row to the Resultset grid. Right click on any column header and from the context menu, choose the Show auto filter row command.

This action will add one more row to each column in the Results grid.

Click on the icon on the left in the filter field, will open the list of all possible filters.

When the type of the filter is chosen, type the criteria for filtering. This filter will automatically be applied to that column, and in the grid, will be shown only the results of set filter.

Q: How can I filter the results, in order to see only failed rules with high severity?

A: To see only failed rules with high severity, first click on the Result column filter and from the list check only the Fail type.

Then, click on the Severity column filter and from the filter list check the High severity.

Q: How can I group results by result type and sort them by severity?

A: Drag and drop the Result column header into the group panel. Then click the severity column header once if you want to sort the list in ascending way, or the second time if you want to sort the list in descending way.

Q: How can I search through the results and the rules?

A: To search the entire grid (the results or rules grid), right click on any column header and from the context menu choose the Show find panel command. Everything you type in the find panel will be highlighted through the entire grid.

Q: Will all of my changes be saved and persisted in between sessions?

A: All changes made to grouping and sorting will be saved between sessions.

Q: Can I choose which columns will be present in the Rules or Resultset grid?

A: Yes, you can choose which columns will be present in any of the grid. Right click on the columns header and from the context menu choose the Column chooser command. This will open the Columns window, and by simple drag and drop, from the window or to the window, you can choose which columns will be or will not be present in the Rules or the Resultset grid.