Custom Queries Editor

The Custom Query Editor is used to generate sets of data from different tables and views that are present in the IMS database. The Query results can then later be sorted, filtered and exported to Excel (or used in a Dashboard). 

Creating Custom Queries

To start a new custom Query you need to open the Query Editor. You can then either start from scratch or select an existing Query to edit.

Opening the Query Editor

To open the Custom Query Editor:

• Click on the Custom reports Main Menu.

• Select Queries from the drop down.

Creating Personal Folders

You can create a personal folder to store your Queries in:

• Click the green Plus button next to My queries.

Creating New Queries

To create a new Query:

• Click the Plus button.

• Then follow the instructions below to create your Query.

Editing Existing Queries

To edit an existing Query:

• Find the applicable Query in the list.

• Click the Edit button.

• Then follow the instructions below to create your Query.

Defining the General Section (top)

When you start a new Custom Query Editor you first needs to fill in the general fields (in the top) to define the Query. 

General fields and their usage:

• Name - Here you can enter the Query name.

• Folder name - Select a folder in which your newly created Query will be saved. You can use your personal folder if you created one.

• Private - When set as Private the Query will only be visible to user that created it and CSA users.

• Base view - Here you can search and select a base view for the Query (if you do not see a desired view please Request Support).

• Query Description - Shortly describe the dataset that the Query will provide.

• Send report- If you select this, then the Query report will be email. Two additional fields become available:

  • Send report to - Here you can select email addresses to which the Query should be send.

  • Send mail - Here you can select the frequency for sending the Query report.

• GridActionBar Template - The enables you to set an additional template that is rendered after the normal GridActionBar of UDQ. Note: You will normally not use this.

• Percent Base Query - If selected, then percentage can be shown on Dashboards (base count).

Creating Datasets from Base views

Selecting the Base view

Selecting a Base view is the first step in creating a new Custom Query. This is done via the Base view field in the general section (top). A Base view is either a table from the database that you can use to build up the Query using dependent objects or it is a ready-made view that will give you exports that we expect the users will need . The readymade views start with "vw". 

Selecting Fields

Once the Base view is selected, you will see the Field selection section. Here you can select fields from Base view for the dataset:

• Use the collection arrow to expand the list.

• Check the box next to the desired field names.

The selected fields will become the grid column in your Query results.

Changing Field (Column) Titles

All selected fields will show on the right side. Here you can rename the fields. This is then the column titles that will show in the results.

Rearranging Columns

You can also rearrange columns by dragging them into the order that you want them to appear in the results.

Removing Fields (Columns)

You can always remove fields (columns), if you change your mind. This is done by clicking on the trash bin button next to the field that needs to be removed.

Extending the Base view

The Base view can also be extended with tables and views to which it has a defined relationship with. To do this:

• Click the dropdown arrow.

• Select the desired table / view.

• Then click the +.

• Depending on the selected entity, you can then again expand with the (collection) arrow to see the individual fields. 

One-One or One-Many Relationship

If the relationship is one to one or one to many like in the example above, columns in the result grid can be sorted and filtered on:

Many-One and Many-Many Relationship

If the relationship is many to one or many to many like in the example below:

Then the result grid will not be sortable on the columns, but you will still be able to filter the grid on the collection data.

 

Fields marked in blue

You can keep on extending the entities to get the dataset needed. Fields marked in blue represent an extension of the entity that it is on.

Pivoting Data

Custom Queries also allow you to pivot (move data in columns to rows) data to help build up your Custom Query. To add a pivoted field to your data:

• Add the fields that you want to your Query, as shown above.

• Click the pivot icon at the top right of the Field Selection table.

• Next, arrange the fields in your Query by dragging them from the Selected Fields grid on the right to the desired location (Row, Column, Values).

As you place the Fields into their rows or columns, their color will change to indicate that they have been used in the pivot table.

• Click Save to save the changes to your Custom Query.

Setting Criteria (Filtering the Dataset)

You can (optionally) also add criteria based on Field values, to filter your dataset. 

Adding a Criteria

To add a criteria:

• Either add a Field directly from the Criteria section by clicking +, or 

• Select one of the Fields that have already been added (right pane) and click +.

• Add the criteria for the Field value (e.g. less than...).

Removing Criteria 

To remove a criteria again, just click the x next to it.

Adding more than one option

To add more than one option select "in (; separated)" from the dropdown.

Saving Queries

When your dataset is ready, you can Save the Query.

Displaying Results

Saving the Query will redirect you to the result view, where the dataset can be sorted, filtered or be exported to Excel. Saved Queries can also be used in Dashboards.

If you are struggling to get the desired results, please Request Support.

Filtering the Data

To filter your Query:

• Click the filter button.

• Expand the desired criteria.

• Check boxes of the desired values or specify a range. If this list is too long and not displaying everything:

  • Click on the three dots button.

    

  • Enter a search value in the search field.

    

  • Click <Enter>.

  • Select the desired values.

• Click on the Show x matching… link to show the matching tags.

• The additional filters will be added to the scoping bar (highlighted in yellow).

• The criteria can then be modified directly by clicking on the criteria in the scoping bar.

Examples

For an example of use, see Reviewing collected Checklist Data.

For RCM also see RCM Queries.