Overview
Usually, when building a custom report, you are only interested in seeing a small portion of the records in a table. For example, when creating an open work order report, you don't want to see closed work orders on your report. Filtering is the concept of allowing or disallowing certain records to be viewed. To filter, you specify what data must be contained in a field to allow the record into the query. The records that meet the filter conditions will be brought into the query (added to the smaller table). There are two ways to build a filter:
1. Simple Filter
Conditions determine the way in which filters interact with each other and the report as a whole. In order to create a simple filter it is important to understand the difference between the “AND” and the “OR” operators. These two operators are used to configure the CMMS Custom Filters:
- The “And” operator indicates that all specified conditions must be met.
- The “Or” operator indicates that any specified conditions can be met.
Note: Both the “AND” and “OR” operators cannot be used in the same simple filter. If you want to use AND and OR, you must use a complex filter.
To summarize, the “And” operator is restrictive and the “Or” operator is flexible.
1. To add a filter, click the add filter icon:
2. For example, if we want to create a work order report that only shows open work orders, we need to create the following filter:
3. If you want to refine the data further, you can add more filters as needed. In this example, the report is looking for open, corrective work orders of high, or highest priority:
2. Complex Filters
1. Before we look at complex filters, please note that you cannot have both simple and complex filtering. It must be one or the other. Complex filtering is where any MySQL experience comes in handy. You can use some of the built in functions such as:
- SUM() - Will add the values in that group together (if numeric)
- AVG() - Will calculate an average of the grouped values (if numeric)
- MIN() - Will return the lowest value in alpha-numeric order
- MAX() - Will return the highest value in alpha-numeric order
- COUNT() - Will return the number of records that are grouped in that row
For additional functionality, you can open the expression editor and use your own MySQL code to filter the data as needed. For example, lets say we only want to see overdue work orders in a report. We need to create a filter that only shows work orders where the difference in days between the suggested completion date of the work order, and the present time, is greater than zero. The code looks like this:
timestampdiff (day, C[Work Order 1 - Suggested Completion Date] , now() ) > 0
2. Let's combine this complex filter with the simple filter from above. Combining them will show overdue open corrective work orders of the high or highest priority.
Note: The maintenance type and priority tables have already been joined to the work order table, so we can see those tables in our Advanced Filter window:
3. All tables joined in the report are listed under the Pick a table section. These can be selected to pull variables from the Click to paste column section:
4. Create the advanced filter for overdue open corrective work orders of the high or highest priority as follows:
Note:- In this case we have used the LIKE operator so the query will find the word "High" in both high and highest.