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:

• View Groups. A drop down list where you can choose what to show in the Views pane.
  • Search views – shows all views in the system, but you must provide a filter text. When the filter is empty the list is also empty. Default when adding a new Query Builder report.
  • Related views – shows the views that are used in the report plus all that have reference to or from those views.
  • Query views – shows the views that are used in the report. Default when editing a Query Builder report.
• Filter. Use the filter to search for views. This works similar as the Navigator Filter which means that you only need to type a few characters. E.g. “cus or” will show views that have at least one word starting with “cus” and at least one word starting with “or”. This means “cus or” will match “Customer Order” but not “Customer Info”.
• Views. This is where the views are presented. Views normally represent a table in the database. A view consists of one or more columns. To see the columns for a view just expand the node. You can add columns by dragging them to either the Columns or the Criteria pane.
• Columns. These are the columns that will be presented in the report.
• Criteria. This is where you filter the records to show in the report. If you leave this list empty you will show all records in the report. Normally you only want to see some of the records available, e.g. all customers in a certain city or all orders for a specific month.
• Joins. When you add 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.

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

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

Figure 3. Example view Business Activity expanded.

5. 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.

6. 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

7. 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

8. Save the report.
9. 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.

• Preset Value. Preset value is the default type of criterion value. This is the type of criterion you add when you want the same criterion every time you run the report.
• Substitution Variables. If you want to have dynamic values in the report, e.g. “Today”, you can do this by changing the Type of the criterion to Substitution Variable; when you do that you will get a drop down list of values that are applicable for the data type of the column in the criterion. A substitution variable will evaluated when the report is executed.
• User Input (a.k.a. Report Parameters). To add Report Parameters you change the criterion Type to User Input. The value part will change to a question mark and you will be prompted to provide the value when you execute the report.

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.