Articles in this section

Report templates (design your Excel reports)

  • Updated
Follow

A long-awaited functionality, new in version 21: Report templates, a way to make your Excel reports look good.

First of all, why create a report template?

If you use the dynamic reporting in combination with the task configuration to share your dashboards and reports, you might have noticed that till today the Excel file you're sharing, didn't really "have the looks". A shared Excel by default looks like this:

report-templates-example-no-layout.png

Let's get started to make your Excel reports look good

How it works

A dynamic report contains columns and each column is automatically defined by iController as a kind of 'column data type'. Let's say we've built the report below:

report-templates-example-report-columns.png

The preview of this report looks like this:

report-templates-example-report-preview.png

Each column is automatically defined as a column 'data type':

Column name Column data type in 'English' Column 'data type' in iController
Company Text string
Customer number Text string
Customer name Text string
Outstanding amount Amount denomination
Overdue amount Amount denomination
Maximum days due Number integer
Date last procedure step Date date
Procedure step Text string

To define the looks of the Excel, we are going to build and upload an Excel template in which we define the looks per column data type (third column in the table above).

Example 1: basic report template

Report template: template-outstanding-items-list-basic.xlsx

report-templates-template-basic.png

Result: result-template-outstanding-items-list-basic.xlsx

What does this template do:

  1. Row 1 defines each unique column data type, and how to look. This equals to the headers of your report.
  2. Row 2 defines the values of the report. Probably, in the report itself, the values contain more than 1 row, so the row 3 and 4 will be pushed downwards automatically.
  3. Row 3 defines the subtotals, if applicable
  4. Row 4 defines the totals, if applicable

Please note that you can change the layout of all 4 rows, but they have to stick together. You can, for example, do this:

report-templates-template-basic-moved.png

But you can't do this:

report-templates-template-basic-fails.png

Important: cells A1 until F4 need to stay together and although we don't know in front which column data types the reports contains, the report template needs them all: 

  • string
  • date
  • integer
  • denomination
  • double
  • date-time

More examples

For a wide range of examples, please visit this knowledge base article: Report templates - examples

Important note: In this first version, this functionality is only available for our support. We try to create a userfriendly version as soon as possible, which will be available for system administrators as well. Please contact our support team with the preferred template you would like to add in iController.

 

Configure

  1. Go to Settings > Report templates

    settings-report-templates.png

  2. Click Add new

    report-templates-add-new.png
  3. Name your template, upload an Excel template (examples) and choose if you want to show subtotals and/or totals.

    report-templates-create-new.png
  4. Click Save

How to use

  1. Create a task configuration

    report-templates-create-task.png

  2. If you choose Excel 2007 as the File type, another option appears: Excel template
  3. Choose from the report templates you've created before and click Download preview to see the result.

  4. If you get an error page, your report template is incorrect. If you have difficulties setting it up, please contact collections-support@billtrust.com 

Related articles