Query Results

About Query Results

The following topics describe available actions related to query results.

Access the Results Workspace

Procedure

Access the Query page.
Select Browse.

The Select a query from the catalog window appears.
Navigate to the folder that contains the query you want to view, select the query, and then select Open.

The Results workspace appears.

Sort the Query Results

Before You Begin

When defining sort criteria for a query, note that:

The sort criteria determines the default sort order for query results. After the results are displayed, users can modify the sort order in the results grid.
If no sort criteria has been defined, the query results will not be sorted in any particular order by default. Users can still modify the sort order in the results grid.
Any sort criteria that is defined for numeric columns in a query will be applied to stored values (vs. displayed values) regardless of whether the query is running in formatted or unformatted mode.
Unlike the sorting that you can apply directly to the results, sorting preferences that you define in the Design workspace are stored with the query itself and are applied each time you run the query.

For example, according to the following image, the Asset Installation Date will be sorted in Ascending order:

These sort options will affect the query results, as shown in the following image:

Note: The sort order that you specify within the query definition will be saved as the default layout for the results.

About This Task

For any query, you can define criteria to determine how the results will be sorted by default.

Procedure

For the query whose results you want to sort, access the Design workspace.
In the Conditions section, in the Sort cell for the field that you want to sort, select Ascending or Descending. By default, the Sort Index cell for the first field that you sort displays a 1, indicating that this field will be used as the primary sort value.
If you want to set a secondary sort value, in the Sort cell of the appropriate field, select Ascending or Descending. By default, the Sort Index cell for the second field that you sort displays a 2, indicating that this field will be used as the secondary sort value.
Repeat these steps until you have defined all the sort values you want.
After you run the query, the columns for which you defined a sorting preference will be sorted in the specific order.

Sort Column Values in the Results Workspace

About This Task

You can sort values in one or more columns in ascending or descending order.

Note: These settings are not saved when you access the Results workspace again.

Procedure

Access the Results workspace.
To sort the values in a column in ascending or descending order, select the column header.
The values in the selected column are sorted in ascending or descending order.
To sort the values in multiple columns, perform the following steps:
1. For the column whose values you want to sort, right-click the column header, and then select Sort Ascending or Sort Descending.
2. For each additional column whose values you want to sort, right-click the column header, and then select the sort order.
The values in selected columns are sorted in ascending or descending order.

Note: The columns whose values are sorted in ascending order are indicated by in the column headers, and the columns whose values are sorted in descending order are indicated by in the column headers. The numbers appearing in the column headers denote the sequence in which you have sorted the columns.
Tip: To clear the sorting from a column, right-click the column header, and then select Clear Sorting.

Group by Column Values in the Results Workspace

About This Task

You can group query results based on the values in a column.

Note: These settings are not saved when you access the Results workspace again.

Procedure

Access the Results workspace.
To group the results based on the values in a column, perform the following steps:
1. Right-click the column header and select Group by This Column.
  
  Tip: You can also drag and drop a column header on an area outside the result grid to create a group.
  
  The results are grouped based on the values in the selected column.
2. To create sub-groups, right-click additional column headers and select Group by This Column.
  A sub-group is created based on the values in the selected column.
Note: The groups are automatically sorted in ascending order. You can change the order by selecting the grouped column name.
Tip: To remove a group, perform the following steps:
- Right-click the grouped column name and select Ungroup. You can also drag and drop a grouped column name on an area within the result grid to remove the group.
- To remove all groups, right-click any grouped column name or column header, and then select Ungroup All.

Filter the Query Results

About This Task

You can filter the values in the query results by setting a filter in one or more columns.

Note: These settings are not saved when you access the Results workspace again.

Procedure

Access the Results workspace.
Select .
A Filter row is added below each column name.
Select below the column name in which you want to set the filter criterion.
A window with a list of filter options appears.
Select the option for filtering the values in the column.
Enter the value for the filter in the filter cell of the column.
The values are filtered as per the criterion set in the column.

Export a Query Result Set to a File

About This Task

Note that, when you run a query in unformatted mode, the results will still display formatted date values. Date values will always be displayed in the local time for the user; however, if you export the unformatted query result set to a file or dataset, the exported date values will be unformatted. Formatted queries and queries with a large number of results take a longer time to export.

Therefore, long running queries should be exported in the background.

Note: When running the query during export, you can specify the page size to be used by the query engine.

Procedure

For the Select query for which you want to export, access the Design workspace.
In the upper-right corner of the workspace, select , and then select Export to a File.

The Export to a File window appears.
In the Please provide a File Name box, provide the file name.
In the drop-down list, select the desired file type.
Select the page size to be used by query execution engine during export to file. By default, this value is set to 50000.
If this is a long running query, select the Run in background check box.
Select Export.
- If you choose to run the export immediately, the exported file will download using your browser. Save the file to the desired location.
- If you choose to run the export in the background, you will get a message that it has been submitted.
  From the queries overview page, select Query Export. A list of exported query results will be displayed.
  - If the status is Submitted, your export has not started.
  - If the status is Running, your export is currently running.
  - If the status is Finished, you may download the results by selecting the row and selected the download icon.
  - If the status is Failed, your export failed, and the error message is in the Errors column.
After downloading, you may delete the export by selecting the row, and then selecting the Delete icon.

Note: If the query that is being exported contains a lot of pages, the export process is slow. For queries which results in less number of records, there is no difference in export time. For queries with large number of results, if you set a higher page size value, the time required to export can be reduced.

Export a Query Result Set to a Dataset

About This Task

A query can be exported as a dataset, a fixed set of information that is stored as a Catalog item.

Note: When you run a query in unformatted mode, the results will still display formatted date values. Date values will always be displayed in the local time for the user; however, if you export the unformatted query result set to a file or dataset, the exported date values will be unformatted.

Procedure

For the Select query for which you want to export, access the Design workspace.
In the upper-right corner of the workspace, select , and then select Export to a Dataset.
The Save As window appears.
In the folder hierarchy, navigate to the folder in which you want to save the query.
In the Name box, enter a name for the dataset. If you select the same name and location as an existing dataset, that existing dataset will be overwritten when you save.
The Caption box is populated automatically with the value that you entered in the Name box.
In the Description box, you can enter a description for the dataset. This is not required to save the query.
The Of type box is populated automatically with the Catalog item type.
Select Save.
The query result set has been exported to a dataset.

Modify the Value in the Field Cell

Procedure

Access the Design workspace.
In the Conditions section, in the grid, in the Field cell for the column whose value you want to define, select the gray button in the right side of the cell.

The Advanced section of the Expression Builder window appears.
In the text box, enter the text you want to appear in the results, surrounded in single quotation marks.
Select Done.
In the page heading, select .

Remove the Limit on the Number of Results

Procedure

For the query for which you want to remove the limit on the number of results that are returned, access the Design workspace.
In the heading of the Conditions section, clear the Limit Results check box.
The Limit Results to Top box is disabled, and the number that appeared in it is removed.
In the content heading, select .
The query results are displayed, and the number of results that are returned is no longer limited.

Modify the Output Mode of a Select Query

Procedure

For the Select query for which you want to modify the output mode, access the Design workspace.
In the grid in the Conditions section, depending on which mode you want to use, select or clear the Formatted check box as needed.
In the content header, select .
The query is run in the selected mode, and the results appear.
Note: When you run a query in unformatted mode, the results will still display formatted date values. Date values will always be displayed in the local time for the user.
Tip: You can save the query to save the selected mode. The next time you run the query, it will use the mode that was selected when it was last saved.

Display Unique Records Only

Procedure

For the query for which you want to display unique records only, access the Design workspace.
In the grid in the Conditions section, select the Unique Records Only check box.
The Limit Results to Top box is disabled, and the number that appeared in it is removed.
In the content heading, select .
The query is run, and the results will include only unique records.

Limit the Number of Results

Procedure

For the query for which you want to define the number of results that are returned, access the Design workspace.
In the Conditions section heading, select the Limit Results check box.
In the Limit Results to Top box, enter the number of results you want the query to return.
In the content header, select .
The query results are displayed according to your selection in the Records box. For example, if you enter 20 in the Records box, the query will return only the first 20 records that meet the other query criteria.

Aggregate Query Results

Before You Begin

Ensure that all the fields for which you want to aggregate results have a total type selected (for example, group by, count).

Procedure

For the query for which you want to aggregate the query results, access the Design workspace.
Select the Show totals check box.
In the content header, select .
The query results are aggregated.

Show the Units of Measurement

Procedure

For the query for which you want to see the units of measurement in the query results, access the Design workspace.
Select the Show UOM check box.
In the content header, select .
The units of measurement appear in the query results.

Create a Hyperlink

Procedure

To create an internal URL:
1. Access the Design workspace of the query in which you want to create hyperlinks.
2. In the grid in the Conditions section, for the field on which you want to create the hyperlink, in the Hyperlink cell, select .
  The URL Builder window appears.
  
  Note: Do not create a hyperlink on a date field. Doing so will convert the value in the date field to the local time of the user creating it, and the value will be incorrect for users in different time zones.
3. In the Select URL Format section, select Meridium APM, and then select Next.
4. In the Select URL Feature Area section, in the list, select the area of APM to which you want the URL to point, and then select Next.
5. In the Select URL Type section, in the list, select the specific location to which you want the URL to point, and then select Next.
6. In the Specify Parameters section, specify the Entity Key and any other values necessary to populate the URL, and then select Finish.
  
  Note: If the parameter value you specify includes a special character, you must enter the URL-encoded value. For example, the manual hyperlink to open a record in record manager using a datasheet with the ID TEST?&% must be entered as #record-manager/{1}?datasheetid= TEST%3F%26%25, assuming that the entity key of the record appears in column 1 of the query.
  
  The Expression Builder window closes, and then, in the grid in the Conditions section, <expr> appears in the field on which you created the prompt.
To create an external URL:
1. Access the Design workspace of the query in which you want to create hyperlinks.
2. In the grid in the Conditions section for the field on which you want to create the hyperlink, in the Hyperlink cell, select . Do not create a hyperlink on a date field. Doing so will convert the value in the date field to the local time of the user creating it, and the value will be incorrect for users in different time zones.
  The URL Builder window appears.
3. In the Select URL Format section, select Manual.
4. In the Specify Parameters section, enter the URL to which you want the hyperlink to correspond, and then select Finish.
  
  Note: If the parameter value you specify includes a special character, you must enter the URL-encoded value. For example, the manual hyperlink to open a record in record manager using a datasheet with the ID TEST?&% must be entered as #record-manager/{1}?datasheetid= TEST%3F%26%25, assuming that the entity key of the record appears in column 1 of the query.
  
  The Expression Builder window closes, and in the grid in the Conditions section, <expr> appears in the field on which you created the expression.

Results

Note: If you clear the Included check box for the System field, and then run the query in formatted mode, selecting a hyperlink in the query results may cause the following error message to appear: An entity with the key '{<X>}' could not be found. Please close the tab. If this occurs, select the Included check box for the System field, and then rerun the query.

You cannot modify a hyperlink using the URL Builder window. The URL Builder interface does not store settings for existing hyperlinks.
If you want to use the URL Builder window to modify a hyperlink, you can recreate the hyperlink and include your modifications in the URL Builder window.

Delete a Hyperlink

Procedure

Access the Design workspace.
In the grid in the Conditions section, for the cell in which you want to delete a hyperlink, select .
The hyperlink is removed from the query.