build apps by user

Other Spreadsheet Report Styles – Part 2

One Page One Row Report

In simple listing report, one page accommodates a number of rows of records. In contrast, One Page One Row style lets you format and print the content of each row of record in a single page. To continue with the previous example, let’s see how to print the invoice records in the worksheet into individual pages of invoices, instead of a listing.
one page one row report
To create this report style, add the dot dot command ..formatMultiPage in any cell on the spreadsheet to tell the program the reporting style is one page one row.
To print one page one row report, each row of record must has an unique identifier, and it is Invoice No in the invoice report. In any location on the spreadsheet, add the dot dot command ..GroupBy “Invoice”, in which Invoice is the cell caption in the worksheet.
You can then put the cell captions onto the spreadsheet and format the layout of the page that is for displaying a row of record. In the end of the page, you can add the dot dot command ..PageBreak to create a page break at the location.
In the output report, all pages are printed continuously in the same spreadsheet.
the output spreadsheet of one page one row report

One Page Multiple Row Report

In contrast to one page one row report, one page multiple row report allows the content of a single page to include more than one row of record. As in the example of invoice report above, when the report is grouped by Customer, instead of by Invoice, then multiple rows of invoice records of the same customer will fall into the same page.
a one page multiple rows invoice report
Like one page one row style, add ..formatMultiPage in any cell of the spreadsheet. Put the cell captions on the spreadsheet and format the layout of the report. Since this example is going to print out each invoice record on one page, ..PageBreak is added. In case you just need an invoice report grouped by customer, can ignore ..PageBreak.
add the report style and format the layout of the report
Since each page of the invoice report is grouped by customer, ..GroupBy “Customer” is used here. In the layout of the invoice report, there are multiple occurrence data – invoice no., date, amount etc, that will be put as a list in each page of the report. The dot dot command ..GroupBy here is location sensitive and must be put in the same row of the multiple occurrence cells of data.
add ..GroupBy at the same row to the multiple occurrence values
Combine with Excel Formula
You can use Excel formula to calculate the total invoice amount of each customer. Since Invoice amount is put in the cell E8, you can add this formula =Sum(E8:E9) in the cell E10. As the report is rolled out and expanded, the summation formula will calculates the amount of all the invoices of that customer.
combine using Excel formula in the report.
You may like to sum up the whole report and calculate the total invoice amount of all customers. Let’s add ..Footer in a row in the bottom of the spreadsheet. This dot dot command creates a footer section for the report and all data on and below that row will only appear in the end of the whole report.
..footer adds a footer section in the excel report
To sum up the whole columns of invoice amounts in the report, what need to do is to add ..Total in the same column of invoice amount in the footer section. As the report is expanded, a summation formula is added here to sum up all the cells in the column. calculate the total sum of a whole column in the footer section
In this example, the total invoice amount of each customer is also included in the same column, the summation result of ..Total as the grand total amount of the invoice report is doubled. What you really want to display in the spreadsheet is half of this summation result. Firstly, you can hide this “doubled” value by giving it a white color.
hide by coloring it white
In the cell below this one, add the formula =E13/2 to display the actual total invoice amount of the report.
the real answer is divide in half

Computer Readable (Fixed Width) Report

A report file can be used as the input of another computer program, and a commonly used computer readable file format is fixed width sequential file. A fixed width file is a data file, with each row of data (or record) having the same number of characters. In each row of record, no matter the actual length of the data in each field, the same field occupies the same number of character spaces.
This is a sample fixed-width file:
a fixed width file
Fixed-Width Environment in Excel Spreadsheet
You can create a “fixed width” environment on Excel spreadsheet easily, and when the report is printed out, you can simply choose the file type “PRN” in Save As to turn the spreadsheet into a fixed-width file.
The first step is to let all columns of the spreadsheet having the same width that is enough for holding one character. To do this, you can select the whole worksheet by clicking at the top left corner, and then set the width value of all columns to 1 – the width that holds one character.
set all column width of the Excel spreadsheet to 1
The width of a column is just enough for one character. If the width is too small, the text inside the cell will not be saved in the output file, and so you can use this way to hide a value if it is necessary. If the width is too wide, it will leave two spaces or more in the output file.
Add Replacing Cell Captions
You can then add replacing cell captions onto the spreadsheet in specific locations. For example, you shall place the cell caption of the first data field in the first column. If this data field contains a width of 25 characters, the cell caption of the second data field shall be placed in the contiguous location, which is the 26th column and so on.
place the values in the contiguous cell to the previous field.
The value in the cell of a Excel spreadsheet can be generally divided into 3 data types – text, date and numeric value. The methods of placing the cell captions of these 3 data types are different and are explained below.
Text Value
If the replacing cell caption is to place a text value onto the spreadsheet, the cell caption can simply be added directly in a single cell. The text value will be displayed in the spreadsheet and its characters will be spread across the field space in the output file automatically.
adding text field on the fixed width spreadsheet.
Date Value
When the replacing value is a date value, the cells for holding the date value have to be merged together in Excel, otherwise the date value will be hidden in the single cell and does not appear in the output file. If the date value occupies 10 characters, you have to merge 10 cells together and then enter the replacing cell caption.
merge the cells in the spreadsheet to hold the date value.
Numeric Value
If it is alright to have the numeric value left-aligned in the fixed-width file, the way of adding numeric value is the same as date value – by merging the cells that hold the value. Sometimes you might want the numeric value to be right-aligned in the fixed width file, and simply setting the cell on the spreadsheet to be right-aligned will not meet the purpose.
The nueric values are left-aligned in the fixed width file.
The method to do this is to put the replacing cell caption of the numeric value in a single cell first, and then use Excel formula to distribute each of its digits into the correct cell locations.
The first step is to put the cell caption in a single cell, let’s say the cell is AT3, and since this cell value is not expected to be appeared in the output file, you have to hide this cell up. The method to hide the cell is simply by making the column width to be smaller than 1, and Excel will not put anything inside the cell to the output file.
put cell caption in a cell and hide it up by setting column width smaller than 1.
The next step is to use Excel formula to distribute each digit of the numeric value into the corresponding cells. Let’s say the numeric value shall be displayed in the cells from AU3 to AZ3. These are the formula to be added in each of the cells:
AZ3 : =IF(LEN(AT3)<1, “”, LEFT(RIGHT(AT3,1),1))
AY3 : =IF(LEN(AT3)<2, “”, LEFT(RIGHT(AT3,2),1))
AX3 : =IF(LEN(AT3)<3, “”, LEFT(RIGHT(AT3,3),1))
AW3 : =IF(LEN(AT3)<4, “”, LEFT(RIGHT(AT3,4),1))
AV3 : =IF(LEN(AT3)<5, “”, LEFT(RIGHT(AT3,5),1))
AU3 : =IF(LEN(AT3)<6, “”, LEFT(RIGHT(AT3,6),1))
add formula to distribute the digits to each of the cells.
Generate Fixed Width File
When the spreadsheet report is printed out, you can easily turn it into a fixed width file by saving the spreadsheet in PRN format.
The generated spreadsheet report:
the spreadsheet report of a fixed width file.
In the generated spreadsheet report, go to File -> Save As. In Save As Type, select Formatted Text (Space delimited) (*.prn) and save the file.
select prn format in save as.
Then, you can use any text file editor to open the fixed-width file.
use any text editor to open the fixed-width file.