How to create a custom report

In this article we explain how to create a report based on a custom selection of data. The report will be generated as a plain Excel file.

The report in this example shows the contract credit terms in days for handling services. The report output will look similar to this:

1) Create a report

Go to the administration menu, click reports and click “Add new”.

Insert a name for this new report. This is the name that will also appear like that in the reports list. For example fill in: “Credit days contacts”.

As we are not using a template, leave this drop down box empty. For creating reports that are based on a template, see how to create a template.

The remarks will be displayed in the reports list. For example: “This report shows the credit term in days per customer”. 

2) Add a Query

Now click Add New at: "Queries to be executed for the report selected above".

We need to select a data set for this report. You can choose from several queries. Because the contract terms are part of the contact data, we choose the contacts query.

After selecting the query we could specify a target sheet and cell. This defines the Excel sheet and the top-left column of the report data. This is not a required field and mostly used only if we use a template. Leave it blank to report the data starting at cell A1.

The option “show column names” will write the column names in the first row. In this case we need to tick the box. If you report data into a template, you may want to clear this option and instead manually insert adjusted column names in the template itself.

The “Insert rows” is useful only in case you are loading data into a template-based report. It copies the formatting of the first row in the template to the other rows. This can significantly slow down generating the report, so leave it unchecked.

The “Assign Excel names to values in first row” option is only used with templates. It will make FBO One define a named cell based on the column name for each value in the first row of data. This allows formulas in the template to use easy to read name-based references instead of cell references. 

Some data providers (in our case the contacts query) have options. For example, the movements query allows you to specify if you want to report arrivals or departures only. Such options can be specified in the the “Data provider settings” field. You can find these options in queries manual page.

When you request a report, there are several ways to filter data from the report. First of all, you can use Excel to filter using Excel's built-in filtering function. You can also limit the data that FBO One will export using Report C# filter expressions. For example, you could use a filter expression to limit the output to customers with a name that begins with the letter A. In this example, we don't need a filter.

3) Define the columns for the report

Now we need to configure the columns in the report. For our example, we need the Name and the Credit term. Click “Add new” and pick the Contacts | Name column.

Now we need to fill in a “Column offset”. Excel names it columns alphabetically, as A, B, C and so. In FBO One, the columns are numbered 0, 1, 2. Therefore, enter 0 to place the Contact name in Column A of the output.

For standard tabular reports like the current example, the Row offset should be kept at 0 for all of the columns.

The Column width is optional. If you specify it, the column will be assigned the given fixed width in Excel. For a contact Name, a width 20 characters is an appropriate value. The width is based on the width of a character in the default font. 

The Column alias and format field can be left blank for this example. The alias, if used, will provide an alternative value of the header of the column in the first row.

After you have saved the first column, you can follow the same procedure for the remaining columns, in this case “Contacts | Credit term days”.

You should see this now:

You can now run the new report from the “Reports” page.

 

See also:

Queries: Contacts

How to: create a template

Fields: Report