Before defining a user report type, first determine what data fields are required and the table names of where the data fields may be found. This will help identify your Base Table and Additional Data Sources if required.
How to identify a table name for a data field
As a starting point, find the data field name on an Equus Platform screen. The data field's table name is most likely to be called the Menu Item or Equus Platform screen name such as Employee.
This is not always the case if the Menu Item name has changed. In the example below, Vendor Services does not exist as a table name. The actual table name is called Service Order and this appears in the URL path which does not change if when the Menu Item changes.
Other tips on selecting or creating the right user report type is to:
have some knowledge of the Equus Platform database schema - try to think how table are linked
identify where the field location - is it at the system / company / assignment / employee / template / line item level
use &mode=ddmo or "Inspect" on Google Chrome to identify the names of tables and fields
How to identify an appropriate Base Table
A base table is the first data source designated for the User Report Type. It is possible to have a User Report Type solely defined by a Base Table with no additional sources if all the data fields you require resides in this table.
From the generic user’s perspective, a Base Table is the "primary table" or the table with the most important data for the report. From a technical perspective, it is the FROM table and every other table you include will be joined to it. Left join if it is a child/sibling (Company to Employee for example), Inner join if it is a parent (Cost Estimate to Assignment for example).
Example: A report to show Assignment and Assignment Dependent details which requires data from two tables: Assignment and Assignment Dependent. Either of the two tables can be the base table but the most appropriate one depends on what data you want t see.
Scenario 1: Base Table = Assignment Dependent
Using a User Report with the below configuration shows Assignments that have Assignment Dependent details only. By selecting Assignment Dependent as the Base Table it acts as a filter and excludes Assignments with no Assignment Dependent details from the report.
Scenario 2: Base Table = Assignment
Using a User Report with the below configuration shows ALL Assignments and the Assignment Dependent details associated with the Assignment. If there is no associated Assignment Dependent record associated with the Assignment, the Assignment is included in the report. Compared to the previous example, the outcome of this report returns a "fuller" set of data and if data is to be excluded from the report this can be carefully managed using the User Report's filter function.
How to identify an appropriate path for Additional Data Sources
If the Base Table does not include all data fields in the User Report, additional data sources needs to be specified in the Additional Data Sources Allowed section on the User Report Type Details page. This involves two steps:
1. Select the data source/ table name from the Data Source drop down list
2. Click [Choose] and specify the path. The path chosen for each data source will determine how the data sources are joined together. In most cases the system will initially give suggestions on the possible direct paths. Click [Show More] to see all the possible paths and select the most appropriate one depending on what data you require. Having poor User Report Type configuration can impact system performance so it is best practice to use the path that is most direct to the relevant data that you require.
Example: A report to show all Assignments where "Visa Service" is the Service Order Type value which requires data from at least the Assignment and Service Order tables.
Scenario 1: User Report Type uses system suggested direct path
Using a User Report with the below User Report Type configuration shows all Assignments and the associated Service Order details if it exists and no values if there is no Service Order record associated with the Assignment. A filter can be added to the User Report to show only Assignments where Service Order Type = Visa Services.
Scenario 2: User Report Type uses an alternative path
Service Orders can be requested from certain screens in the Equus Platform such as the Assignment Visa Request screen which in this examples has been renamed as the Visa & Work Permit in the Menu Items.
Click
and select the path that includes "Assignment Visa Service Request". To easily find a path in the list, use the browser’s "Find on Page" functionality (in most browsers, this is accessed by pressing [Ctrl] and F on the keyboard). When the correct path is displayed, click the radio button to select it, and then click
.
Using a User Report with the below User Report Type configuration can be a more direct path to showing all the Service Orders records of interest as it filters out Service Orders not found in the Assignment Visa Service Request table (i.e. Service Orders not created from the Visa & Work Permit screen). This is User Report Type configuration is appropriate if all "Visa Services" Service Order Types are created from this specific screen but is not suitable if this is not case. This is highlighted in the User Report Outcome rows for Mary Smith when comparing the row details in this scenario and the previous one.
