On the Main Menu screen, hover over the "Reports" menu option, then click the "Report Writer" button. You will see the following screen:-
You can either start with an existing report by selecting the "Data Source" and "Report" type and clicking "Open", or you can click the "New" button to create a new report from scratch. If you're new to the Report Writer, it's recommended that you start by opening an existing report to understand how it all comes together. The next screen you see will look something like this:-
Each row in the report writer screen sets up a column in the report. The left hand side of a row ("Column", "Width" and "Alignment") displays the columns you want to include on your report. The right hand side ("Header", "Sum", "Sort" and "Where Conditions") displays the criteria.
If you have started with a new report from scratch, it is best to add one item at a time. This helps you find problems easier (therefore it will most likely be the last field added that has caused a problem). To do this, you simply click on the dropdown menu under the "Column" section to add a new item to your report, viewing the results as you go (using "Preview").
The example above includes two 'where conditions', as follows:-
To add a 'where condition' under the "Where data meets the following criteria" section, select the 'operator' to use and then the box to its right will be enabled, allowing you to enter the criteria for your 'where condition'.
Tip! '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 - Click here to create a new report.|
|Delete - Click here to delete a report.|
|Setup - Click on the "Setup" button to show the Page Setup. You can alter the margins, paper size, and orientation (to print sideways, choose landscape).|
|Preview - You can print preview the report on screen by clicking the "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.|
|Sheet (Spreadsheet View) - If you want to copy the data to a spreadsheet click on the "Sheet" button. The data will appear in a spreadsheet format. You can copy and paste the data from here into another program such as Microsoft Excel by selecting the data with "CTRL" + "A", then copying it with "CTRL" + "C", and finally pasting it where you want it using "CTRL" + "V".|
Tip! Any changes you make to your report will be saved automatically when you close the report.
In 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" dropdown menu. You can adjust the "Width" amount 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 "0" (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.
In this example we are searching for all customers who have generated sales between the dates of 1/09/2014 and 31/03/2015. 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.
Let's say we'd like to know how much sales each customer has generated. For that, 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/09/2014 and 31/03/2015. 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.
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.
If you don't specify any 'where conditions' under the "Where data meets the following criteria" section, then nothing will be filtered (i.e. all information for that column will be displayed). However, there are many different operators that can be used to filter your data. Each one is explained below.
The 'equals' operator means: 'show all records that are exactly equal to the criteria entered'. The example below would display all "Clients" whose name is exactly equal to "Colonial Cottages Ltd".
The 'greater than' operator means: 'filter all records that are greater than the criteria entered'. The example below would display all "Sell Price" amounts which are greater than "5".
The 'less than' operator means: 'filter all records that are less than the criteria entered'. The example below would display all "Sell Price" amounts which are less than "5".
The 'greater than or equal to' operator means: 'show all records that are greater than, or equal to the criteria entered'. The example below would display all "Sell Price" amounts which are greater than, or equal to "5".
The 'less than or equal to' operator means: 'show all records that are less than, or equal to the criteria entered'. The example below would display all "Sell Price" amounts which are less than, or equal to "5".
The 'does not equal' operator means: 'show all records that do not equal the criteria entered'. The example below would display all "Clients" that are not called "Norksgrove's Print Shop".
The 'like' operator means: 'show all values that are like the criteria you specify'. Using a wildcard (*) will find a range of values (e.g. 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".
The 'between' operator means: 'show all records that are between the criteria entered'. The example below would display all records that have a "Date" between 1/01/2014 and 31/01/2014.
When you're finished setting up your report, click on the "Preview" (print preview) button on the toolbar to check it and confirm that all your 'where conditions' are set up properly. When you're satisfied, click the "Print" button on the toolbar to print it out.
In Version 6 (or newer), reports can be exported/emailed as a PDF (just like invoices, statements, quotes, etc.). To do this, you first need to click the "Preview" button on the toolbar to do a print preview. Then you can either right click on the preview using your mouse and choose "Export / Email as PDF" or you can click the "Export" button on the very top toolbar. Next, the "Select Recipients" screen will appear allowing you to select the people who are to get a copy of the email. You can click "New" to add a new email recipient if you wish. When you've ticked all the recipients you want to include, click the "Send PDF" button and the following "Email PDF" screen will appear:-
From this screen, you can:-