Custom Queries Editor

The Custom Queries Editor lets you build a query by hand. You choose a base view, the fields you need, and optional filters, and IMS returns the matching data in a grid. You can then sort and filter the results, export them to Excel, or use the query in a Dashboard. You only see data that your IMS roles and permissions already allow.

Note

You can also create a query by typing a question in plain language. Click Ask & Build in the Custom Reports Main Menu. For details and availability, see AI Query Builder.

Start a Query

You start from the Custom reports Main Menu. You can create a new query or edit one that you own.

Create a New Query

To create a new query:

1. Click the Custom reports Main Menu.

2. Select Queries from the drop-down. For more about the list that opens, see Listing all Queries.

3. Click the Plus button. The New Query page opens. It stays empty until you choose a base view.

Next, build the query as described in the following sections.

Edit an Existing Query

You can edit a query only if you own it. To edit a query:

1. Find the query in the list.

2. Click the Edit button next to the query.

Then change the base view, fields, filters, and settings as described in the following sections.

Create a Personal Folder

You can create a personal folder to store your queries in. To create a folder:

1. Click the green Plus button next to My queries.

2. In the Folder name field, enter a name for the folder.

3. Click Save.

Understand the New Query Page

The New Query page is where you define a query. Before you run the query, these controls are available:

After you click Run, these controls appear above the grid:

Choose a Base View

The base view is the starting point of a query. It is either a database table that you build on by adding related objects, or a ready-made view with the data you are likely to need. Ready-made views start with "vw". The base view decides what one row in your results represents. Start with the object you want one row for. For example, to list Equipment, choose Equipment. To list CMLs, choose CML. For more about how base views and related tables work, see How Query Data Is Organized.

To choose a base view:

1. Click the search button next to the Search a base view box. The Select base view dialog opens.

Note

You can also type directly in the Search a base view box to filter the list of base views.

2. Scroll through the list or type part of a name in the Search by name... field.

3. Select the base view.

4. Click OK.

After you choose a base view, IMS sometimes preselects some of its fields. For example, the Corrosion Loop base view starts with the Corrosion Loop Name and Name fields selected.

Note

If you can't find the view you need, Request Support.

Select Fields

The fields you select become the columns in your query results. You choose them in the Fields panel.

Add Fields

To add fields to your query:

1. Click Fields. The Fields panel opens, with the available fields on the left and your selected fields on the right.

2. Find a field by typing in the Search fields box, or by expanding the arrow next to the base view name.

3. Click the field. A selected field shows a check mark and appears in the list on the right.

An icon next to each field shows its type. For example:

• A text icon marks a text field.

• A toggle icon marks a field that shows true or false.

• A calendar icon marks a date field.

• A hash (#) icon marks a number field.

Customize the Selected Fields

Hover over a field in the list on the right to show its buttons. You can then customize each field:

• Drag the six dots next to a field to change the order of the columns.

• Click a field name, and then type a new name to rename it. The new name becomes the column title in the results.

• Click the circle next to a field, and select a color. The color is applied to the column when you click Run.

• Click the eye button to hide the column, or to show it again.

• Click the sort button to sort the results on that field. Each click switches between ascending and descending order.

Note

When you sort on a field, a chip such as "1 Desc" appears next to it. The number sets the sort order, and the label shows the direction. For example, if Description is "1 Desc" and Name is "2 Asc", the results are sorted on Description in descending order first, and then on Name in ascending order. Click the x button on the chip to clear the sort.

• Click the plus button to add a filter for that field. See Add a Filter below.

• Click the trash bin button to remove the field (column) from the query.

Extend the Base View with Related Tables

A base view can be extended with related tables and views. They appear as entities under the base view, and the number next to an entity shows how many of its fields are selected. This example uses the Equipment base view, which starts with Equipment ID, FLOC, Name, and CMMS Type selected. It builds a list of equipment with their components, site, plant, and unit.

To extend the base view:

1. Click Fields. The Fields panel opens.

2. Click the small arrow next to Equipment to collapse its fields. The entities under it, such as Parent Locations and Equipment Group, are now visible.

3. To remove an entity you don't need, point to it and click the small x button next to it. For example, remove CMMS Type. Its field also leaves the list of selected fields.

4. In the drop-down at the bottom of the list, select "Component/Circuit (Collection)". You can type to search for it.

5. Click the Plus button next to the drop-down.

6. Click the small arrow to the left of Component/Circuit (Collection) to expand it, and then click Component. The field appears in the list of selected fields.

Note

Selecting the check box next to an entity selects all of its fields. In this example you selected only Component, so Component/Circuit (Collection) shows a 1.

7. Click the small arrow to the left of Parent Locations to expand it, and then select Plant, Site, and Unit. FLOC is already selected, so you don't need to select it again. The number next to Parent Locations changes to 4, because four of its fields are now selected. You can also see the newly added fields on the list of selected fields on the right.

Note

To rearrange the list of selected fields, drag the six dots next to a field to put the columns in the order you want.

8. Point to Unit in the list of selected fields, and click the plus button to add a filter on it. The number next to Filters goes up by one, but the Filters panel doesn't open.

9. Click Filters to open the Fields panel and set the operator and value for the filter.

10. Click Run to see the results.

See the final result in the image below.

For how parent, child, and collection relations differ, see How Query Data Is Organized.

Pivot Data

Pivoting moves data from columns to rows. You can pivot data in the Fields panel. To pivot your data:

1. Add the fields you want to your query.

2. Click the pivot button (the sigma icon) at the top right of the list of selected fields. The Columns, Rows, and Values areas appear.

3. Drag each field by its six dots into the Columns, Rows, or Values area.

4. For a field in the Values area, select how to calculate the value in the drop-down next to it, for example "Count".

5. Click Run to see the pivoted results.

To take a field out of an area, click the x button next to it.

Filter the Dataset

Filters are optional. They keep only the rows that match your conditions.

Add a Filter

To add a filter:

1. Click Filters. The Filters panel opens.

2. Click Add Filter.

3. In the Fields (first) drop-down, select the field to filter on.

4. In the Operator (second) drop-down, select how to compare the field, for example "equals" or "contains".

5. Enter or select the value to compare against.

Note

You can also start a filter from the Fields panel. To do so, click the plus button next to a selected field.

The comparison options depend on the field type. For text fields they include, among others:

• "equals" and "not equals".

• "case-insensitive equals" and "case-insensitive does not equal".

• "starts with" and "does not start with".

• "ends with" and "does not end with".

Filter on Dates

A date field has three drop-downs after the field name. To add a date filter:

1. Click Filters, and then click Add Filter.

2. In the first drop-down, select a date field.

3. In the second drop-down, select how to compare the date, for example "before" or "after".

4. In the third drop-down, select a relative date, for example "now", "yesterday", or "7 days ago". To use a specific date instead, select "equals", and then enter the date in the field that appears.

The comparison options for dates include, among others, "before", "after", "year equals", "month number equals", "day number equals", "hour equals", "minute equals", "second equals", and "is null". The relative dates include, among others, "now", "yesterday", "7 days ago", "30 days ahead", "first of this month", and "end of next 12 months".

Match More Than One Value

To match several values for one field, select "in (; separated)" from the comparison drop-down. Then enter the values, separated by semicolons.

Combine Filters with Filter Groups

A filter group holds a set of filters. Use several groups to combine sets of conditions. To add a group:

1. In the Filters panel, click Add Filter group. A new group appears below the existing one.

2. Add filters to the new group.

In a group, the And button keeps rows that match all of the group's filters. Click it to change it to Or, which keeps rows that match at least one filter. Click the button between two groups to change how the groups combine in the same way.

Hover over a filter group to show two more buttons in its header. Click the copy button to duplicate the group, or the trash bin button to delete it. The trash bin button is unavailable when only one group exists.

Remove a Filter

To remove a filter, hover over it and click the trash bin button next to it.

Run the Query and Review the Results

The results don't update while you build the query. Click Run to see them in the grid. You can then narrow the results, filter and sort the columns, pin rows, and export the data.

Narrow the Results by Site, Plant, and Unit

After you click Run, the All Sites, All Plants, and All Units drop-downs appear above the grid. All values are selected by default. To include or exclude values:

1. Click one of the drop-downs.

2. Type in the search field to find a value, and then select the check boxes of the values to include. Clear the check boxes of the values to exclude.

3. Click Apply. The Apply button appears next to the drop-downs when you select only some of the values.

Filter the Results

To filter the results grid:

1. Click the Filter button at the top right of the grid. The Filters panel opens and lists the columns of the results.

2. Expand a column.

3. Select the check boxes of the values you want, or specify a range.

4. Repeat for any other columns you want to filter. The number next to a column shows how many values you selected in it.

5. Click Show results to apply your selection, or click Clear all to start again.

In the filtered results, you can still click a linked value, such as an equipment ID or a schedule name, to open that equipment or schedule.

Sort and Freeze Columns

To work with a column in the grid, click the three dots button next to the column name. The menu has these options:

• Freeze Column freezes the column in place.

• Sort Ascending sorts the results on that column from low to high.

• Sort Descending sorts the results on that column from high to low.

Pin Rows

The pinning feature lets you keep a selection of rows while you move through the Main Screen tabs. After you run the query, select the rows you want and click the Pin button. Only data related to the pinned selection is then displayed. You can pin up to 20,000 objects. For more, see Pinning Feature.

Export the Results

To export the results to Excel, click the Export button above the grid.

Save the Query

In the upper right corner, click Save to save the query. A saved query appears in the list of queries, where you can edit or delete it. Click the name of the query to open it again. You can also use saved queries in Dashboards. If you are struggling to get the results you need, Request Support.

Name the Query and Choose Who Can See It

To name the query and choose who can see it:

1. Click the three dots button above the grid, and then click Settings.

2. In the Name field, enter a name for the query. You can also rename it by clicking the query title on the New Query page.

3. In the Folder name field, select the folder to save the query in. You can select your personal folder if you created one.

4. In the Visibility field, select "Public & visible to everyone" (the default) or "Private & visible only to you".

5. In the Query Description field, briefly describe the data the query returns.

6. Click Apply.

Note

A query that is visible only to you is still visible to CSA users.

Send the Report by Email

You can send the query results by email on a schedule. To send a report:

1. Click the three dots button above the grid, and then click Send report.

2. In the Send report to field, select the people who should receive the report.

3. In the Send mail field, select how often to send the report: Daily, Weekly, Monthly, Quarterly, Every odd month, or Every even month. Daily is the default.

4. Select Don't notify if no data if you don't want a report sent when the query returns no data.

5. Click Apply.

Related Articles

For more information, see:

• AI Query Builder, to create a query from a plain-language question.

• How Query Data Is Organized, for more theoretical information on queries.

• Listing all Queries, to see the queries available to you.

• Reviewing collected Checklist Data, for an example of using a query.

• RCM Queries, for the RCM-specific queries.