Blue Mountain Regulatory Asset Manager’s (Blue Mountain RAM) tool for creating queries is the Query Designer. The Query Designer organizes design activities across 4 tabs: Columns Headers, Criteria, Sorting, and Flags. In this blog, I will highlight the powers of the Query Designer’s Columns Headers tab:
- Column width and reduction of side-to-side scrolling
- Column aliases and clarity of what data is displayed
- Which columns QuickFind will be able to search per query
- Which records are included in the query results (filtering by column)
Column Width and Reduction of Side-To-Side Scrolling
The Columns Headers tab includes a Column Width setting. As each column is added, it starts with a default width of 120 pixels, however, some columns need less width, while others need more.
It is easier to read a query when the columns are sized according to the data they hold. More information is initially view-able in a larger space and users have less resizing or scrolling to do when viewing the query.
To adjust column width settings, double-click a row to open its Edit Columns window within the Query Designer window. Edit the Column Width (pixels) setting as you wish. NOTE: A smaller number narrows the column.
I have changed the column width settings in this query to suit the data.
The results are…
Column Aliases and Clarity of What Data Is Displayed
It is easier to understand a query when the information is clearly labeled. For example, if you want a query to show both work status and asset status, you need to ensure that the viewer can identify which status column is for the work versus the asset. In Query Designer, the Field ID number identifies which status column is which:
- Field IDs that start with “4” are asset fields
- Field IDs that start with “5” are event fields
- Field IDs that start with “6” are work fields
NOTE: You can look in Blue Mountain RAM’s System Configuration at Form Setup to see the Field ID pattern for specific types of assets, events, work, or for other record types.
Since users do not have this information, you can clarify each status column for the users. To do so, double-click a row within the Query Designer’s Columns Headers tab to open its Edit Columns window. Edit Query Column Alias to clarify each status column for the user as: Asset Status and Work Status.
The results are…
Which Columns QuickFind Will Be Able To Search Per Query
When you add a column, its inclusion is not only visible to users, but also to the QuickFind tool. QuickFind’s behavior is to only offer the columns included in the query design for use with its criteria statement builder. Therefore, the choice of columns has a big impact on users being able to search for information. Additionally, many of Blue Mountain RAM’s features act upon records found by searching queries with QuickFind, so the choice of columns in a query is very important as well! Some of the features that depend upon query searching for maximum efficiency include:
- Batch Updates (Fields, Statuses, Signatures, Rules)
- Batch Reporting
- Batch Logbook Entries
- Batch Archiving or Deleting
Which Records Are Included in the Query Results (Filtering by Column)
When you add a column from a record type (asset, event, work, etc.) that was not previously represented in the query design, its inclusion becomes a filter. It filters out all records that cannot contribute data from that column/record type. Here are two examples:
Example #1: An “All Assets” query design only contains columns from asset records. Additionally, there are no criteria specified on the Criteria Tab. Every asset is included.
Modification: The “All Assets” query is modified to include the event’s “Event Name” column. It is still the case that no criteria are specified on the Criteria Tab.
Result: Only those assets that have at least one event – so that they can contribute Event Name data – are included. Assets without events are excluded. The “All Assets” query is no longer “All Assets.” It is “All Assets with an Event.”
Example #2: An “All Work” query design only contains columns from work records. Additionally, there are no criteria specified on the Criteria Tab. All work records are included.
Modification: The “All Work” query is modified to include the event’s “Event Name” column. It is still the case that no criteria are specified on the Criteria Tab.
Result: Only those work records that have a parent event – so they can contribute Event Name data – are included. Work without a parent event is excluded. On Demand work does not have a parent event, so all On Demand work records are excluded. The “All Work” query is no longer “All Work.” It is “All Event-Scheduled Work.”
When you have questions, consult the Blue Mountain RAM User and Administrator Manuals. If necessary, please contact our friendly Support Technicians at email@example.com with questions. Also, please enjoy calling and speaking with your Regional Sales Manager (RSM) at 800-982-2388 if you are interested in Blue Mountain RAM ideas, training, or services.