Work with QueryBuilder
You can perform the following actions in QueryBuilder:
Create a new query
- In QueryBuilder, select the entity you want to search in the Entity field.
- In the Filter area, click
to add a new clause, and select the field to be filtered as well as a filter condition, and a value. To remove an applied filter, click
.
- In the Select area, click in the field and select a field to display that field in the search results.
To remove a field from the search results, in the Search field, click x to the left of the field name. - In the Expand area, click
and select a table to expand the search results.
- Below the Expand field, select the fields of the table you want to display in the search results.
You can add multiple connected tables to the selected entity, but you cannot expand the search results sequentially across multiple tables. - In the Order by area, select the first sort criterion and the sorting direction (ascending/descending) of the search results. The default sorting order is always by Id (ID) and ascending. You can add additional sort order criteria. When adding multiple criteria to the sorting order, the order of the criteria will determine the order of the sorting in the search results.
- Click Search to execute the search. Remember to click Search to update the search after making changes to the query.
Save a query
- In the Result area, click Save Query to open the Save Query form.
- In the Save Query form, enter a name for the query in the Name field, and optionally a description in the Description field.
- Select the Select the Save date filters with absolute values check box if you want to save all filter date values as absolute values, for example saving the absolute date 27 February 2021 instead of the relative date Today +1.
- Click Save New to save the query.
Load a saved query
Queries created in QueryBuilder are saved in the WorkZone database, and therefore you can also search for them and load them from the WorkZone database.
- In QueryBuilder, click My Queries to search the WorkZone database for all your queries, that is queries of which you are the owner. All your queries are shown in the Results area.
- Click an ID in the ID column to open the query on a new tab.
- The name of the saved query will be displayed in the upper left hand corner of the QueryBuilder page.
Update a saved query
You can update an existing query or use an existing query as a foundation for another query instead of creating a new query from scratch.
Update a query
- In QueryBuilder, load the query you want to update. See Load a saved query.
- On the new QueryBuilder tab, make your changes to the query.
- Click Save Query to open the Save Query form.
- Click Update to save your changes to the query.
Save an existing query as a new query
- In QueryBuilder, load the query you want to update. See Load a saved query.
- On the new QueryBuilder tab, click Save Query to open the Save Query form.
- In the Save Query form, enter a new name for the query in the Name field, and optionally a description in the Description field.
- Select the Select the Save date filters with absolute values check box if you want to save all filter date values as absolute values, for example saving the absolute date 27 February 2021 instead of the relative dates Today +1.
- Click Save New to save the query with a new name.
- Make any changes to the new query and click Save New to save the your changes.
Delete a saved query
In QueryBuilder, load the query you want to update. See Load a saved query.
- On the new QueryBuilder tab, click Delete Query to open the Delete query form.
- In the Delete query form, click Delete.
- Click OK to confirm the deletion of the loaded query.
Copy query components to the clipboard
You can copy individual query components from a query to your clipboard for insertion in other locations in WorkZone.
To copy a component in QueryBuilder, click the component label you want to copy to the clipboard, and then click OK. You can paste the clipboard contents to the desired location in WorkZone.
The following elements of a query can be copied to the clipboard:
- Entity
- Select
- Filter
- Expand
- Order by
- Aggregate
For example, by copying the value of the Entity element, you can see the system entity type for custom types after pasting the copied Entity value to the clipboard.
Export a query
You can export the query results as an *.xltx file which can be opened in Microsoft Excel. The results are displayed in an Excel worksheet with column headings for filtering.
Export a query to Excel
- In QueryBuilder Results area, click Export Query to convert the current query to an .xltx file. The file is named
Som.File.xltx
. You can either download it locally and open it in Microsoft Excel or open the file directly from the browser. - When Excel opens, click Enable Content in the yellow security warning bar to enable data connections and to run the query in the background.
- Set the data connection credentials and privacy levels that are relevant for your organization, and click Save to open the query results in Excel.
- In Excel, you can filter and adjust the search results and create charts, add additional calculations, and apply functions to the results.
- Finally, you can save the spreadsheet and the embedded data connections as a local file.
Tip: You can edit the query from Microsoft Excel in the Query tab.
Create Power BI reports using WorkZone data
You can export a query and import it in Microsoft Power BI for creating BI reports based on WorkZone data.
-
Click Export Query to convert the query to an .xltx file.
-
Open Power BI.
-
Go to File > Import > Power Query, Power Pivot, Power View.
-
Navigate to your Downloads folder and open the downloaded .xltx file.
Tip: You may have to change the file extension to show All files (*.*) to see the xltx file. -
Click Start and Power BI starts importing the query setup into a Power BI sheet. When the import is completed, click Close in the Import Excel workbook contents dialog.
-
When you see the message: "There are pending changes in your queries that haven't been applied". Click Apply changes.
-
Click Transform data > Transform data to see the data. Power BI does not load the data in to the sheet by default.
-
Right click WorkZone OData and click Enable load.
-
Click Close and apply.
-
Enter your credentials (only the first time you load data).
Now, you are ready to start creating the BI report. You can always refresh the data by clicking Refresh.