Edit Reports Tutorials

Custom reports are created and edited with SQL in Edit Reports, on the Setup menu, and viewed in Custom Reports. You must be familiar with SQL to use this module effectively. Creating customised reports for customers is also one of the services QuickEasy offers.

💡These tutorials explain how to create, edit, copy and delete custom reports, populate their fields, and add filters and drilldown actions.

How to Create a Custom Report#

1. Navigate to Edit Reports.
2. Click New (button) and fill in the fields as follows:
   1. Description = Enter a description for the report.
   2. Code = Enter an abbreviated description for the report.
   3. Chart Type = Select a type of chart from the dropdown list. This selection can be changed later.
   4. Note = Enter an explanation of what the report shows (optional).
3. Create the Fields.
4. To add a chart:
   1. Check one box below Chart Category for the field that groups the data (for example, the quotation number).
   2. Check one or more of the boxes below Chart Series to populate the chart.
      • Note: Not all fields result in meaningful charts.
5. Create the Filters.
6. Create any Drilldowns.
7. Click Menu (button) > Edit User Access.
   1. To grant access, move the users from the Available to the Selected section using the arrows in the middle.
   2. Click Save (button).

How to Populate the Fields#

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.
         • Note: You can right-click a table and select Show Fields to see the fields it contains.
3. Add any data from the functions:
   1. Click Functions (tab).
   2. Right-click the applicable function and select Create Select SQL.
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 appear 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 = Replace 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 show (e.g. ID fields that contain numbers, or caption fields).
   6. Decimals = If the Editor option is Number, enter 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 in 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 or Edit Filters#

1. From editing a custom report, click Filters & Drilldown (tab).
2. Click Edit (button) on the top row if it is not blue.
3. In the Filters section on the left, the fields you can use as filters are prefilled. You can also create more filters by completing the top row. Enter or edit the fields as follows:
   1. Type = Select a type from the dropdown list (the default 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: Create a lookup list to filter the data.
        1. Right-click the entry and select View/Edit Lists.
        2. Enter the entries for the lookup, one entry per line.
        3. Click Save (button).
        4. The checkbox in the Value List column is checked.
      • Key Value Pairs (Comma separated): Replace a certain lookup field (as entered) with a different value (as entered).
        1. Right-click the entry and select View/Edit Lists.
        2. Enter 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 is checked.
      • SQL (Returns: filter_key, filter_value): 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.
        2. Complete the SQL query (see Steps 1 to 6 on How to Populate the Fields).
        3. Close the SQL popup.
        4. If the query was created successfully, the checkbox in the SQL column is checked.
      • Fixed ID: Uses a fixed ID.
        1. Enter the applicable ID in the Fixed ID column.
   2. Caption = Enter a caption for the filter.
4. Click Save (button).

How to Create a Context Menu Action (Drilldown)#

1. From editing a custom report, click Filters & Drilldown (tab).
2. Click Edit (button) on the top row if it is not blue.
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. Applies only if Module was chosen as the Type.
   3. Fieldname = Select the field name to link the action to from the dropdown list.
   4. Menu Caption = Enter the caption that the user sees 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.
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 it is not greyed out.

How to Copy a Report#

1. Navigate to Edit Reports.
2. Right-click the report you want to copy and select Copy Report.
   • Note: When the report is open, you can also click Actions (button) > Copy Report. Any unsaved changes to the report are saved first.
3. Click Yes (button) on the message “Copy Report?” to confirm.
4. The copy opens for editing. Its description is the report’s description with “- Copy” added at the end. The copy has the same code, note, SQL, chart type, fields, filters, drilldowns and user access as the original report.
5. Change the Description and Code, and edit the report as needed.
6. Click Save (button).

How to Delete a Report#

1. Navigate to Edit Reports.
2. Right-click the report you want to delete and select Delete Report.
3. Confirm the deletion on the popup.

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.