In this topic we shall try to cover the most frequently asked questions, in a "user friendly" way, which will help you to use Ad-hoc enquiries.
Click on the question you would like to know about.
Before using the system you will need to have an idea of which fields you would like to display on the report and what your selection criteria is. Once you have decided this then you can start building the report.
Click the Verbs... button. Select the output of your report. It is a good idea to select the DISPLAY option at first. This way you can display the report and keep modifying it until you are happy with it and then change it from DISPLAY to PRINT. When you have selected the required Verb, click the OK button.
Note: The Ad-hoc enquiries form defaults to DISPLAY when you first go into it.
Click the Fields... button. A list of the required fields within the file is displayed. The first column is the short code for the field, the second is the full description of the field and the third column indicates what type of field it is. C = normal alpha/numeric field, D = date field and N = numeric field. If there is an asterisk then this indicates that there is more than one occurrence of the field.
Click on each field you require. The field is highlighted. Use the scrollbar to view more fields. When you have selected all required fields click the OK button. The fields which were selected will now be displayed in the Formulate query section of the form.
Click the Fields... button again and click the field which was selected by mistake. It will no longer be highlighted and when you click the OK button the field is removed from the Formulate query section.
Click the If... button. The Build selection form is displayed.
You can either type in the field name, or click on the Ellipsis button. Select the required field by clicking on it and selecting the OK button or alternately double click on the highlighted line.
Tip : Click on the Ellipsis button
to search.
After a field has been selected, click in the Operator box, and a Drop Down button will appear. Click the Drop Down button and select the appropriate operator criteria (reserved words) by clicking on it or by using the <up> and <down> arrow keys.
The reserved words are as follows:
|
<> not equals |
>= greater than or equals |
<= less than or equal |
|
> greater than |
< less than |
= equals |
|
co contains |
|
|
Tip : Click on the Drop down
to select.
After selecting an operator, you must enter a comparison.
If more criteria are required for the selection then you can click the And... or or... buttons and follow the above process.
Example 1 for Sales: If you would like to select all targets which have a source of business code of ADV then your selection would read:
IF SOURCE = ADV
Example 1 for Finance: If you would like to select all customer accounts which have an account type of V then your selection would read:
IF ACTYPE = V
Example 2 for Aftersales: If you would like to select all parts with a bin location of ABC123 then your selection would read:
IF BIN = ABC123
Example 2 for Sales: If you would like to select all companies which have a postcode beginning with RG12 then your selection would read:
IF POSTCODE co RG12
Example 2 for Accounts: If you would like to select all customers which have a postcode beginning with RG12 then your selection would read:
IF POSTCODE co RG12
Example 2 for Aftersales: If you would to select all parts with a description containing the word 'spark' then your selection would read:
IF DESC co SPARK
To enter your next line of selection criteria you will need to click either the and... button or the or... button. In both cases the Build Selection form is displayed again and you will need to follow the same process of selecting a field, a reserved word and a comparison.
Example 1 for Sales: IF MODEL = MONDEO AND COLOUR co BLUE
This would find all vehicles with a model code of Mondeo and the colour description containing blue.
Example 1 for Accounts: IF ACTYPE = V AND POSTCODE co RG12
This would find all accounts with an account type of 'V' and the postcode contains RG12.
Example 1 for Aftersales: IF BIN = ABC123 AND DESC co SPARK
This would find all parts with a bin location of 'ABC123' and the description contains 'spark'.
Example 2 for Sales: IF MODEL = MONDEO OR COLOUR co BLUE
In this case the system will find all vehicles with a model code of Mondeo regardless of the colour and it will also find all the vehicles which are blue.
Example 2 for Accounts: IF ACTYPE = V OR POSTCODE co RG12
In this case the system will find all accounts with an account type of 'V' regardless of the postcode and it will also find all the accounts with a postcode containing RG12.
Example 2 for Aftersales: IF BIN = ABC123 OR DESC co SPARK
This would find all parts with a bin location of 'ABC123' regardless of the description and it will also find all parts with a description containing 'spark'.
By default the system will display the report in the quickest way by blasting down the file.
However, you can change the sequence of the records. One way is to use the Keys... button. This will display the "Key-paths" to the file. Selecting one of these will also mean that the system can display the report very quickly. The Key-paths form will display the fields it will sort by. In some cases there is more than one field, which means it sorts in the order of the first field, then the second field and so on. If you wish to use a key-path then click the appropriate line and click the OK button.
You can also sort using the command SORT which, by itself, will sort in the order of the first field in your report. Using SORT= requires you to type a field name with it and it will sort by this field.
Example for Sales: If you would like to sort in registration number order and registration is the first field in the report then you can just use the SORT command. But if it is not the first field in the report then you must use SORT=REGNO.
Example for Accounts: If you would like to sort in account number order and account number is the first field in the report then you can just use the SORT command. But if it is not the first field in the report then you must use SORT=ACCOUNT.
Example for Aftersales: If you would like to sort in part number order and part number is the first field in the report then you can just use the SORT command. But if it is not the first field in the report then you must use SORT=PART.
To use one of these commands click the Commands button and click the appropriate line from the list and click the OK button. The command is added to your query. If you have selected the SORT= command then you will need to type the field name after it.
The system defaults to print the report in forward order. To change it to print backwards click the Commands... button, click the BACKWARDS option and click OK .
Click the Check Query button. The "Execute query" section will display it's interpretation of the query, the sort order and the date format. Click the Proceed button. If you chose the DISPLAY verb then the query is displayed on the form. If you chose the PRINT verb the system will ask you which printer you wish to print to and if you click the OK button the report is printed.
Click the Close button and delete the word DISPLAY. Type the word PRINT and click the Check Query button followed by the Proceed button. Select the printer and click the OK button. The report is printed.
Yes. Instead of using the verb DISPLAY or PRINT in the query enter the verb SPREAD. After you have clicked Check Query and Proceed the output will open up Excel and transfer your data into a new spreadsheet.
The system will normally print the date in the format of DD/MM/YY (e.g. 30/11/97). If you would like the date to printed in a different format then click the Commands… button and select one of the following:
DATE10 : DD/MM/YYYY (e.g. 30/11/1997)
DATED : Day only (e.g. 30)
DATEM : Month only (e.g. 11)
DATEY : Year only (e.g. 97)
DATEYM : YYMM (e.g. 9711)
DATEMD : MMDD (e.g. 1130)
DATEF : Full date(e.g. 30th November1997)
Yes. Click the Verbs... button, click the GRAPH option and click the OK button. Next select the fields you would like to be included in the graph. The format of your query should read:
GRAPH FIELD1 FIELD2 (FIELD3) IF …
FIELD1 is the field to be displayed along the X-axis, FIELD2 is the field to be displayed along the Y-axis and FIELD3 is the field to be displayed along the Z-axis. It is not necessary to use a third field.
Yes. Click the Verbs… button, click the LABEL option and click OK. Next select the fields which you would like to print on the label. You must then type the word DIMENSION followed by the dimensions of your labels in angle brackets.
Example for Sales: If you would like to print the name and address of customers from the target file and your labels are two across the page then your query may look like this:
LABEL TITLENAM ADDRESS(1) ADDRESS(2) ADDRESS(3) ADDRESS(4) ADDRESS(5) POSTCODE DIMENSION<50,2,8>
Example for Accounts: If you would like to print the name and address of customers from the customer accounts file and your labels are two across the page then your query may look like this:
LABEL NAME ADDRESS(1) ADDRESS(2) ADDRESS(3) ADDRESS(4) ADDRESS(5) POSTCODE DIMENSION<50,2,8>
The first dimension of 50 is the length of the label and it is the position where the system isgin to print the second label across the page. This dimension must be no shorter than the length of your longest field. The second dimension of 2 is how many labels across the page. The third dimension of 8 is the depth of the label and therefore the position where it isgan to print the next label down the page (i.e. 8 lines down from where it printed the first one). This dimension must be at least one more than the number of fields to be printed.
It is a good idea to test the printing of labels by just selecting to print a small number to check that every label will print in the correct position.
Yes, click the Commands... button, click the STOPATxx option and click OK . STOPATxx is displayed at the end of your query. Over type the 'xx' with the number of records you would like to display or print.
Example for Sales: PRINT MAGIC DESC REGNO STOPAT10
This will print the first 10 vehicle records it finds.
Example for Finance: PRINT NAME ADDRESS(1) POSTCODE STOPAT20
This will print the first 20 vehicle records it finds.
Example for Aftersales: PRINT PART DESC REORDER STOPAT20
This will print the first 20 part records it finds.
Yes, instead of listing all the records which match your selection criteria it is possible to just print or display the total value. Click the Verbs... button, click the TOTAL option and click OK
Example for Sales: TOTAL LABLTD IF MODEL = MONDEO
In this example, using the Vehicles & drivers file, the system will find all the Mondeo vehicles and just display one total value for the labour life to date sales of all the vehicles.
Example for Finance: TOTAL BASETYTD IF ACTYPE = V
In this example, using the customer accounts file, the system will find all the account types of 'V' and just display one total value for the year to date turnover of all the accounts.
Example for Aftersales: TOTAL AVGCOST IF REORDER = A
In this example, using the parts stock file, the system will find all parts with a reorder category of 'A' and just display one total value of the average cost of all the parts.
Yes, click the Commands... button, click the BREAKON option and click OK . The system will break on the first field in the query and subtotal any value fields included in the query.
Example for Sales: MODEL LABLTD BREAKON
In this example, using the Vehicles & drivers file, the system will list the all the vehicles in the file with their labour life to date sales and will also display a subtotal for each model.
Example for Acounts: ACTYPE BASETYTD BREAKON
In this example, using the Customer accounts file, the system will list all the accounts in the file with their year to date turnover and will also display a subtotal for each account type.
Example for Aftersales: REORDER AVGCOST BREAKON
In this example, using the Parts stock file, the system will list all the parts in the file with their average cost and will also display a subtotal for each account type.
It is also possible to do the above and print one page per account. To do this click the Commands... button, click the PAGEBREAKON option and click OK . The system will break on the first field in the query and subtotal any value fields included in the query and throw a page break after each subtotal. This can only be used with the PRINT verb.
Example for Sales: PRINT MODEL LABLTD PAGEBREAKON
In this example the system will list the labour life to date sales of all the vehicles with a subtotal per model and then the next page will display the next model and so on.
Example for Accounts: PRINT ACTYPE BASETYTD PAGEBREAKON
In this example the system will list the year to date turnover of all the accounts with a subtotal per account type and then the next page will display the next account type and so on.
Example for Aftersales: PRINT REORDER AVGCOST PAGEBREAKON
In this example the system will list the average cost of all the accounts with a subtotal per reorder category and then the next page will display the next reorder category and so on.
It is also possible to use the BREAKON command with the SUPPRESS command. This will suppress the records and only display the subtotals. Click the Commands... button and click both the BREAKON> and the SUPPRESS option and click OK button.
Example for Sales: MODEL LABLTD SUPPRESS BREAKON
In this example the system will list only the subtotal of the labour life to date sales for each model and not each vehicle individually.
Example for Finance: ACTYPE BASETYTD SUPPRESS BREAKON
In this example the system will list only the subtotal of the year to date turnover for each account type and not each account individually.
Example for Aftersales: REORDER AVGCOST SUPPRESS BREAKON
In this example the system will list only the subtotal of the average cost for each reorder category and not each part individually.
When doing a query of the vehicle and target file you may wish to find all the people all own a certain type of vehicle. In some cases a person may own more than one vehicle but you may wish to only display that person once in the report.
You can use the NODUPS option to remove duplicates of the field you are sorting on.
Click the Commands... button, click the NODUPS option and click OK .
Example for Sales: 1MODEL 1REGNO 2MAGIC 2TITLENAM IF 1MODEL = MONDEO SORT=2MAGIC NODUPS
In this example the system will display the model, the registration number, the target number and the targets title, initials and surname if they own a vehicle with a model code of Mondeo, and the report is displayed in the order of the target number. With the NODUPS commands included, if the system finds more than one vehicle owned by one customer it will only display the first one it finds. Without the NODUPS command the system would display both vehicle records and therefore the targets name twice.
Example for Accounts: DAYBOOK ITEM POSTDATE ACCOUNT IF POSTDATE >= 01/01/1998 AND POSTDATE <= 31/01/1998 SORT=ACCOUNT NODUPS
In this example, using the General ledger transaction file, the system will display the daybook number, daybook item number, posting date and account number of transactions in January 1998, and the report is displayed in the order of the account number. With the NODUPS commands included, if the system finds more than one transaction per account number it will only display the first one it finds. Without the NODUPS command the system would display all January transactions for each account.
Some of the enquiries options use two or more files. A field prefixed with 1 are the fields from the first file, a field prefixed with 2 are from the second file, 3 from the third and so on.
Yes, when you are happy with your query and you would like to run this later or on a regular basis then you will want to save it. A query can either be saved as a ‘User’ or ‘Shared’ query. If you chose ‘Shared’ the query is put into a list that allows other users to use it. If you chose ‘User’, then only you is able to see that query and retrieve it using the Load and ‘User’ radio button option.
If you wish to save the query then click the Save... button. The SAVE QUERY form is displayed where you must enter a description for the query in the NAME box. The system defaults to saving this query for this User only but it is possible to click the Shared option which allow other users to retrieve this user query. Click OK and the query is saved as a User query.
Note: A Global query can only be saved by a consultant. Please consult your systems manager.
Queries are saved as either ‘User’ or ‘Shared’ queries. Depending on how they were saved depends on how you retrieve the query. To retrieve a user query click the User option in the Stored queries section and click the Load button. A window will display the available enquiries. If the word 'User' is displayed alongside the query then this query was previously saved by yourself. If the word 'Share' is displayed then this is a query which was previously saved as a shared query by yourself or another user. Click the required query and click OK . The details of the query is displayed on the form.
To retrieve a global query click the Global option in the STORED QUIRIES section and click the Load button. A form will display the available queries. Use the scrollbar if necessary to display more of the queries. Click the required query and click OK . The details of the query is displayed on the form.
Note: A Global query can only be saved by a consultant. Please consult your systems manager.
Be careful when deleting queries. If it is not one of your user queries then check with other operators.
Load the query in the normal way and click the Delete button. The query is now deleted.
