This tutorial shows you how to create a template for a custom made report. In this example we’ll create a custom made order report. The final result will look like:
Notice that the tab on the bottom is named “Orders”, as this will be important for some settings later.
1) Create a template
First, you need to create a template for the purpose you want to. As you see, we’ve created an excel sheet with our logo and made some borders around the names of the columns. Important is that you need to save your template as .xls file, and not as an .xlsx, because there could be some problems with the version in the .xlsx format. What also is important is that the way you look at it when you save it, it is also the way you’ll see it when you request the report. So when the 'sheet2' tab was active while you saved the template file, you will face the second tab when you request the report. And also the selected cell will be the same after you request the report. So think about that when you save a template file. A template may look like this:
A best practice is to add an extra sheet in the template called 'Formulas and Explanation'. If Excel formulas need to be used to work with the data from FBO One it is best to do this in this sheet instead using hidden columns in the Report sheet. This way they will be visible and it will be easier to find the source of a problem if something isn't right in the report. If the formulas used are very complicated this sheet can also be used to explain the intent of those formulas so if something needs to be changed in the future it takes less time to understand the formulas.
For Amsterdam Support staff: add the JIRA issue number to the 'Formulas and Explanation sheet.
2) Upload the template
Now you have the template and saved it, we can upload it to FBO One. Go to the administration menu, and click Template documents.
Click Add new. Now you can browse to the file on your local hard disk. Then fill in the description and template name.
3) Add a new report
To create the report itself, you can refer to another page, how to create a custom report. In this tutorial we’ll go into the differences between creating a report with and without template. When you inserted the name of the template, you need to select a template document. The dropdown menu should now show the template you just uploaded.
Choose the right template and click Save.
4) Add a query to the report
Click Add new at Queries. Because in our example we need order information, the query will be the orders query.
The show column names checkbox is used if we want FBO One to give the name of the columns we select. Because we've created a template that has the column names already in it, we don't need this option.
The insert rows checkbox is used if for example we have a formula in the template on the first row and we want to use this formula also on the second row. This option can also be used for other options that you insert in a row and you want to use it for the next rows also. For example, if you centered the text.
The option 'Assign Excel names to values in first row' has to be unchecked for table reports such as this one but can be used for templates that are form-style and you need to show more field of 1 record on multiple places. For this example leave it unchecked.
5) Add columns for the report
Now that we have a query, we need to select the columns we want for our report. You can learn how to do this on the page how to create a custom report. The result should look in our case like this:
6) Run the report
Now you have completed all steps and the report should look like the report at the top of this screen.
If data in the same row but from different columns in the report is difficult to link together, it might help to format the report to show alternating grey white lines to increase the readability. To do that go to: How to add alternating grey white lines for better readability. The result will look like this:
How to create Excel 2007 Pivot Reports
How to create a custom report
How to add alternating grey white lines for better readability
How to change an Excel report template