Report Designer User Guide
The SQL Query
The first step in creating a report is to create an SQL Query. An SQL Query is a piece of text which defines:
- Which tables in the database the information is to be extracted from.
- Which fields from the tables are to be included.
- The rules and conditions that are to be used to determine what data is to be included in the result.
An Introduction to Databases
Creating an SQL Query
The steps to be taken when constructing an SQL Query in MEX are as follows:
- Open up the Query Designer.
- Select the tables to be used in your Query.
- Create the joins between the tables.
- Select the fields from the tables that you wish to include in your Query.
- Add any calculated fields to the Query
- If you wish to filter the data extracted by your Query then add the necessary criteria.
- Preview the results of your Query before closing the Query Designer and returning to the Report Editor.
Step I. Open the Query Designer
To open up the Query Designer:
- In the Report Design screen, click on the "Set Datasource" button located in the top left-hand corner of the screen. This will open up the "Report Query Designer" screen.
The Report Query Designer has three main areas:
- The "Table Selector" which allows you to select the tables and fields that are to be included in your query.
- The "Table Display", which shows you the tables that you have selected for your query and the relationships between the tables.
- The "Field List", which shows you a list of the fields that you have selected to be returned by your query.
The Report Query Designer
Step II. Selecting the Tables to be Used in Your Query
In the Query Designer, the first thing to do is to select the tables that are to be used in your Query. The Relational diagrams provided in this manual can help you decide which tables you need to include. Relational diagrams are available for the following areas of MEX:
| Area of MEX | Relational Diagram |
| Asset Register | The Asset Register |
| Contacts (Tradesmen, Suppliers, Customers etc.) | Contacts |
| Easy Time | Easy Time |
| Preventative Maintenance | Preventative Maintenance |
| Purchase Orders | Purchase Orders |
| Stores Catalogue | Stores Catalogue |
| Work Orders | Work Orders |
| Work Order Spares | Work Order Spares |
The tables are selected from the Table Selector area of the Query Designer. The Table Selector is basically a list of the tables which make up the MEX database. You select a table for inclusion in your query by double- clicking on the table in the list which will add it to the Table Display area. If you wish to remove a table from the Query then you can do so by clicking on the small cross in the table's top right-hand corner.
The "Commonly Used" Tables
By default only the commonly used tables are available in the table selector; the MEX database has quite a few more tables in it. It is unlikely that your query will require tables other than the "commonly used' ones, but if you do you can click on the "Show all objects" button. This will make all of the databases's tables, views and functions available for selection.
Step III. Review the Joins Between the Tables
Once you have added the necessary tables to your query, you need to specify the relationships between the tables by creating the "Joins". A Join is used to define how the data in one table is related to the data in another. For example:
- A record in the Asset table has the field "AssetID", which is a unique number used to definitively identify the Asset in the system.
- A record in the Work Order contains the "AssetID" field to determine which Asset a Work order has been created against.
- Therefore, the Asset and Work Order tables are related to each other via their respective AssetID fields, and a join should be created between these two tables using this field.
To create a join between tables, in the Table Display area:
- Locate the field in the first table.
- Locate the field in the second table. This may involve scrolling through the fields in the table to locate it.
- Using your mouse, "drag-and-drop" the field from the first table onto the corresponding field in the second table. This will draw a line between the two tables indicating the relationship between them. In the example shown below the Asset and WorkOrder tables have been joined via the AssetID field in each table:
Joins Between Tables
Types of Join
There are three types of joins within SQL:
- Show records from both tables only where there is a match.
- Return all records from the first table, and only records from the second table when there is a match.
- Return all records from the second table, and only records from the first table when there is a match.
The type of join selected will affect the results returned by the query. By default when you add a join between tables it will only display records where there is a match between the tables.
As an example, suppose that we have a Work Order and a Department table as shown below. You will see that not all of the Work Orders have a value in the DepartmentID field, meaning that these Work Orders have not had a Department selected for them:
If we join the two tables together using the DepartmentID field, it means that only the Work Orders that have a Department will be shown in the results of the Query:
To return all of the Work Orders including those without a Department, we need to alter the join to "Return all Work Order Records", which means that all of the Work Order records will be returned by the Query and Department records will be shown where there is a match:
You can choose a different type of join by right-clicking on a join and selecting one of the options from the pop- up menu. In the example below the join is being changed so that all records will be returned from the WorkOrder table, and records from the Department table will only be displayed when there is a match:
The Join Properties Menu
Step IV. Selecting Fields for Your Query
Having added the tables to your Query and defined the joins between them, you can now select the fields from the tables that you wish to include in the results. Each table in the Query contains a list of the fields in the table. You can scroll through the list of fields in a table and when you have located a field that you wish to include click on the check-box to select it.
Selecting Fields for the Query
When a field is selected for the Query it will be included in the results of that Query and may be included in the report. In the example below the Department, Work Order Number and Description fields have been selected for the Query, which means that these fields will be included in the query’s results when it is executed:
Renaming the Fields
When you add a field to a query it uses the field name from the database which tend to be a bit over-long and sometimes unclear. As an example the Due Start Date on a Work Orders has the field name "DueStartDateTime". For the purposes of clarity it is a good idea to rename the fields that will actually be displayed on the report to something shorter and clearer.
For each field that you are including in the Query you can enter a new name into the "Column Name" area as shown below:
Renaming the Fields
Enabling Parameters for the Report
When a report is added to the Reports Listing, parameters can be selected to filter the report by. The parameters that are available for selection will depend upon the fields that have been included in the report's Data Source. In order for a parameter to be available for a report, the parameter's corresponding field must be included in the top level of the Report's Data Source. For example, if you want to filter a report by Account Code, then the AccountCodeID field must be included in the top level of your report's Data Source.
The fields for the various parameters are given in the table below:
| Parameter | Field | Parameter | Field | |
| Asset | AssetID | Manufacturer | ManufacturerID | |
| Account Code | AccountCodeID | Model Number | ModelNumberID | |
| Asset Type | AssetTypeID | Payment Term | PaymentTermID | |
| Bin Location | BinLocationID | Preventative Maintenance | PreventativeMaintenanceID | |
| Catalogue Class | CatalogueClassID | Priority | PriorityID | |
Catalogue Group | CatalogueGroupID | Purchase Order | PurchaseOrderID | |
| Catalogue Item | AssetID | Purchase Order Status | PurchaseOrderStatusName | |
| Catalogue Transaction Action | CatalogueTransactionActionName | Purchase Order Type | PurchaseOrderTypeName | |
| Colour | ColourID | Qualification | QualificationID | |
| Component Code | ComponentCodeID | Reason Code 1 | ReasonCode1ID | |
| Condition Code | ConditionCodeName | Reason Code 2 | ReasonCode2ID | |
| Currency Type | CurrencyTypeID | Request | RequestID | |
| Customer | CustomerContactID | Stock Class | StockClassID | |
| Customer Type | CustomerTypeID | Store | StoreID | |
| Department | DepartmentID | Supplier | SupplierContactID | |
| Freight Company | FreightContactID | Tax | TaxID | |
| Frequency Type | FrequencyTypeID | Trade | TradeContactID | |
| Fuel Type | FuelTypeID | Trade Code | TradeCodeID | |
| Hazardous Class | HazardousClassID | UOM | UOMID | |
| INC | IncID | Work Order | WorkOrderID | |
| Inspection Question Type | InspectionQuestionTypeName | Work Order Status | WorkOrderStatusID | |
| Job Type | JobTypeID | Work Order Status Function | WorkOrderStatusFunctionName | |
| Key Status | KeyStatusName | |||
The exception to this rule is if you wish to enable a set of date parameters for your report. To do this a date field must be included in your Query, and an entry must be made in the Criteria for the date field.
Using Predefined Variables for Report Parameters
If your SQL skills are more advanced then you can make use of some predefined variables in your report' generally not required unless you are trying to create more complex queries that summarise data in such parameter fields cannot be included in the results of your query.
@RegionID
The @RegionID parameter will always be set to the ID of the Region that the user running the repo
Report Parameter Variables
Each drop-down parameter in the Reports Menu also populates a predefined parameter. For exam available as a parameter on the Reports Menu. The predefined variable @paramDepartmentID w in your query, and the variable will be populated with the ID of the Department that has been select If no Department has been selected then the variable will be set to NULL.
If you wanted to use the @paramDepartmentID variable in your query to select all Work orders th Department selected in the drop-down, or if no Department is selected the select all Work Orders, you could use would be:
SELECT * FROM WorkOrder Where WorkOrder.DepartmentID = @paramDepartmentID OR @paramD
Step V. Adding Calculated Fields to the Query
If you wish you can add add fields to the Query that are calculated from other fields, rather than being added directly from a table. For example you might have a "Cost" and "Quantity" field. To add a "Total Cost" field to your Query you would add the calculation "Cost times Quantity" and call it "Total Cost".
The method for including a calculated field in a query is to type the calculation to be performed into an empty row in the Field List. In the example below the EstimatedLaboutCost of a Work Order has been added to the EstimatedMaterialCost:
Adding a Calculated Field
When you are entering a calculation into a query you must use a specific format for typing in the fields. You must first type in the name of the table, then a dot, then the name of the field. In the above example the EstimatedMaterialCost field from the WorkOrder table is being included in the calculation, so it has been typed in as: WorkOrder.EstimatedMaterialCost
Once you have entered a calculated field query you can enter a name for the field into it's "Column Name". In the above example the calculated field has been called "EstimatedTotalCost".
Some examples of formulae that may be used in calculated fields are:
| Numbers | Adding Numbers | Label: [First Field] + [Second Field] or Label: [Field] + 123 |
| Subtracting Numbers | Label: [First Field] – [Second Field] or Label: [Field] – 123 | |
| Multiplying Numbers | Label: [First Field] * [Second Field] or Label: [Field] * 123 | |
| Dividing Numbers | Label: [First Field] / [Second Field] or Label: [Field] / 123 |
| Text | Adding Text Together | Label: [First Name] + [Last Name] or Label: [First Name] + 'Sample of Text' |
Step VI: Adding Criteria to Your Query
By adding criteria to a query, it is possible to filter the information returned by the query. Criteria can be assigned against any column of a query. When the query is run, the data returned will only include the records in which the field matches the criteria specified for the field.
For instance, the example query below will only return the records in which the ActualQuantity is greater than Zero and the JobTypeName is equal to "PM".
Adding Criteria to the Query
Some examples of common criteria expressions are detailed below.
| Text | Equal to a String | 'sample of text' |
| Not Equal to a String | <> 'sample of text' | |
| Using Wild Cards | '%sample' or 'sample%' or '%sample%' | |
| The field is empty | Is Null | |
| The field is not empty | Is Not Null |
| Numbers | General Number | 12345 |
| Greater Than | > [Field] or >123 | |
| Less Than | < [Field] or <123 | |
| Equal To | = [Field] or =123 | |
| The field is empty | Is Null | |
| The field is not empty | Is Not Null |
| Dates | General Date | '2016-01-01' |
| Greater Than | [Date Field] or >#01/12/2003# | |
| Less Than | < [Date Field] or <#01/12/2003# | |
| Between 2 Dates | Between #01/01/2003# and #01/01/2004# | |
| The field is empty | Is Null |
| The field is not empty | Is Not Null |
| Boolean (True/False) | Is True | 1 |
| Is False | 0 |
If the field is a check box (for example the dangerous goods check box in the stores catalogue) the criteria can be either one or zero.
Adding Criteria to a Text Field
If you are adding criteria to a text field then you must enclose your text with single quotation marks. For example if you want to enter the text Breakdown as a criteria then it must be entered as 'Breakdown'.
Adding Criteria to a Date Field
If you are adding criteria to a Date field then:
The date must be entered in the yyyy-MM-dd format. The date must be enclosed by single quotes.
So, if your criteria is that the contents of the date field must be greater than the 31st of May 2015, then you would enter: > '2015-05-31'
Enabling Date Parameters For Your Report
One of the most common ways of filtering a report is by the date. In the MEX Report Menu there are three sets of date parameters that may be used; Date1, Date 2 and Date 3. To filter a report using one of these sets of date parameters you must:
- Include a date field in your Query.
- Enter the date criteria into the field's Criteria.
The Criteria to use in the date field is:
BETWEEN @Date1Start AND @Date1End
For example, if you wanted a query to return only the Work Orders that were created within a given date range then you would include the WorkOrder table's RaisedDateTime field in the Query and enter "BETWEEN @Date1Start AND @Date1End" into the field's Criteria as shown below:
Enabling Date Parameters for a Report
If you want to use more than one set of date parameters you can also use:
BETWEEN [Date2Start] AND [Date2End]
Or
BETWEEN [Date12Start] AND [Date2End]
Step VII: Previewing the Results of your Query
The results of your query can be previewed at any time during the design process, however you should make a habit of previewing it before closing the Query Designer to ensure that your query will return data within your expectations. Clicking on the "Preview" button located at the top right-hand corner of the Query Editor will show a listing of the first 100 records that will be returned by your query. If you are happy with the results then you can close the Query Designer and continue with creating your report.
If at any time you wish to alter your query you can return to the Query Designer by clicking on the "Set Datasource" button in the top right-hand corner of the screen.
Previewing the Query Results