Custom Reports Tutorial

Overview#

💡These tutorials explain how to view a custom report, create and edit reports, add filters, charts and drilldowns, give users access and fix common problems.

For every button, field and setting on the screens, see Custom Reports.

How to View a Custom Report#

1. Navigate to Custom Reports.
2. Select the report from the dropdown list.
3. Set the filters that appear, for example, the date range or the customer.
4. Click View (button).
5. If the report has a chart, click Chart (tab) to see it.

How to Create a Custom Report#

1. Navigate to Custom Reports, click Menu (button) and select Edit Reports.
2. Click New (button).
3. Enter the Name and a Description of the report.
4. Click Edit SQL (button), enter the report's SQL query and save it. The Fields tab now lists the query's fields.
5. On the Fields tab, change the Caption, Alignment, Decimals, Summary and Visible settings as needed.
6. Click Save (button).
7. Give the users access to the report (see How to Give a User Access to a Report).

How to Change a Report Safely#

1. In Edit Reports, right-click the report and select Copy. Confirm the copy.
2. Make your changes on the copy and test it on the Custom Reports screen.
3. When the copy works, make the same changes on the original report, then delete the copy.

How to Add a Filter#

1. Open the report in Edit Reports and click Edit SQL (button).
2. Add the parameter to the query's conditions, for example, where Q.QUOTEDATE between :DATESTART and :DATEEND for a date range, or and Q.CUSID = :CUSID for a Customer filter.
3. Save the SQL and the report.

How to Add a Chart#

1. Open the report in Edit Reports.
2. Select the Chart Type, for example, Column diagram.
3. Click Chart (tab). Check the Category field (for example, the month) and the Series fields (the numbers to show).
4. Click Save (button).
   • Note: The report now opens with a Chart tab next to the Data tab.

How to Add a Drilldown#

1. Open the report in Edit Reports.
2. Make sure the SQL query returns the ID of the record to open, for example, the Sales Order's ID.
3. Under Drilldown, select the Module (for example, Sales Orders) and the Fieldname that holds the ID.
4. Click Save (button).
   • Note: Users can now double-click a row, or right-click it and select DrillDown, to open the record.

How to Give a User Access to a Report#

1. Navigate to Main Menu > Office > My Organisation, double-click the user and click Settings (tab).
2. Find Reports & Dashboard > Reports - Access Allowed.
3. Click Edit... and select the reports the user may view.
4. Save the user's settings.

How to Add a Report to the Dashboard#

1. In Edit Reports, add the ID column with the Field Chooser (*) and note the IDs of the reports you want.
2. Navigate to Main Menu > Office > My Organisation, double-click the user and click Settings (tab).
3. Enter the IDs, separated by commas, in Reports & Dashboard > Dashboard - Reports (ReportID eg. 1,2,3).
4. Set Dashboard - Rows and Dashboard - Columns for the layout of the charts.

How to Move a Report to Another Database#

1. In Edit Reports, right-click the report and select Save to file. Save the .qer file.
2. In the other database, open Edit Reports and click Load (button).
3. Select the .qer file. The report is added with its SQL, fields and chart.

Troubleshooting#