Excel Spreadsheet Reporting
> Retrieve Report Data from Database
Retrieve Report Data from Database
It’s simple to create report with Excel spreadsheet in BAU DB. Before moving data to a formatted report template on Excel spreadsheet, the first step is to create a BAU DB worksheet that retrieves report data from database. Let’s illustrate the steps of creating a report with an example customer statement report, which retrieves records from Invoice worksheet and consolidates the invoice records of multiple clients.
Create Report Worksheet
First thing first, create a new worksheet for collecting report data from Invoice worksheet. It’s simply done by a single click in Main Menu:
You can change the worksheet caption in Worksheet Setting:
The new report worksheet is created and is ready for adding content on it.
Select Report Data Range
When a user want to generate a report, the user shall be able to select the desired range of data to be displayed in the report. Some cells can be set up in the worksheet for entering values that are used to limit the data of a report.
In the customer statement, we can set up a cell “By This Date” to let user enter a date value so that only invoice records before this date value will be retrieved from the database.
In the customer statement, we can set up a cell “By This Date” to let user enter a date value so that only invoice records before this date value will be retrieved from the database.
This is the setting of the cell “By This Date”. Change the caption, select “TextCmb” and add the formula:
Let’s dwell on something fun here. Since the statement report is generated on monthly basis, the selection list of this cell shows only date values of month-ends.
Formula for the selection list:
Formula for the selection list:
Multi;;450|2000;Select DateValue(Format(DateAdd("m",Day,Now),"yyyy/mm/01"))-1 as Month from tblCalendar where Day>-20 and Day<2 order by Day desc
This formula utilizes a built-in database table tblCalendar which has only one field Day with integer values from -31 to 31. This build-in table is created automatically and resides in each database file, which is useful and convenient for building formula related with date value.
The month-end values in the selection list are generated from this part of the formula DateValue(Format(DateAdd(“m”,Day,Now),”yyyy/mm/01″))-1. Inside that, Now is a formula function which returns today’s date value. DateAdd(“m”,Day,Now) uses the values of the database field Day to add Day x Months, with “m” in the formula means Month, to today’s date. While Format(…, “yyyy/mm/01”) turns each date value into the first date of its month, DateValue(Format(…,..))-1 turns it into a date value, and then minus 1 turns each date value into a month-end, i.e. the day before the first day of the month is the month-end of the previous month.
“Go” Button
“Go” button is actually a cell for the sole purpose of triggering the retrieving of data from database, after data range selection is completed. In the formulas, we can disable the triggering of formulas by those data range cells, and leave alone the button cell to trigger the formulas.
Below is the setting of the button cell. The setting of “Button” in Cell Nature is just to make it looks like a button. It acts like a normal cell so that you can press Enter of keyboard in this cell to trigger the execution of formulas that retrieve report data. You can place any words in Initial Value as the “caption” of the button.
You will know how “Go” button works in the next section.
You will know how “Go” button works in the next section.
Formula to Retrieve Data from Database
The next step is to add the formulas for retrieving report data into a set of multiple value cells. In this example, invoice records from Invoice worksheet are retrieved onto the report worksheet.
To retrieve a large set of records, “BulkReport” is used and formulas are added to each of the multiple value cells. The formulas among the cells are only different in the retrieving database fields, i.e. the “Select” part of the formulas.
Customer :
Select CustName from tblInv where Status="OUTS" and InvDate< =datevalue(~'b.A') and 'b.B'="Go>>" Order by CustName, Invdate
Invoice :
Select InvNo from tblInv where Status="OUTS" and InvDate< =datevalue(~'b.A') and 'b.B'="Go>>" Order by CustName, Invdate
Date :
Select InvDate from tblInv where Status="OUTS" and InvDate< =datevalue(~'b.A') and 'b.B'="Go>>" Order by CustName, Invdate
Amount :
Select TotalAmt from tblInv where Status="OUTS" and InvDate< =datevalue(~'b.A') and 'b.B'="Go>>" Order by CustName, Invdate
In the example, report data range is controlled by the cell ‘b.A’. The condition InvDate< =datevalue(~'b.A') lets only invoice records with invoice date earlier than or equal to the date value in the cell to be displayed.
The “~” sign disables triggering of formula by the cell ‘b.A’ – “By This Date”, and lets the formula to be triggered only by the cell ‘b.B’ – “Go” button, the condition ‘b.B’=”Go>>” is always true and this is how the “Go” button works.
Since only “Outstanding” invoices are to be retrieved in a customer statement, the condition clause where Status=”OUTS” is added. To list the invoice records and group together by customer, Order by CustName, InvDate is used.
Total Counting Cells
In the report worksheet, you can add some cells to calculate summary information of the retrieved records.
No. of Customers
This cell counts the distinct number of customers in the retrieved data. It uses “CountDistinct” in Calculation Type and add the database field b1.A, which is the “Customer” cell.
This cell counts the distinct number of customers in the retrieved data. It uses “CountDistinct” in Calculation Type and add the database field b1.A, which is the “Customer” cell.
No. of Invoices
This cell counts the number of invoice records, which is also the number of records retrieved. Select “Count” in Calculation Type and add the database field b1.B, which is the “Invoice” cell.
This cell counts the number of invoice records, which is also the number of records retrieved. Select “Count” in Calculation Type and add the database field b1.B, which is the “Invoice” cell.
Total Amount
This cell sums up the total of invoice amount. Select “Sum” in Calculation Type and add the database field b1.D, which is the “Amount” cell.
This cell sums up the total of invoice amount. Select “Sum” in Calculation Type and add the database field b1.D, which is the “Amount” cell.
Save Up Report Data
After retrieving the reporting data, it’s just convenient to save up the data as a record entry in the worksheet and leave it here for future reference. This feature is also the fun part of BAU DB, which allows you to use the same set of report data to be printed on different Excel spreadsheet report templates.
