6.1 Printing a Report
6.1 Printing a ReportClick on the report writer button. Select the source of the data, and then select the report you want to print.
Check that the where conditions are as you want them (where conditions are discussed later), and then press the print button.
6.2
Creating Your Own ReportsClick on the report writer button. Select a data source from which to get the data and then click on the new button.
Each row in the report writer screen sets up a column in the report. The left hand side of a row (column, width, alignment) displays the columns you want to display. The right hand side (header, sum, sort, where conditions) displays the criteria.
When you build your report it is best to add one item at a time. This helps find problems easier (therefore it will most likely be the last field added that has caused a problem). To use this method of building a report you simply add one column at a time, viewing the results as you go (using preview).
Report Name
In the report name box, enter a name for the report being created. This will be the title for the printed report.
Column
Under the column heading, click on the first (at the top) drop down box. Select an item (field) from the drop down list. It is a good idea to select the first field according to the way you want the data to be sorted. In the example, the first field is employee name which will govern the order of all the data.
Width
This is the width of the displayed column. As a general rule, the width should be the number of characters being display. However, you must take into account that some characters are wider, or thinner than others (eg. i and w). In the example above, only the first twenty or so characters of the employee name will appear on the printed report. Even though only the first 20 characters are displayed, the full name will be used for sorting and comparison (if they are selected).
Align
The align field drop down box selects the alignment (justification) of the field as it will appear on the printed report. Numbers (including currency) look best right aligned, and text looks best left aligned.
Header
The header check box changes the appearance of the report by grouping together all the items within a column which are the same. Each grouped item appears as a subtitle. If you want to use one level of grouping then it must be the first field. If you want two levels of grouping then they must be the first two fields. See advanced functions for more details on grouping (below).
Sum
The sum tick box allows a printed subtotal to appear at the end of every grouped section. If there are no groupings, then a total is displayed at the end of the report.
Sort
The sort order is selected using the sort drop down box. By default, fields have no order (the order they are stored in the database is used). Select ascending if you want the order to be from A to Z, smallest to largest. Descending reverses the order.
Where Condition
The where condition allows you to customise your reports even more. By changing the where condition for a column, you can have certain records to be included or filtered out. Leaving the where condition blank means that all records found will be displayed. On the example above, the where condition for employee has been left blank. This means that all employees will be displayed.
To add a where condition, you find the column in which to perform the where condition and click on the corresponding where condition drop down box. Select the operator to use (see advanced functions for more about where conditions and operators). In the next box type in the criteria. The example above shows that the report should only display records that are between the dates 1/5/2006 and 31/10/2006.
Where conditions can be used with letter fields such as names. The "Like" where condition allows you to select records using wild cards such as "*" and "?". For example, to select names beginning with "R" use "Like R*".
For more information and examples, see the advanced functions section below.
Report Writer Buttons
 |
Add New. Click here to start a new report. |
 |
Delete. Click here to delete the report. |
 |
Page Setup. Click on the page setup button to show the report writer page setup screen. You can alter the margins, paper size, and orientation (to print sideways, choose landscape). Click the close button to save the settings. |
 |
Print Preview. You can preview the report on screen by clicking the print preview button. You can use this to test your report before you print. |
 |
Print. When satisfied with the report layout, click on the print button to print it out. |
 |
Spreadsheet. If you want to copy the data to a spreadsheet click on the spreadsheet button. The data will appear in a table format, similar to a spreadsheet. Select the portion you wish to copy (click on the top left small grey button to select the whole table). Right click on the selected section and choose copy from the pop up menu. Alternatively, you could press CTRL + C. The selection will be copied to the windows clipboard ready to paste into another application. Open the spreadsheet and press CTRL + V to paste the clipboard onto the spreadsheet. |
Your report writer setup (or template) is saved as soon as the report writer form is closed.
6.3
Advanced FunctionsIn your reports you can have blank columns. Blank columns are used to help readability. To insert a blank column you simply select <SPACE> from the column drop down box, which is the first item in the column drop down box. You can adjust the width of the blank column to increase or decrease its width.
Sometimes you may want to display a report that searches a column on specific criteria but do not want to display that column. If this is the case, then you simply set the width to zero. An example is the invoices outstanding report, which selects records with a status of outstanding, but does not display them.
Headings are useful for grouping items. There are three different levels of headers (discussed below); no headers, one level of headers, and two levels of headers.
No Headers
In this example we are searching for all customers who have generated sales between the dates of 1/5/2006 and 31/10/2006. We are looking for invoices with a status greater than incomplete (this means all invoices that are either outstanding or paid). We are sorting the records by company first, and then date. The conditions used above will produce the following report.
Notice that there is no grouping. The report has produced a list of companies, and given a total for all companies.
One Header
What if we want to see how much sales each customer has generated? We need to group the report by company. This is done by ticking the header tick box associated to company.
As with the no header example earlier, we are searching for all companies who have generated sales between the dates of 1/5/2006 and 31/10/2006. This time however the first header box is ticked. This will group the output by company, providing a better breakdown of sales by customer. The conditions used above will produce the following report (note: this is a fragment of the report).
Now we can examine the amount of sales generated by all of our customers. We can, however, narrow our output down even more by having two headers.
Two Headers
We can break the report down even further by selecting the second header tick box. This will not only group the report by company, but also by date. This would be useful if you had a lot of sales per day, and wanted to analyse how much a customer spends per day. The criteria shown above will produce the following report (note: this is a fragment of the report).
It is recommended that you experiment with the report writer, to get a better understanding of how to group items in a report. Keep things as simple as possible at first, and work your way towards more complex reports when you feel you have the hang of it.
There are many different operators that can be used to filter your data. Each one is explained below.
Blank
Having a blank where condition means that nothing will be filtered. All information for that column will be displayed.
The equals operator means show all records that are exactly equal to the criteria entered. The following example would display all contacts whose name is exactly equal to Colonial Cottages Ltd.
This is the greater than operator. It filters records that are greater than the criteria entered. The example below would display all selling price amounts which are greater than five.
The less than operator works the opposite way. It filters all records that are less than the criteria entered. The following example would display all selling price amounts which are less than five.
The greater than or equal to operator shows all records that are greater than, or equal to the criteria entered. The example below would display all selling price amounts which are greater than, or equal to five.
<=
The less than or equal to operator shows all records that are less than, or equal to the criteria entered. The following example would display all selling price amounts which are less than, or equal to five.
This is the does not equals operator. It shows all records that do not equal the criteria entered. The example below would display all contacts that are not called Norksgrove's Print Shop.
Like
The like operator searches for values that are like the criteria you specify. Using a wildcard (*) will find a range of values (eg. sm* would search for contacts whose name begins with sm, sM, Ms, or SM). The following example would display all contacts whose name begins with Nor.
Between
The between operator shows all records that are between the criteria entered. The example below would display all records that have a date between 1/1/2007 and 31/1/2007.
