Excel Spreadsheet Reporting
> Print Report Data on Excel Spreadsheet
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:
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:
Cell values are the cell captions on BAU DB worksheet enclosed with double-colons:
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 always start with 2 dots in front of the keyword of the commands, so that you can also call them dot-dot commands:
Excel Formulas
Embedding Excel formulas in the report is a convenient way to enrich the report content:
Embedding Excel formulas in the report is a convenient way to enrich the report content:
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.
A cell value can be concatenated with other texts in a cell in the 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.
..formatMultiPreprint
..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.
..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
..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.
..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.
..LoopBottom
..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.
..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.
..LastPage
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.
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
..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.
..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.
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.
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 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.
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.
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.
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”.
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.
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.