Using Query Builder

[Using IFS Enterprise Explorer Clients] [Extracting Information in IFS Enterprise Explorer] [To Extracting Information in Enterprise Explorer]

Explanation

With Query Builder you can create personal reports without having to know SQL syntax or names of columns and constants. The Query Builder knows the columns and its data types for a view, so you don’t have to remember how they are spelled or how to format dates and numbers or what available static codes a column can have. It also knows how many of the views are related to each other, making it really easy to join those views, i.e. you don’t have to remember which columns maps to each other.

You use translated names for views and columns which makes it a lot easier to create the report since s the real names in the database can be too technical to understand. The translated view names are mostly in synch with the forms they are used on which makes it easy to find which ones to use.
Create Query Builder reports via the navigator node Reports/Ad-Hoc Reporting/Query Builder. Once you have saved the report you can run it via Reports/Ad-Hoc Reporting/Explore Query Builder Reports.

The reports you created are available via Explore Personal Query Builder Reports page. The reports are personal and will only be visible for the user who created them.

Prerequisites

By default all users are allowed to create their own Query Builder reports.

Window

Create Personal Query Builder Reports

Related Window Descriptions

Explore Personal Query Builder Reports

Procedure

You can create personal Query Builder reports via the navigator node Reports/Ad-Hoc Reporting/Create Personal Query Builder Reports.

Figure 1: Query Builder page

The page is divided into several parts:

Example

The following example shows how to create a report for Business Activities which has not started yet:

  1. Open the Create Personal Query Builder Reports page and click New button.
  2. In the Title field, type “Activities not started” as the name of the report.
  3. In the Filter field, type “bus act”. This will limit the number of views to select from

Figure 2: Example use filter to find views

  1. Locate and expand the view “Business Activity” in the search result to see all available columns.

Figure 3. Example view Business Activity expanded.

  1. Drag Start Date, Description and Main Contact to the Columns pane. Note the there are two Start Date columns, one is for local time and one is for universal time (UTC). If you move the mouse pointer to the column you will see a tooltip that will show “START_DATE_TIME_ZONE” (local time for the logged in user) for one and “START_DATE” (UTC) for the other; choose the first one.

Figure 4. Example columns selected.

  1. To limit the records to only see those who have not yet started, drag the column Start Date to the Criteria pane. Use the same Start Date as above.

Figure 5. Example criteria

  1. In the row you just added to the Criteria pane, change the Type to Substitution variable, the Operator to “>” and the Value to “#TODAY#”

Figure 6. Example Critera

  1. Save the report.
  2. Right-click on the header to open the context menu and select View Report to see the result of the report

Function columns

Some columns are merely a key to another view. Often this is a LOV (List of values), and fact is that often you want to display more than the key, such as the full name of a person and not just the User ID. There are actually two ways to do this, either by joining the views or by using the built in support for function columns. Thez are called function columns as they are displayed as a column, and can be used as columns, but they really are function calls.

The columns that have function columns are expandable. To show them just expand the node and then you can add them to the report as if they were normal columns (with the exception that certain restrictions apply if used in criteria).

Figure 7: Function columns for column Campaign ID

Note! If you use a function column in Criteria that the report can get very slow as using a function here will always cause the database to scan the whole table, i.e. it has to check every single row in the database. The database cannot use indexes when you have a function call in the criteria.

Criteria

Criteria corresponds to the WHERE clause in SQL, which is where you filter the records to show only the ones you are interested in. The Query Builder helps you with formatting of dates and numbers and also the syntax regarding how to add parameters to the report.
There are three types of criteria: Preset Value, Substitution Variables and User Input.

You can have more than one criterion in the report; just add more columns to the pane. When you add another column an AND operator will automatically be added for you. Change this to OR if you want in the drop down selector. You can also add operators manually by clicking on the plus sign in the header of the criteria pane.

Joins

When you add columns from more than one view to the report you always need a join expression that tells how the views are connected. When the Query Builder already knows the connection between the views it will automatically add the join expression for you. If however the Query Builder doesn’t know the connection, then you will need to add the join manually.

Adding a manual join

  1. On the joins pane, click the plus sign to add a new row.
  2. Select Left and Right view. The order of the views does not matter.
  3. Optionally you can change the join type. Normally you should keep the Inner Join.
  4. Open the Keys drop down and add keys by clicking the plus sign. Set the columns that connects the views.
  5. Save and show the report.

Figure 8: Joins pane, adding keys to a manual join.

Row Type

The report result is presented in an overview page. Zou can hightlight one or several rows in the oage and select View Details from the context menu. This will navigate to the detail page of the object you based the report on. As an example, if you have a report that shows Business Activities you can from each row navigate to the Business Activity page and see all information for that record. However, when you have a report that joins two views, you need to say which view to use when navigating from the report. You can only choose one view. This choice is done with the Row Type selector. Default is to use the first view you add to the report.

The Row Type can also enable menu commands that are specific for the type selected, such as Mail merge which is available for views like Person and Customer.