Guide to creating reports and forms

In this section, you can create custom forms and reports or use default reports provided by Sage. You can create forms and reports with a visual interface or by using advanced tools like GraphQL queries, HTML code and cascading style sheets (CSS). Both methods include a preview panel that updates whilst you work.

Overview

The Reporting menu includes tools to create and manage reports and report templates. This page also lists any custom reports that you created.

On the Reports page, you can find several factory reports delivered by Sage. They cover many areas such as purchasing, sales, manufacturing and so on. Except for changing the Active template, factory reports cannot be modified.

Some factory reports include a standard report template delivered by Sage. Some include variables for filtering data when generating a report.

You can generate a standard form template as delivered. Or you can duplicate the form on the Templates page and customise it to meet your business needs or apply company branding.

To add variables to a report, you need to create a custom report, which you can base on a factory report. See the section on Conditions and variables for more information.

When you open a report record, you can see the Parent package, which defines data used in the report, and whether the Report type is a printed document or an email.

The Templates tab displays any standard forms delivered by Sage and customised templates that use this report's data package and associated variables.

Templates are versions of this report with a custom layout and data included in a report. Creating a custom template is explained in detail on the Report templates page. A report can have many templates, and you can go back and edit them as needed. You can, of course, access them here, but they are also listed on the Report templates page.

If a report includes variables, those variables can be used for all the templates created from the report. For example, the salesCreditMemo factory report includes a variable that references a credit note when you generate a report. That variable is available in all templates created from the salesCreditMemo report.

If a factory report does not include variables, you cannot add any. First, you need to create a copy of the factory report and then add variables. This process is described in detail on the Reports page.

Accessing reports and templates

From the Reports page, you can select a report to view details and any associated templates. On the Templates tab, select the Edit icon to modify a custom template. Or you can select the printer icon to generate the report. To modify a standard template delivered by Sage, you need to open it from the Report templates page and create a duplicate that can be modified.

Custom report templates

You can easily create custom forms and reports using a visual interface. Whether you need a purchase order or a monthly sales report, you can select the data you need and apply filters and other refinements. Add a logo, customise the layout, choose a font and align content however you want. See your changes while you work to get the results you want before sending to a customer or others in your organisation.

Conditions and variables

As mentioned above, some factory reports include variables that display on the Variables tab on the report record. Variables are important for filtering and referencing data. To add variables to a factory report, you need to create a copy of the report first and then you can add variables. Creating a copy of a report is similar to creating a new report.

Add a variable

The first example shows how to add a variable with a reference to a report. The means that the report displays data for a specific document such as a purchase order or sales order, or a specific customer. When you create a template and want to generate the report, you'll need to add the reference number.

This process is also detailed within the context of creating a custom form or report template.

Add a reference: This type of variable can be used to reference a document. First you create the variable and then you add it as a filter on the data container for the report.

1. On the Variables tab, select Add line.
2. In the first column, enter the Variable name using the camelCase format. For example, purchaseOrder.
3. Press the Tab key on your keyboard. Notice that the Variable title displays automatically and capitalises the first word and adds a space before each capitalised word in the Variable name: Purchase order. You can accept the default or change the name if you want different capitalisation, or you want to add accents for your language. You cannot change the title after saving the report record.
4. Press the Tab key again and in the Variable type field, select Reference. On a form, when you enter a purchase order number in this field, your template references data from that purchase order only.
5. Press the Tab key a few more times to validate the row.
6. Save the report record. Remember, you cannot change the Variable title after saving.

To use this variable, you need to add it to the data container in a report.

7. Start by filtering data for your report. On the Editing toolbar, open the Fields panel.
8. In the search box, enter purchase order.
9. Select the Purchase order data field to add the data container.
10. The Insert an object type wizard opens. Select Data container.
11. On the Define filters panel, select Add filter.
12. For Property, select _id.
    For Filter type, select Equals.
    Switch Use parameter to ON.
    For Filter value, select the name of the variable you created, in this case Purchase order.
13. Apply.

Add a condition: This next type of variable can be used to print or not print a conditional block. First you create the variable and then you can add a conditional block using this variable, which displays as a checkbox on the Preview panel.

1. On the report record Variable tab, select Add line.
2. In the first column, enter a Variable name, such as printBlock and then press the Tab key. The Variable title displays as Print block. Again, you can change the name before saving if you want or accept the default.

3. For Variable type, select Boolean.
4. Tab a few more times to validate the line. Remember to save the report record.

Add a conditional block

Conditional blocks give you the option to print or omit data when generating a report.

5. While editing or creating a report template, on the Editing toolbar, select Conditional block.
6. Select Add condition, enter these settings, and Apply.
   Value Type 1 is Parameter.
   Value 1 is Print block.
   Operator defaults to Equals.
   Value Type 2 is Constant.
   Value 2 is True.
7. This means that any data in this block does not display in the preview and is not included when you generate the report. You can select the Print block checkbox, in this example, to display and include the data in the conditional block.

Watermarks

In the Preview panel on the Settings tab, you can enter a watermark. It can contain up to 36 characters including spaces.

If the report includes a reference variable, such as an order number, the watermark only displays in the preview when you enter a reference like an order number.

See the Report template page for details and step-by-step instructions for creating a new form.

Add Data

On the Template tab you can select, filter and sort the data for your report. See the section below for layout options.

1. Select the folder symbol to display the Fields panel. This panel displays the data you can add to your template including any Custom fields that you created. You can add custom fields to your forms and reports.
2. When you select data, you can choose a Table or a Data container. A table provides a list of records. The data container displays a main record, with additional records if needed, based on settings for filters and sorting. Within a data container, you can insert a table from the toolbar to customise how your data displays.
   
   See Guide to creating reports and forms for more information.
3. A wizard guides you through the process of defining filters and sorting data. The table wizard gives you the additional option of selecting columns and grouping data.
4. When defining filters, you have the Use parameter option. When switched to On, you can filter a specific property such as order number, date and so on.
5. Data displays in the Preview panel and updates whilst you work. Select Preview if you don't see it. And select Extend preview for a full-page view and the option to print to PDF or a connected printer.

Group data and add totals

For reports that include a list of lines, such as sales order or purchase order lines, you can group data and include key metrics like minimum, maximum, sum, average and count.

If you're creating a report with line data like sales or purchase order lines, you can group table data that features key metrics like minimum, maximum, sum, average and count. To do this, you need to add data as a table.

When you get to the third panel, Define content, in the wizard, you can begin to group your data and select an operator like sum, average, distinct count and so on, for lines.

The image below shows an example of how you can group data and add metrics.

Important points

The Title column displays a custom name that is different from the property name.

Groups display in numerical order. You can have more than 1 property per group.

The Document number operation can only be Distinct count.

Sums and averages are labeled Group 1 footer, Group 2 footer and so on, in the visual editor.

The last group simply displays Footer.

Layout and design options

This section describes the various layout and design options you can use when building a custom form or report. You can do this on the Template tab.

Basic formatting options

Start by displaying the Formatting panel from the toolbar, which displays options and settings related to elements in the template.

Highlight an element like a table or data container to see your options.

If you added a table for data, the Formatting panel displays alignment, background and border colors, styles, and you can modify columns.

If you added a data container, use the Formatting panel to modify sorting and filters.

If you did not define a locale or a paper format, you can do this on the Settings tab in the Preview panel. The paper format refers to standard paper sizes and affects how your template displays and prints.

Editing toolbar

The Editing toolbar is part of the rich text editor and provides standard options for text and other elements in your template. For example, you can insert an image, like a company logo, using the Insert image button. You can change fonts, insert a table and so on.

From the toolbar, you can also insert:

Document properties

When you select this option, you can insert dynamic content such as the current date, who generated the report and so on.

If your cursor is in the header or footer section, you can insert the page number and the total number of pages.

Unbreakable container

When you insert an unbreakable container, the data within the container displays on the same page. For example, if you had the container toward the lower part of the page, the container would begin at the top of the next page if the contents don't fit the current page.

Conditional block

To include or not a data field or block based on variables and filters described above.

Headers and footers

You can enter content for headers and footers directly in the design area. Remember that the content you enter displays on all pages.

In addition, you can insert document properties like page number and total number of pages, as described above.

If your header or footer content overlaps with the main report content, go back to the Basic details tab to increase the size.

Move or remove elements

Elements can be moved within their original blocks. You cannot move a data field from one data container to another or from a table to a data container.

Select an element. This changes to border colour from yellow to blue. You can drag it to a new location, add a carriage return or delete it.

List reports

From the Report page, you can create a list report using a wizard that guides you through the process, which includes:

• Selecting data and fields in a table
• Defining filters and sorting
• Grouping data and adding summary rows
• Previewing your report
• Adding default headers and footers

Advanced report option

You can also define reports by accessing the HTML code, cascading style sheets (CSS) and GraphQL queries.

A custom form or report can be converted to an advanced, or HTML, report to give you complete control over the code behind the report. You can add complexity without starting from scratch, saving time. In addition, when you convert to HTML, you can preserve the original version as well.

Header and footer tab

This tab only displays when you build an advanced, HTML template.

You can enter a header and/or a footer that displays on every page of your template. Select Preview to update the Preview panel to see your text.

Attachments

The content for this topic is still in progress.

We will make the content available as soon as we can. Your patience is appreciated.

Translations

If you need to use your custom template in another language, on the Translation tab you can enter the translated text for plain text you entered in your template.

The Translation grid displays one line for each line of plain text you added to your template.

1. In the Target language column, select the language or languages you want to create text for. When you select a target language, any text in that language in your report displays in the Source column.
2. In the Target column, enter translated text for each line and in each language you need.
3. To see the translated text in your template, in the Preview panel, change Preview locale on the Settings tab to the desired language.

Translating reports delivered by Sage

Factory reports are reports and templates delivered by Sage. These reports cannot be modified, nor can you add custom variables or translated text. As mentioned above, you need to create a copy of a factory report following the steps to create a custom form or template.

Export a report to another tenant

If your organisation has more than one tenant, you can use the Import data page to transfer reports and templates from one environment to another.

Creating detailed, customised reports can take some time. Doing this in an active environment can add unnecessary complexity. For this reason, there are advantages in creating reports in a dedicated tenant or environment.

When you are satisfied with the report template, you can easily export it to your live environment.

Share a report with another tenant

This process includes 2 main steps: exporting from the source tenant and importing into the destination tenant.

Export the report and report template

1. From Reports page main list, select one or multiple reports.
2. Select Export.
3. When the export finishes, download the file or files from the Notification centre.
4. From the Report templates page, select a template linked to the report you just exported.

Import the report and report template

1. Change to the tenant where you want to share the report or reports.
2. Go to Administration > Import and export > Import data.
3. On the Data tab, select the report file you downloaded and saved.
4. In the Files to import grid in the Template column, select the Report.

Import the template

1. If necessary, go back to the Import data page.
2. Repeat the steps above importing the report template file you downloaded and saved.
3. In the Files to import grid in the Template column, select Report template.