Edit Reports Tutorials

The Custom Reports module allows users to create, edit, and manage custom reports. This module provides a user-friendly interface to define report headers, fields, filters, and drilldown options, enabling tailored data analysis and reporting. 

Custom reports are split over two functions:

• Edit Reports is the interface where you create the report using SQL. Edit Reports can be found in the Setup menu.
• Custom Reports is where you view the reports. You may add custom reports to any Menu as a group, or individual reports. If you add Custom Reports as a group, a dropdown list is created from which you can select the reports. If you add the individual report to a menu, that report is automatically selected and there is no dropdown list.

These tutorials explain how to create custom reports. However, you will need to know how to use SQL to use this module effectively.

---

How to Create a Custom Report 

1. Navigate to Edit Reports.
2. Click New (button) and fill in the fields as follows:
   1. Description = Type a description for the report.
   2. Code = Type an abbreviated description for the report.
   3. Note = Type an explanation of what the report shows (optional).
3. Create the Fields.
4. Create the Filters.
5. Create any Drilldowns.
6. Click Menu (button) > User Access.
   1. Move the users from the Available to the Selected section using the navigational arrows in the middle to grant access.
   2. Click Save (button).

---

How to Populate the Fields

• The Tables and Parameter (tabs) provide data fields which can be used to write SQL queries.
• The Functions (tab) allows you to access and customise pre-written database functions (queries) and thereafter filter and sort the information into a format that suits your needs.

1. From editing a custom report, click Actions (button) > Edit SQL.
2. Add any data from the tables:
   1. Click Tables (tab).
      1. Double click a field to add it or right-click the field and select Create Select SQL from the context menu.
         • Note: You can right-click a field and select Show Fields from the context menu to see the fields contained in that table.
3. Add any data from the functions:
   1. Click Functions (tab).
   2. Right-click the applicable function and select Create Select SQL from the context menu.
4. Click Params (tab) and double-click any of the available fields to add them to the SQL query.
5. Click Test (button) on the bottom row to check for basic SQL errors. The results will be displayed on screen. Click OK (button).
6. Click Save (button).
7. Close the popup and wait a few seconds for the tables to be populated with the data.
8. On the Fields (tab) edit the following fields:
   1. Caption = Overtype the field name with a user-friendly caption.
   2. Editor = Select an option from the dropdown list.
      • Note: The Editor column is prefilled with the type of data that is contained in the field.
   3. Alignment = Choose a different alignment from the dropdown list (optional).
   4. Summary = Choose a summary option for the field if applicable.
   5. Visible = Uncheck the fields that you do not want to display (e.g. ID fields that contain numbers) or caption fields.
   6. Decimals = If the Editor option is Number, type the number of decimals.
   7. Fieldname Colour = Select a field from the dropdown list that you want to use as a colour filter.
      1. Navigate to the field you want to apply the colour to.
      2. Select the field from the dropdown list that contains the SQL instruction to set a colour based on a certain condition
         • Note: This feature is similar to conditional formatting on a spreadsheet.
   8. Fieldname Caption = Select a field from the dropdown list if you want to add an additional caption.
      1. Navigate to the field you want to apply the caption to.
      2. Select the field from the dropdown list that you created to generate the caption based on some condition (e.g. the name of the current month).
9. Click Save (button).

---

How to Create / Edit Filters

Filters will appear above the report, and work in the same way filters work in other reports (List Views). Creating filters is optional.

1. From editing a custom report, click Filters & Drilldown (tab).
2. Click Edit (button) on the top row if not greyed out.
3. On the left (Filters section): the fields you can use as filters are prefilled. You may also create additional filters by typing the information on the top row. Enter or edit the fields as follows:
   1. Type = Select a Type from the dropdown list (by default it is Fixed ID). The options are:
      • Date: Filters on a specific date.
      • Period (Date Start and Date End): Filters the records based on a period.
      • Text List: Allows users to create a lookup list to filter the data.
        1. Right-click the entry and select View/Edit Lists from the context menu.
        2. Type the entries for the lookup, one entry per line.
        3. Click Save (button).
        4. The checkbox in the Value List column will be checked.
      • Key Value Pairs (Comma separated): Allows users to replace a certain lookup field (as entered) with a different value (as entered).
        1. Right-click the entry and select View/Edit Lists from the context menu.
        2. Type the entries for the lookup, one entry per line in the format: Output, Value (e.g. 1, Active).
        3. Click Save (button).
        4. The checkbox in the Value List column will be checked.
      • SQL (Returns: filter_key; filter_value): Allows users to replace a certain lookup field with a different value (as determined by an SQL query).
        1. Right-click the entry and select View/Edit SQL from the context menu.
        2. Complete the SQL query (see Step 8 on How to Populate the Fields).
        3. Close the SQL popup.
        4. If the query was successfully created, the checkbox in the SQL column will be checked.
      • Fixed ID: Allows users to type a fixed ID.
        • Type the applicable ID in the Fixed ID Column.
   2. Caption = Type a caption for the filter.
4. Click Save (button).

---

How to Create a Context Menu Action (Drilldown)

 The drilldown function is optional. You can use it to define actions that will be available via the context menu (e.g. open the Customer account). You may include as many drilldown options as you wish. The available drilldown modules are Assets, Contact, Item, Service Manager, and Transaction. 

1. From editing a custom report, click Filters & Drilldown (tab).
2. Click Edit (button) on the top row if not greyed out.
3. On the right (Drilldown section), create any actions as follows:
   1. Type = Select Module (to open a particular module) or Attachment (to open a URL).
   2. Module = Select a Module from the dropdown list. Only applicable if Module was chosen as the Type.
   3. Fieldname = Select a field name, from the dropdown list of available field names, to link the action to.
   4. Menu Caption = Type the caption that the user will see when they right-click the entry.
4. Repeat Step 3 to create more actions (optional).
5. Click Save (button).

---

How to Edit a Custom Report 

1. Navigate to Edit Reports.
2. Right-click the report you want to edit and select Edit Report from the context menu.
3. When the interface opens, click Edit (button) on the top row.
4. Edit the report as needed.
   • Note: To edit the SQL, click Actions (button) > Edit SQL.
5. Click Save (button) on the top row if not greyed out.

---

How to Delete a Report 

1. Navigate to Edit Reports.
2. Right-click the report you want to edit and select Delete Report from the context menu.

---

Tips

• Regularly save changes: Click Save (button) frequently to avoid losing changes, especially when editing fields, filters, or SQL.
• Use descriptive names: Ensure report descriptions and codes are clear to make them easy to identify in the List View.
• Test SQL queries: Validate SQL queries in a separate environment before applying them to avoid errors.
• Leverage drilldowns: Use drilldowns to link reports to related modules for quick access to detailed data.
• Manage filters carefully: Ensure filter parameters match the SQL query to avoid runtime errors.

---

Troubleshooting

• Invalid SQL error:
  • Check the SQL query for syntax errors or missing parameters. Ensure all referenced tables and fields exist.
• Fields not updating:
  • Verify that the SQL query is valid and saved. Fields are generated based on the query’s output.
• Filters not working:
  • Ensure filter names match the parameters in the SQL query and that the correct filter type is selected.
• Drilldown not working (when viewing the report):
  • For modules, verify the field contains a valid ID.
  • For attachments, ensure the URL is valid and accessible.
• “No SQL Statement Provided” Error: 
  • The selected report lacks a valid SQL query. Edit the report to provide a query.
    • Note: This error message shows when you try to view the report.

---