build apps by user

Print Report Data on Excel Spreadsheet

After report data is collected on a worksheet, you can create a report template in Excel spreadsheet and “print” the report data onto the spreadsheet. The fun of using Excel spreadsheet to create report is that you can use the familiar formatting features of Excel to create your report style, and apply simple Excel formulas to do some calculations as part of the report content.

Format Report on Excel Spreadsheet

To continue the example of customer statement report here, let’s create the layout of statement report on an Excel spreadsheet.
Customer statement is a multiple-page report with the same layout for each page, what you need to do is to create a single whole-page layout of the report on the Excel spreadsheet. Just use the formatting features in Excel to create the page layout, font styles, sizes and colors etc.
A full page layout of Customer Statement:
format a report template on Excel spreadsheet
The content of a report includes Replacing Cell Values, Report Directives and Excel Formulas. Replacing Cell Values are used for moving the corresponding cell values of the retrieved report data from the BAU DB worksheet onto the Excel spreadsheet. Report Directives are commands to control the layout and presentation of report data as it is rolling out onto the spreadsheet. Excel Formulas can be used for a lot of purposes like doing some calculations on the report data as supplements to the contents of the report.
Cell Values
Cell values are the cell captions on BAU DB worksheet enclosed with double-colons:
Add replacing cells on the spreadsheet template for moving data from BAU DB worksheet
Report Directives
Report directives always start with 2 dots in front of the keyword of the commands, so that you can also call them dot-dot commands:
report directives control the layout and presentation of report data
Excel Formulas
Embedding Excel formulas in the report is a convenient way to enrich the report content:
Excel formulas can be added on the report to do some calculations

Cell Values

The fundamental function here is to move the report data from BAU DB worksheet to Excel spreadsheet. Just like printing automated document, what you need to do is to enclose cell captions of BAU DB worksheet with double-colons and place them on the Excel spreadsheet.
enclose cell captions with double-colons on Excel spreadsheet
A cell value can be concatenated with other texts in a cell in the spreadsheet:
cell value concatenate with text in a cell on the Excel spreadsheet

Report Directives (Dot Dot Commands)

Report directives give instructions to the program about how the report data are to be printed on Excel spreadsheet. In this example of Customer Statement, a number of directives are used.
apply report directives on the spreadsheet to control the layout of data
..formatMultiPreprint is the style of the report. Firstly, it is a report with multiple groups of data, as in the Customer Statement the data are grouped by multiple customers.
Secondly, all pages of the report will have the same exact layout, which is convenient when you want to print the report on pre-printed paper form. It is therefore when formatting the report template, the layout of a single whole page is created.
..formatMultiPreprint is not a location sensitive command and can be placed in any location on the spreadsheet.
..GroupBy is used here to indicate the grouping of report data. Customer Statement is a report of invoice data with multiple customers. The invoice record are presented and grouped by each of the customers.
In the example, since invoice data is grouped by customer, ..GroupBy “Customer” is used, with which “Customer” is the cell caption in the worksheet.
..GroupBy is a location sensitive command. It is placed in the row defined with multiple value data – in this case, the row with invoice data – ::Invoice::, ::Date:: .
You can group data by more than one cell. In that case, use comma to separate the cell captions after ..GroupBy.
apply report directives on the spreadsheet to control report layout
..LoopBottom indicates the row position to stop when printing rows of multiple value data in a page. After that, a new page is created to continue printing the rest of the data.
In the example, the invoice records of a customer are printed from the row where cell values are defined up to the row with ..LoopBottom. If that customer has more rows of invoice records to be printed, a new page is created to continue the printing.
The rows defined with ..LastPage only appear on the last page of each grouped data. It is useful to present summary information for each group of data.
In the example, total invoice amount of a customer only appears in the last page of that customer, if that customer has more then one page of invoice records.
..PageBreak adds a page break in the spreadsheet at the row where it is defined, which is also the end of the whole page layout.

Combine Using Excel Formulas

It is just convenient to combine using Excel formulas to enrich the content of your reports. The formulas will be copied to all rows and pages in the report with cell references updated automatically.
The examples below demonstrate the 2 use cases used in the customer statement report.
Calculate overdue period
To calculate the overdue period of an invoice, 2 cell values from the BAU DB worksheet are also placed in the same row of invoice data – ::By This Date:: is the cell value of the statement date, and ::Amount:: is the invoice amount.
Since these 2 cell values are for calculation purpose only, they are “hidden” in the Excel spreadsheet. To hide a cell value in Excel spreadsheet is easy, just give it a “white” color, that’s it.
statement date is hidden in the same row of repetitive invoice data
invoice amount is hidden in the same row of repetitive invoice data
On the same row position, 3 Excel formulas are then added to calculate the difference of invoice due date with the statement date. Invoice amount will only be displayed in either of the 3 different columns with due date period of 0-30days, 31-60 days and more than 60 days respectively.
the formula to calculate overdue period is hidden in the same row of repetitive invoice data
The formulas will be brought forward along with the cell values as the report data are printed and rolled out over the rows of the spreadsheet.
Calculate sum of overdue amount of each customer
The formulas here calculate the total overdue invoice amount for each group of customer. In the example, the Excel formula applied is Sum(E11:E42), which sum up the cell values from E11 to E42.
When the page is expanded and the rows of invoice records of a customer are spread out on more than one page, the cell references of that formula will be changed accordingly to sum up the whole column of data of that customer.
this formula sum() is used here to calculate the total invoice amount.
You can definitely figure out much more fun and useful ways to incorporate Excel formulas in your reports.

Generate Report onto the Excel Spreadsheet Template

As you have created the page layout, added cell values of report data, report directives (dot dot commands) and Excel formulas, the Excel spreadsheet template is now ready for “printing” – to move the data from the BAU DB worksheet onto the spreadsheet template.
To generate the report, go to “Print” button and select the report template file.
press the print button to generate the excel spreadsheet report
select spreadsheet template file for printing the report
Just like the document template, you can create file name prefix for printing in the worksheet. It will be more convenient for selecting the template when you will only see the file templates for that worksheet. In worksheet setting, add the prefix of template files at “Print File”.
add a prefix to select spreadsheet template file easier
If you add the whole name of the spreadsheet template file, in the example – statement, the report will be generated immediately after pressing the “Print” button, without the need to select any template file.

Multiple Spreadsheet Report Templates

As described in the previous page, you can save up the report data as an entry in the worksheet for future reference. Another reason to save up the report entry is that you can print the data using more than one report template.
create multiple template files for a report
One of the funs here is that you can create multiple report templates for the same set of report data. It’s common that you have to present the same set of data to different groups of people, who have different concerns and interests about the data they would like to see.
You can create multiple spreadsheet template files with different layouts, cell captions of report data, dot dot commands and Excel formulas, so that you can easily present the same set of data for different purposes.