Creating a Report Generator Report - Adding Searches
This page describes adding searches to Report Generator reports so that they only list selected records instead of every record in a particular register.
---
When designing Report Generator report, you will often need to add searches. If you add a search, the report will only list the records that match the search criterion, not every record in the primary register. Searches can be automatic, or they can be controlled by the person producing the report, when they specify what to search for. On this page, we will first describe an automatic search, and then a user-controlled search.
The example report that we are using in this documentation is entitled "Customer List (All Customers)", but at the moment it will list every record in the Contact register, including Suppliers and Contact Persons as well as Customers. You can add an automatic search to remove the Suppliers and Contact Persons from the report. Follow these steps:
- Return to the Report Definition record and go to the 'Data' card.
- Click on the "Register" line in the report display area (marked "Register: Contacts..." in the example) to specify that the search is to be carried out in that register, and then click the [Selection] button above the report display area. The 'Selection' dialogue box opens:
The 'Selection' dialogue box is divided into two sections. In this example, as the report is entitled "Customer List (All Customers)", it can be assumed that the person producing the report is doing so because they want a list of Customers only. So, there is no need explicitly to ask whether non-Customers should be removed from the report. The search can therefore be automatic, so you can ignore the lower six fields in the window. You only need use the first three fields to define an automatic search (you can use the other fields to place a variable in the specification window, as described later on this page).
- Field Name
- Paste Special
Fields in the register
- Choose the field that is to be the subject of the search. The 'Paste Special' list will show all the fields in the register you are working with (in the example, the Contact register).
- When the report is produced, there will be a search in the field specified here for the value that you enter in the Value field below. In the example report, the CUType field is the subject of the search (CUType is the internal name for the Customer check box in the header of each Contact record), and so this is the field that should be specified here.
- Formula, Value
- Use one of these fields (but not both) to specify what you want to search for. In other words, specify in one of these fields the value that must be present in the Field specified above in order for a record to be included in the report.
- If you want to search for a certain value, enter that value in the Value field. If you want to search for the contents of a variable or another field, enter that variable or field in the Formula field.
- In the example, you need Contact records that have been marked as Customers to be listed in the report. This means Contact records in which the Customer check box has been ticked. When this check box is ticked in a particular Contact record, the value "1" will be stored in the CUType field in that record. You should therefore enter "1" as the Value, so that there will be a search to find all Contacts where CUType is 1.
If you need the report to list records in the Contact register that are not Customers, you could enter "0" as the Value (the report will list Contacts in which CUType is 0), or you could enter "1" as the Value and select the Not option (the report will list Contacts in which CUType is not 1).
- When the 'Selection' dialogue box is complete, click the [OK] button to close it. The information that you entered will be placed in a second line in the report display area, marked "Selection:":
This search for Customers in the Contact register is an automatic one: it will always take place and does not need to be activated by the person producing the report.
You can also add a search that can be controlled by the person producing the report. This person can specify that a search will take place, and they will also specify the value that will be searched for. For example, it might be useful if they could produce a list of Customers belonging to a certain Category.
To do this, you need to place a Category variable in the report specification window, and to program the report to perform a search based on what the person producing the report enters in this variable. You can accomplish these two tasks together in the 'Selection' dialogue box. Continue with these steps:
- Return to the Report Definition record and go to the 'Data' card.
- Click on the "Register" line in the report display area (marked "Register: Contacts..." in the example) to specify that the search is to be carried out in that register, and then click the [Selection] button above the report display area. The 'Selection' dialogue box opens:
In this example, you will use the lower six fields in the 'Selection' dialogue box to name the variable and place it in the specification window. As in the previous example, you will use the first three fields (and, if necessary, the Not check box) to define the search.
- Field Name
- Paste Special
Fields in the register
- Choose the field that is to be the subject of the search. The 'Paste Special' list will show all the fields in the register you are working with (in the example, the Contact register).
- When the report is produced, there will be a search in the field specified here for the value that the person producing the report enters in the variable in the specification window. In the example report, the Customer Category field is the subject of the search, and so this is the field that should be specified here.
- Formula
- Formula is not used with user-controlled searches.
- Value
- If you want a default value to be placed in the variable when the specification window opens, specify that default value here.
- Not
- Select this option if you want to perform an "inverse" search, i.e. if you want to search for records that do not match what was entered in the specification window. For example, you can use this option to search for all Customers except those in the Category specified in the specification window.
- Input Label
- Enter the name of the variable, as it will appear in the specification window (as illustrated below). The Label should indicate to the person producing the report what they should type in to the variable.
- Variable Name
- Enter a name for the variable that will hold the search criterion (what will be searched for). Include at least one alpha character in the name and do not use spaces or punctuation marks of any kind. Use the underscore _ instead of a space. Ideally, the variable name should indicate the purpose of the variable.
- The person producing the report will enter their search criterion in this variable in the specification window. When they click the [Run] button, there will be a search in the Field Name that you specified above for records that match this search criterion. In the example there will be a search to find Customers with a Category that matches what the user types in this variable in the specification window.
- Paste Register
- Paste Special
Registers in Standard ERP
- If you want the person producing the report to be able to use 'Paste Special' to bring a value into the variable, specify here the register whose contents are to appear in the 'Paste Special' list.
- It can be useful to enter a block here, rather than a register. Blocks are not included in the 'Paste Special' list attached to this field, but you can open a selection list of useful blocks by clicking the [Paste Window] button. The blocks in this list are:
- PerSClass
- Reporting Periods setting
- VATCodeSClass
- VAT Codes setting
- PasteCurDate
- opens 'Paste Date' window
- LandSClass
- Languages setting
- PMSClass
- Payment Modes setting
- For example, if you want the person producing the report to choose a VAT Code as a reporting criterion, click the [Paste Window] button and double-click "VATCodeSClass".
- Width
- Specify here in pixels how wide the variable should be when it is placed in the specification window. Ideally, the width should reflect the number of characters that should be entered in the variable. The default is -1, which means the variable will take up the entire width of the specification window, as shown in the illustration above.
- h, v
- Use these two fields to specify where you want the variable to be placed in the specification window. Enter co-ordinates (in pixels) for the top left-hand corner of the variable (not the label): h (horizontal) is the distance from the left-hand edge of the specification window, while v (vertical) is the distance from the top edge. Defaults are offered: they assume the usual Standard ERP vertical spacing of 20 pixels between variables.
The example illustrated below will place a variable named vsCategory in the specification window. The person producing the report will type a Customer Category Code (or range of Codes separated by a colon) in this variable, or select a Category Code using the 'Paste Special' link specified in the Paste Register field. After the search described earlier in this section for Customers in the Contact register (i.e. for records where CUType is 1), there will then be a second search for Customers whose Category matches exactly what was entered in the vsCategory variable ("CustCat" is the internal name for the Customer Category field in the Contact register). The search is case-sensitive. If the vsCategory variable in the specification window is left empty, all Customers will be listed in the report.
- When the 'Selection' dialogue box is complete, click the [OK] button to close it. The information that you entered will be placed in a third line in the report display area, marked "Selection:":
The order of the two "Selection:" lines is not important: both searches will be carried out. The report will only list records where CUType is 1 and where the Customer Category matches what the person producing the report enters in the specification window.
Having added a search, please follow the links below for more details about:
---
Creating a Report Generator Report:
Go back to:
Creating a Report Using the Report Generator
