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.
By default all users are allowed to create their own Query Builder reports.
Create Personal Query Builder Reports
Explore Personal Query Builder Reports
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:
The following example shows how to create a report for Business Activities which has not started yet:
Figure 2: Example use filter to find views
Figure 3. Example view Business Activity expanded.
Figure 4. Example columns selected.
Figure 5. Example criteria
Figure 6. Example Critera
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 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.
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.
Figure 8: Joins pane, adding keys to a manual join.
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.