reinnovate 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:
create a new report worksheet with a single click
You can change the worksheet caption in Worksheet Setting:
Go to Worksheet Setting to change worksheet caption
change caption of the report worksheet
The new report worksheet is created and is ready for adding content on it.
new report worksheet created

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.
select date range of database data to be retrieved into report.
This is the setting of the cell “By This Date”. Change the caption, select “TextCmb” and add the formula:
cell setting of the cell By This Date
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:

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.
built-in database table - tblCalendar
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.
use go button to start retrieving report data
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.
setting of Go button

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.
invoice records are retrieved onto the 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.
setting of BulkReport
setting of BulkReport
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.
summing up the retrieved report data
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.
CountDistinct counts the distinct number of customers
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.
Count counts the number of retrieved records
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.
Sum calculates the total of amount

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.
save up the report data as an record entry
print the report data on different Excel spreadsheet template.