ASSISI AD-HOC REPORT GENERATOR

We have a catalogue of reports stored on our Support Portal which can be copied and pasted into your report generator and saved on your own practice server. Alternatively, you can discuss your report requirements with our Support Department. In the majority of cases, we already have similar reports in our extensive catalogue to those requested and we will be happy to tweak any of those reports to your own requirements.

You can also write your own queries. To make your queries more flexible, you can build tokens into a report to prompt for variable criteria each time that the report is run.

Once a query has been saved, it can be recalled and executed (run) as often as required. The results can be previewed on the screen, printed, exported to a spreadsheet, exported as a mailing list for a mail merge routine (see page 22 ) or saved to a Workflow list (see page 6).

To access the Ad-hoc Reports Generator, click the Reporting tile on the Assisi Desktop and click Ad-hoc Reporting.

To Execute (Run) an Existing Query:

Click Open.

Browse for the report in (usually the path is Users\A1\Assisi Documents\OneDrive\ Reports on your C: drive, but this can vary so contact Support if you are unsure of your Assisi Documents path) and click Open.
The query will be displayed in the query text box, at which point it can be edited, if required.
Click the Execute button to display the results on the screen.
Some queries require the user to enter variable values upon which the query is based. If this is the case, the values should be added at this point. When you run such a query, the following prompt screen will be displayed.

Default entries for prompted data may be embedded in the script > itself. If so, and this is the correct value, then click OK to > select. Overwrite and click OK if a different value is > required.
Prompted dates can be selected using the calendar or by clicking on > the different sections of the default date and overwriting them.
If the Like operator has been used in the query, this means that > part of the value can be typed in – the % symbol can be used as a > wildcard (ie a special symbol that stands for one or more > characters) before and/or after the text.
If the Equals operator is used, then the exact value needs to be > added.
In setting values for tick-boxes, generally 1=Yes and 0=No.
Once > the query has been run, the results will be shown on the bottom > half of the screen and the red symbol will revert to grey. The > records that meet the criteria will be counted and the number > displayed in the bottom right hand corner of the screen.

To Stop a Query From Running

You can stop a query from running by clicking the Stop button on the toolbar to the right of the Execute button. You will be prompted that the execution has been cancelled and only the records that were selected up until the Stop button was clicked will be displayed on the screen.

Printing and Exporting SQL Queries

The results of the query can be previewed, printed, exported to a spreadsheet or saved to a list. Execute the query and select one of the following options:-

Print Preview

Execute the script and click the Preview button on the toolbar. Confirm that the correct printer is selected and press Print. The preview will be displayed on the screen.

The results can be printed from here if required by clicking the print option in the top left hand corner.

Print

Once the script has been executed, the results can be printed directly to the printer by clicking the Print button on the toolbar. Confirm that the correct printer is selected and press Print.

Export to a Spreadsheet

Execute the script and click the Spreadsheet button on the toolbar. The results will be exported into a new spreadsheet document.

Merge

You can export the results of an ad-hoc report into a mail merge using a pre-prepared template. See notes on page 22.

Save to a List

You can search for clients/animals using an ad-hoc report and the results can be saved to a Workflow list. You can then display any of the List fields available for selection in the Workflow List Design screen. Make sure that either the customer or animal ID is included in the Select clause as this is what will identify the customers/animals on the list.

These column headings should be named CustomerRef or AnimalRef - or renamed, as follows:

SELECT Consultation, Animal AnimalRef, Owner CustomerRef

Execute the script and click the List button on the toolbar.

Select the list from the drop-down box, click Clear existing entries, if appropriate, and click Save. If you do clear existing entries, make sure that you have the correct list selected and that you are not overwriting a list that you actually wish to retain.

Building Your Own Queries

It will be helpful when building your own queries if you understand the structure of the database that stores your data.

Database Structure

Database	A database stores data in tables and can be a collection of tables. Your Assisi database is a collection of tables.
Table	A table consists of a list of records with each record having the same structure. Records are displayed in rows in a table.
Field	The data relating to each record is stored in ‘fields’. Each record has a fixed number of fields of a specific type. Fields are displayed in columns in a table.

Database

A database most often contains more than one table. Each table is identified by a name
(eg Customers). Each field is identified by a unique name within each table. This is preceded by the table name with a full stop separating the table and field names. There are no spaces in table and field names.

Customers.Surname is the surname field in the Customers table.

Tables and Fields

Fields contain the same type of data for every record.

The extract from the Customers table below displays five records (one row for each record) and seven fields (one column for each field), displaying their Customer Reference Number (CustomerRef), Surname, Forename, Title, Address Reference, Home Phone (Phone) and Mobile Phone (Mobile).

Customers’ addresses are stored in a different table called Addresses. The two tables are linked by the Address Reference field, ie where the Address Reference fields are the same in both tables, then that address belongs to that customer.

Tables can be joined for the purpose of extracting data, providing there is a common field in both tables linking the relevant record(s) in one table to the relevant record(s) in another, as in the Customers and Address tables. Such tables are called related tables. If you want to base your query on more than one table, then you will need to include a join in the query (see page 20 for instructions on adding joins). The type of join will vary according to how the data is linked. With this in mind, we have created some views that link tables for you – see Tables/Views list below.

To Build a Query

The first step in building a query is to determine which Table(s)/Views contain the data that you wish to query. You can use the built-in Report Builder to help you construct your query.

Selecting Table(s)/View(s)

Click the Build button on the toolbar. This will display the query builder screen.

On the left-hand side of the screen is the Tables/Views selection tool. This comprises of a choice between Tables and Views.
Click the plus sign to the left of either the Tables or Views to display a list of Tables or Views that are available for selection.

There are many tables in the Assisi database. Tables/Views that you are most likely to want to query are as follows:-

Most Commonly Used Tables/Views

TABLE/VIEW NAME	TABLE OR VIEW	DESCRIPTION
AnimalSearchDetails	View	Joins the Customers, Address and Animals tables.
CombinedHistoryView	View	Joins the Animals and History tables and displays work that has been invoiced. The type field in this view displays a letter C for converted history and a letter L for live history.
CombinedHistoryViewNotInvoiced	View	Joins the Animals and History tables and includes work that has NOT yet been invoiced, ie uncommitted and batched work.
Appointments	Table	Stores all of the details about every appointment ever booked, including those slots closed with a blocking category.
Invoices	Table	Stores all of the details about every invoice ever raised.
ListEntries	Table	Stores all of the details about every entry on lists – even if they have since been deleted from the list.
Payments	Table	Stores details about every payment ever recorded.
Recalls	Table	Stores pending recalls. Can be joined to the Animals table by the AnimalRef field.
ServiceCodes	Table	Stores details about all Professional Service Codes.
StockFile	Table	Stores details about all stock codes.
StockHoldings	Table	Stores details about stock levels for each stock code, per branch/location and, if applicable, per batch number.
StockMovements	Table	Stores details about movements for each stock product, per branch and, if applicable, per batch number.

Getting Started

Queries comprise of various clauses and these clauses have to be constructed in a pre-determined format. A very basic query comprises of at least the following two clauses:-

The SELECT clause (which specifies what data will be displayed for each record selected in your query); and

The FROM clause (which specifies in which Table/View that data is stored).

The SELECT Clause

Decide which Table/View you wish to query and click the plus sign alongside that Table or View. This will display a list of fields within that Table/View.
Drag those fields that you wish to display on your report (preferably in the order that you want to display them) and drop them into the Show box in the query builder.
Once the fields are displayed in the Show box, you can change the order in which they are displayed by dragging them from one position and dropping them into another. If you wish to remove a field from the Show box, drag it back out of the box.
As you select the fields you will see that the query is being built automatically and is displayed on the bottom half of the screen.

The SELECT clause comprises of the fields displayed in the SHOW box. You will see that the fields are separated by commas but with no comma after the last fieldname.

The FROM Clause

The FROM clause displays the Table or View from which the fields have been selected.

If your query comprises of only the SELECT and FROM clauses, then, when it is executed, every record will be displayed from the specified table. To display the top so many entries in a table, you can edit the select clause to read:

SELECT TOP 100 AnimalSearchDetails.CustomerRef, AnimalSearchDetails.Surname, AnimalSearchDetails.Forename

To Save the Query

The following options are available for saving or exiting without saving the query.

Clear query and start again.

Export the SQL query and exit. This exports the query into the ad-hoc reports generator, ready for you to execute (run) the query and view the results.

Load previous session. If you have saved a previous session this allows you to reload it to edit it.

Save session. This allows you to save the session and reload it again later.

Exit without saving.

If you wish to review the results of the query so far,

click Save Session first (so that you will be able to reload it > if you wish to edit it later) and then click Export the SQL > query and exit. The query will be exported to the Ad-Hoc > Reports screen.
Execute the query – see page 4. If the results are displayed as > required, then you can select your required output – see page 5.

If you wish to edit the query,

click Build,
and click Load Previous Session and edit as required.

Note: You can write comments at the top of the query before you save it. General comment (for example the report name or a brief description of the report) should be preceded by two dashes, as follows:

--Animal Registration Report

The two dashes effectively instruct SQL to ignore that line and not attempt to process it. A hard return (ie pressing the Enter Key) will end the comments line.

Once you are satisfied with the query, it can be saved and run (executed) whenever required.

Click the Save button on the Ad-Hoc Reports screen toolbar.
SQL scripts should be saved in the Reports folder, usually in the following path if you wish them to be available to other users in the practice or branches within the group. You can create sub-folders if you wish.
C: drive ► Assisi Documents (Live) ► Reports

Note: This path can vary occasionally. If you are unsure, speak to our Support Department for confirmation.

Select the folder in which you want to save the report (or > sub-folder if you have created sub-folders within the Reports > folder), name the report and click Save.

THE FOLLOWING CLAUSES ARE OPTIONAL AND ALLOW YOU TO PRODUCE MORE SPECIFIC REPORTS

The WHERE Clause

It is possible to place conditions upon which records are selected by adding a WHERE clause, which allows you to select the records that meet specific conditions. You can select any of the fields contained within the tables upon which the query is to be based and specify the conditions that apply to the selection of the records that you wish to retrieve, ie you can compare the value stored in the field in the database against the value for which you are searching. You need to specify:

the field to which the condition applies (this doesn't have to be a field that is included in the SELECT clause - it can be any field in the Table or View);
a condition (comparison operator); and
the search value.

Selecting the Field

Click the Conditions tab on the query builder screen.

Drag the field upon which the condition is to be placed into the Item Name box.

Adding a Condition

Click on the arrow to the right of the Condition box and select an operator. The available choices are as follows:-

=	Equal to	Will search for data exactly equal to the specified value. The value can be alphabetical or numerical or a combination of the two.
!=	Not equal to	Will search for data not equal to the specified value.
>	Greater than	Will search for data greater than the specified value but will not include the specified value. The data can be numerical or in date format. When used with dates, > means after, >= means on or after, < means before and <= means on or before the specified date.
>=	Greater than or equal to	Will search for data greater than or equal to the specified value. The data can be numerical or in date format.
<	Less than	Will search for data less than the specified value but will not include the specified value. The data can be numerical or in date format.
<=	Less than or equal to	Will search for data less than or equal to the specified value. The data can be numerical or in date format.
In	In	See page 14 for an explanation of how these two operators can be used.
Not in	Not in
Like/Not like	Like and Not like	You can use the Like and Not Like operators to find values in a field that match, or do not match, a pattern (text string) that you specify. These operators can be used in conjunction with wildcard characters to make the search more flexible:- % The percentage symbol matches any character string. (It resembles * in DOS applications). The underscore symbol matches any single character. These wildcard characters can be used either before or after the text string – or both, if relevant. For example, you could use the condition LIKE ‘Smith%’. This could return all patients with a surname of Smith and Smithson. Whereas, LIKE ‘Sm_th’ could return all the patients with a surname of Smith and Smyth and LIKE ‘Sm_th%’ could return all patients with a surname of Smith, Smyth, Smithson and Smythson.

Specifying the Condition Value

Click on the Condition Value box and type in the text with which you wish to compare the data entries and press the Enter Key to refresh the script. Please note that a value should always be entered with single quotes around it. The search is not case sensitive, ie data in both upper and lower case will be included.

In this example, the query is based upon the AnimalSearchDetails View and the condition is specifying where the Active indicator on the client record is ‘True’ (1 = True or a ticked box and 0 = False or an unticked box) ie include only active clients in the results.

Multiple Conditions

There is no limit to the number of simple conditions that can be present in a single SQL query. The operators AND and OR join two or more conditions in a WHERE clause.

The AND operator displays a result if ALL conditions listed are true.
The OR operator displays a result if ANY one of two or more conditions listed are true.

NOTE: Combinations of both AND and OR can be included in a single query but, when doing so, more care needs to be taken with the syntax of the script to ensure that the correct data is returned. A detailed explanation of how to combine these two operators into a single query can be found on the following page.

To add a second condition to a script, click the arrow to the right of the Combiner column and select your required operator and then add your next condition on the line below. Continue in this way until all of your conditions have been entered.

In the above example, the query is based upon the AnimalSearchDetails View and the animal registration date has been used in the WHERE clause to select all animals who have been registered between specific dates and additional conditions have been added to select only those small animals that have a tick in the Active Patient box on the Animal Details Screen (a value of 1 indicates that the condition is true or that there is a tick in a box).

This is what the resulting query would look like:

SELECT AnimalSearchDetails.AnimalRef, AnimalSearchDetails.CommonName, AnimalSearchDetails.Active,AnimalSearchDetails.AnimalType, AnimalSearchDetails.DateRegistered, AnimalSearchDetails.DateRegistered

FROM AnimalSearchDetails

WHERE

AnimalSearchDetails.Active = '1' AND

AnimalSearchDetails.AnimalType = 'Small Animal' AND

AnimalSearchDetails.DateRegistered >= '2018-01-01' AND

AnimalSearchDetails.DateRegistered <= '2018-12-31'

Note: Using dates as conditional criteria is usually more efficient if you enter the year first, followed by the month number and then the day number.

Combining AND/OR Connectives

When a query has multiple conditions in the WHERE clause and those conditions combine AND/OR connectives, the order in which the conditions are evaluated can affect the results of the query. By default, all of the AND conditions are evaluated before any OR condition – no matter where the clause appears in the query.

This is not a problem if only AND or only OR connectives are used in a WHERE clause - just if there is a combination of these two connectives. For example, you may want to create a query where you selected all active animals whose names are spelled in one of two ways. If you constructed your query as follows:

The script would look like this:

SELECT AnimalSearchDetails.AnimalRef, AnimalSearchDetails.SpeciesName, AnimalSearchDetails.BreedName

FROM AnimalSearchDetails

WHERE

AnimalSearchDetails.Active = '1' AND

AnimalSearchDetails.CommonName = 'Rosie' OR

AnimalSearchDetails.CommonName = 'Rosy'

In this example, the conditions would be evaluated in the following order.

AnimalSearchDetails.Active = '1' AND

AnimalSearchDetails.CommonName = 'Rosie'

(which would return all of your active animals whose name is Rosie)

It would then continue to evaluate the OR condition, ie

AnimalSearchDetails.CommonName = 'Rosy'

(which would also return all of your animals whose name is Rosy, whether they were active animals or not.

The order of evaluation can be altered, however, by putting brackets around the OR condition as follows. The brackets would need to be added manually to the script around the OR conditions before saving.

WHERE

AnimalSearchDetails.Active = '1' AND

(AnimalSearchDetails.CommonName = 'Rosie' OR

AnimalSearchDetails.CommonName = 'Rosy')

This forces SQL to perform the selection based upon the Active indicator first and then check for the animals’ names of active animals only.

An alternative way to construct your query, however, could be to use an IN or NOT IN clause.

The IN/NOT IN Clauses

The IN clause selects a record if a specified value does match any value in a list of values.

NOT IN clause selects a record if a specified value does not match any value in a list of values

The list has to be enclosed in brackets. The individual values within the list are enclosed in single quotes and separated by commas. The final AND combiner will be ignored if no further conditions are added.

The syntax for this script is as follows:-

SELECT AnimalSearchDetails.AnimalRef, AnimalSearchDetails.CommonName

FROM AnimalSearchDetails

WHERE

AnimalSearchDetails.Active = '1' AND

AnimalSearchDetails.CommonName IN ('Rosy','Rosie')

Nested (or Sub-Queries)

The IN and NOT IN clauses can also be used for nested queries. A nested query is a query statement tucked (or nested) inside an existing query - ie, it is possible to run a query based upon the results of another query. We will not be covering nested queries in this manual, however, as this is an advanced feature that requires a more in-depth knowledge of SQL.

The GROUP BY Clause

This can be used in queries where you wish to summarize data.

If you wanted to find out which classifications were in use in your stock file, you could structure your query, as follows:

This would return a list of stock classifications, one for each product that is not locked for sales, ie multiple results for exactly the same classification.

The GROUP BY clause is an SQL command that is used to group rows that have exactly the same values. Therefore, if you use a GROUP BY clause in your script, and GROUP BY classification, then each classification would appear only once.

The syntax for this script is as follows:-

SELECT StockFile.Classification

FROM StockFile

WHERE

StockFile.LockSales = '0'

GROUP BY StockFile.Classification

This would give you a list of classifications that are currently in use in your stock file, with each classification appearing only once.

Note: We will be explaining later in these notes how you can count how many instances of each grouped Category have been found and how to display only those categories that have more than (or less than) a specified number of categories.

You could then use the following query to list which products have a specified category.

The syntax for this script is as follows:-

SELECT StockFile.StockCode, StockFile.Description, StockFile.Classification

FROM StockFile

WHERE

StockFile.LockSales = '0' AND

StockFile.Classification = 'POM-V'

This would return one record for each individual stock product that has that sales classification.

Note: We will be explaining later in these notes how you can set up a prompt for the category for which you are searching.

The FORMULA Functions

This option allows you to add a formula to perform mathematical calculations on the results of a Group By field - such as counting or adding them up or calculating their average value - and include the answer in the SELECT clause. SQL has several such arithmetic (aggregate) functions.

The most commonly used are:

SUM – totals all non-null numeric values in a specified field.
COUNT – counts the occurrences of all non-null values in a specified field.
AVG – returns the average value of all non-null numeric values in a specified field.
MIN – returns the lowest non-null numeric value in a specified field.
MAX - returns the highest non-null numeric value in a specified field.

It performs these calculations on the number of entries in each group. COUNT can be used to count the records in each Group using any field in the table. The remaining functions have to be performed on a specific field that is included in the SELECT clause.

To Add a Formula Function to your Query

In the query builder, on the Show tab, drag the relevant function from the list on the far right hand side of the screen onto the Formula section and replace the * with an actual field name if the function is anything other than Count.

Formula functions in the SELECT clause do not need to be included in the GROUP BY clause as the value is not a stored value but a calculated value.

The following query would list all classifications – just once – where these classifications are in use in the stock file and count how many stock products are in each classification.

SELECT StockFile.Classification, Count(*)

FROM StockFile

WHERE

StockFile.LockSales = '0'

GROUP BY StockFile.Classification

Creating Your Own Column Headings

When you execute the above query, by default, the field name will be the name of the field in the table. You will note that the column displaying the number of products for each classification has no heading. That’s because there is no field name in the database that stores this data – it is calculated each time that you run the query. You can, however, define a column heading. To do so, type in the heading you require immediately after the field name in the SELECT clause, eg

SELECT StockFile.Classification,Count(*) ‘No of Products’

If you use more than one word for the heading, you will need to put single quotes around it, as above.

You can substitute your own headings in the same way for any field (column) name in the SELECT clause.

Using a Formula Function Without a GROUP BY Clause

You can use a formula function in a query without using a GROUP BY clause if the only entries in the SELECT Clause are formula functions. If you wanted to just count the number of active animals in a specific table, for example, you could construct a script, as follows:

SELECT COUNT(Animals.AnimalRef) 'No of Active Animals'

FROM Animals

WHERE Animals.Active = '1'

There are no facilities for constructing a script with only a formula function in the SELECT Clause in the SQL builder, however, so you would need to type this directly into the Ad-hoc Reports screen.

The HAVING Clause

This clause is reserved for formula functions only and where the GROUP BY clause has been used. It allows you to specify a selection condition based upon the results of such functions. The WHERE clause cannot be used in these circumstances as this can be used to query only actual values stored in a table and not calculated values.

The syntax for this is as follows:-

HAVING Count(*) >= '3'

The AnimalSearchDetails View contains a separate entry for each animal for each client (whether they are active or not) with the same CustomerRef and Surname for each entry if they have more than one animal. The following query would limit the number of entries to active clients with 3 or more live cats or dogs only. (15459 is the Species Code for cats and 15461 is the Species Code for dogs.)

It will then group the results by client reference and client surname, ie only list the client once, and count and display the number of relevant animals in each group, ie for each client.

SELECT AnimalSearchDetails.CustomerRef, AnimalSearchDetails.Surname, Count(AnimalSearchDetails.AnimalRef) 'No of Animals'

FROM AnimalSearchDetails

WHERE

AnimalSearchDetails.CActive = '1' AND

AnimalSearchDetails.Active = '1' AND

AnimalSearchDetails.Specie IN ('15459','15461')

GROUP BY AnimalSearchDetails.CustomerRef,AnimalSearchDetails.Surname

HAVING Count(AnimalSearchDetails.AnimalRef) >=3

The ORDER BY Clause

You can specify the order in which the results are displayed (if no sort order is specified, the data will usually be displayed in the order that the data appears in the table). The ORDER BY clause must always be the last clause in a query, ie you must always specify what records you want to select before specifying in what order you want them displayed. You can specify as many secondary sort fields as you wish, with each field separated by commas, as in the SELECT clause.

The data can be sorted into either ascending or descending order. By default, the data in a query generated using the SQL Builder will be displayed in ascending order – ie alphabetical fields will be displayed in alphabetical order and numerical fields will be displayed from lowest to highest. Once the query has been exported to the Ad-hoc Reports screen, and before saving it, you can add the word DESC to the ORDER BY clause to display the data in descending order.

If you add this ORDER BY clause to the previous script, it will sort the entries from those clients with the most animals to those with the least.

ORDER BY Count(AnimalSearchDetails.AnimalRef) Desc

The TOP Clause

This clause needs to be typed manually into the script but it can potentially be very useful when constructing and checking a query that takes a while to run. Until you are satisfied that the query is accurately returning the results that you are looking for, you can specify that you want it to stop running after it has selected the defined number of records. You will need to remember to remove this clause, of course, before you run and save the completed version of the script.

The syntax for doing this is as follows:-

SELECT Top 100 AnimalSearchDetails.CustomerRef, AnimalSearchDetails.Surname, Count(AnimalSearchDetails.AnimalRef) 'No of Animals'

FROM AnimalSearchDetails

WHERE

AnimalSearchDetails.CActive = '1' AND

AnimalSearchDetails.Active = '1' AND

AnimalSearchDetails.Specie IN ('15459','15461')

GROUP BY AnimalSearchDetails.CustomerRef,AnimalSearchDetails.Surname

HAVING Count(AnimalSearchDetails.AnimalRef) >=3

ORDER BY Count(AnimalSearchDetails.AnimalRef) Desc

It would give you the ability to filter for your top spending clients if you use it on a query that

has been ordered by the descending value of how much clients have spent with you in a specified date range.

You can also use it to display, say, the top 50 records in any table so that you can see the type of data that is stored in that table, eg

SELECT TOP 50 * FROM ConsultationItems

In this script, the * is used instead of specifying every field individually, ie it means all fields in the table. It will select and display all of the fields in the top 50 records in the ConsultationItems table. You can substitute any table name to find out, relatively quickly, how the table is constructed and what sort of data is stored in that table.

Variable Values

Instead of embedding an actual value in a WHERE clause, it is possible to replace the value with a token and prompt for the value each time that the query is run. This will make your queries more flexible and prevent other users from having to edit the actual query themselves if they want to search for a different value.

There is no facility for including variable values in the Ad-hoc Report Builder. The script needs to be edited once it has been exported to the Ad-hoc Reports screen.

Creating Tokens

THERE ARE 3 STEPS TO CREATING TOKENS:-

Make up a name for the token.

Embed the token in the query in place of the value.
Prompt for the value of the token.

Name the Token

Give the token a relevant name which should be preceded with an @ symbol, ie @Room

Note: Do not include spaces between the @symbol and the names.

Embed the Token

Embed the token in the query in place of the actual value, as follows:-

WHERE Appointments.Room = ‘Con 1’

becomes

WHERE Appointments.Room = @Room

Prompt for the Value

Enter a command line for each token that will prompt for the value when the query is run.

--Ask,Enter the Room Name,@Room VarChar (20),Con 1

The command line comprises of the following different settings, separated by commas.

--Ask	Activates the prompt.
Enter the Room Name	Is the text that will appear as an entry prompt when the query is run.
@Room	Identifies the token.
VarChar(50)	Specifies the format of the data in the table and the maximum number of characters to be entered when prompted – see below for the most common format settings.
Con 1	Default value but can be overwritten – no space following the comma and no single quotes in this instance.

Data Entry Format Settings

DATES: DateTime – Use this format for all dates.

TEXT: VarChar(50) - where (50) represents the maximum number of character spaces that will be processed when typing in the variable value as the query is run. (Please note that if you type in, when prompted, more than the maximum number of characters specified here, then the entry will be truncated to the specified number of characters.)

NUMBERS: Decimal(10,2) - where 10 represents the total number of digits, and 2 represents the number of decimal places.

Multiple Tables

It may be necessary to base your query upon more than one table (ie the data that you wish to retrieve is saved in more than one table). Tables can only be joined if there is a common entry in the tables or there is a separate table containing these links. (It doesn’t matter what the common fields are called – the important thing is that the fields must contain the same data in each tables.) You should bear in mind that you may join a table with one entry per customer to a table with more than one entry per customer. (This is called a one-to-many relationship.)

For example, the Customers table stores one entry per customer which contains the customer reference - and the Animals table contains one entry per animal which contains the animal reference. There is a table called Animal Ownership that contains both the customer reference and the animal reference that are populated automatically when new customers/animals are registered and this is what links the animals to the customers. Joining these tables is a common requirement so we have pre-programmed a View called AnimalSearchDetails that links these tables for you. If you report on this View, however, remember that it will produce a result for each animal for customers with multiple animals.

For instructions on joining other tables, where no View exists, see overleaf.

Joining Tables

If you select fields from more than one table in the SQL Builder – on any tab - you will be prompted for the common field.

Select the common field from each table and click the Make Join button.

Remember, the fields do not have to be called the same name, it is the data stored in those fields that has to be identical so that records in one table can be matched to records in another.

Example

Say you wanted to report on appointments in a room in a date range and calculate how many appointments had been booked for each appointment category in that date range.

The appointment Category and appointment Start Time fields are both stored in the Appointments table but if you ran the query from this table only, you could not exclude any categories that had been set up as blocking categories only, ie time slots that had been set up to prevent appointments being booked. This data is stored in the AppointmentTypes table only.

On the SQL Join Maker screen, by selecting AppointmentType from the AppointmentTypes table and Category from the Appointments table, the following join would be generated.

SELECT Appointments.Category

FROM Appointments

JOIN
AppointmentTypes ON Appointments.Category = AppointmentTypes.AppointmentType

WHERE AppointmentTypes.BlockOut = ‘0’

The Appointments table is being joined to the AppointmentTypes table by the common fields

Category in the Appointments table and

AppointmentType in the AppointmentTypes table

This would result in the Category being returned for each appointment booked in the date range. You could then:

Prompt for the Room and Date Range.
Group the results by Category and each Category would appear in the results only once.
Count the number of Categories in each group.
Order in descending order of the number of Categories.

The syntax for this query would be as follows:

--Ask,Enter the Room Name,@Room VarChar (20),Con 1

--Ask,Enter the Start Date,@SD DateTime,2019-01-01

--Ask,Enter the Start Date,@ED DateTime,2019-03-31

SELECT Appointments.Category, COUNT(Appointments.Category)

FROM Appointments

JOIN

AppointmentTypes ON Appointments.Category = AppointmentTypes.AppointmentType

WHERE

AppointmentTypes.BlockOut = '0' AND

Appointments.Starttime >= @SD AND

Appointments.Starttime <= @ED AND

Appointments.Room = @ROOM

GROUP BY Appointments.Category

ORDER BY COUNT(Appointments.Category) Desc

Each time that you executed the query, you would be prompted for the room name, start date and end date. This makes the query much more flexible than if the actual values were embedded in the query and the default values can be overwritten, as required.

GENERATE MAIL-MERGED LETTERS FROM ANAD-HOC REPORT

You can export the results of an ad-hoc report into a mail merge using a pre-prepared template. The report should contain all of the fields that you want to include in your letters and the mail merge tokens will be numbered according to the order of those fields in the report results.

Note: It is important, therefore, that you do not change the order of the fields in the Select Clause in the ad-hoc report once the template has been created.

Create a Letter Template

Launch OpenOffice Writer

Note: OpenOffice Writer defaults all documents to be in a book style format and you should amend the print settings as follows to avoid printing a blank page between each printed page.
Click the Tools Menu and Options.
Click the + symbol to the left of Open Office Writer
Remove the tick from Print Automatically Inserted Blank Pages.

There is no facility to save this modification to an existing document, so click File>Save As and save the document as your new template. Open this new template to continue.

Type the non-variable text, ie standard text, into your letter and save the template – without closing it.
To add today’s date to the template:
place your cursor in the position that you wish to display the date, > click on the Insert menu and click Fields and then > Other;
click on the Documents tab and in the Type column, click > Date;
in the Select column, click Date ( class="underline">not Date Fixed);
in the Format column, select your required date format and
Click Insert.
To add your field tokens to the template, click on the Database tab and then click on Mail Merge Fields.
Click the + symbol to the left of the Middleman database to display the tables in the database and then click the + symbol to the left of AdHocMailMerge to display the tokens in the table.
You can now add the required tokens to your template.

They are numbered Col01-Col99 and these relate to the order that the data columns are displayed in your ad-hoc report, ie Col01 is the first column, etc. There are also additional tokens for Customer Ref (CustomerRef) and Animal Ref (AnimalRef) if these are not displayed.

You can either double-click on each token so that they appear one after the other in the template and re-position them later or place your cursor where you would like each token to be positioned and then double-click the token (or you can click on each token and click Insert if you prefer this to double-clicking). You can click between the template and the Field Selection screen when inserting field tokens.

When all of the tokens have been added, click Close and Save the template in the Templates folder in same path where all of your other Assisi documents, ie consent forms, are stored. If you are not sure of the correct path, please contact Support for advice.

Run the Ad-hoc Report

Select Reporting>Ad-hoc Reporting.

Click Open then browse for and double-click the required report.
Execute the report, the results will be displayed on the bottom half of the screen for checking.

Complete The Merge

Click Merge on the toolbar.

Browse for and select the template and click Open.
An OpenOffice document will be created with individual pages for each record identified in the report, ready for printing and saving.

GENERATE MAIL-MERGED LABELS FROM AN AD-HOC REPORT

Create Label Template

Launch OpenOffice Writer.

OpenOffice Writer defaults all documents to be in a book style format and you should amend the print settings as follows to avoid printing a blank page between each printed page.
Click the Tools Menu and Options.
Click the + symbol to the left of Open Office Writer
Remove the tick from Print Automatically Inserted Blank Pages.

There is no facility to save this modification to an existing document, so click File>Save As and save the document as your new template. Open this new template to continue.

Now select New>Labels from the File menu.

The aim is to add data tokens to the Label Text box which identify the data fields to be displayed on the label.

Open Office remembers the data tokens from the previous template. If there is a tick in the Address tick box, remove this tick, and if there are any tokens already in the Label Text box, you can delete these if they are not relevant and add new tokens, as follows:
In the Database drop down box, select MiddleMan.
In the Table box, select AdHocMMerge.
In the Database Field box, will be a list of tokens that can be > added to the label template. They are numbered Col01-Col99 and > these relate to the order in which the data columns are displayed > in your ad-hoc report, ie Col01 is the first column, etc. There > are also additional tokens for Customer Ref (CustomerRef) and > Animal Ref (AnimalRef) if you need these and they are not already > displayed in the report.
To add your field tokens to the template, select the token in the > Database Field box; place your cursor in the Label Text > box where you would like the token to be positioned; and then > click the arrow to the left of the token name to move the token to > the Label Text box.
When all of the tokens have been added:
in > the Format section click Sheet;
in the Brand box, select the brand of labels that you are using, > eg Avery A4;
In the Type box, select the label reference for the type of > labels that you are using, eg L7160 or L7161.
Click New Document and the new document now becomes your label template. Save this template.

Run the Ad-hoc Report

Select Reporting>Ad-hoc Reporting.

Click Open then browse for and double-click the required report.
Execute the report, the results will be displayed on the bottom half of the screen for checking.

Complete The Merge

Click Merge on the toolbar.

Browse for and select the template and click Open.
The document will open ready for you to print onto your A4 labels.

Contents

Structured Query Language

SQL Queries

To Execute (Run) an Existing Query:

To Stop a Query From Running

Printing and Exporting SQL Queries

Print Preview

Print

Export to a Spreadsheet

Merge

Save to a List

Building Your Own Queries

Database Structure

Database

Tables and Fields

Related Tables

To Build a Query

Selecting Table(s)/View(s)

Most Commonly Used Tables/Views

Getting Started

The SELECT Clause

The FROM Clause

To Save the Query

The WHERE Clause

Selecting the Field

Adding a Condition

Specifying the Condition Value

Multiple Conditions

Combining AND/OR Connectives

The IN/NOT IN Clauses

Nested (or Sub-Queries)

The GROUP BY Clause

The FORMULA Functions

To Add a Formula Function to your Query

Creating Your Own Column Headings

Using a Formula Function Without a GROUP BY Clause

The HAVING Clause

The ORDER BY Clause

The TOP Clause

Variable Values

Creating Tokens

Name the Token

Embed the Token

Prompt for the Value

Data Entry Format Settings

Multiple Tables

Joining Tables

GENERATE MAIL-MERGED LETTERS FROM ANAD-HOC REPORT

Create a Letter Template

Run the Ad-hoc Report

Complete The Merge

GENERATE MAIL-MERGED LABELS FROM AN AD-HOC REPORT

Create Label Template

Run the Ad-hoc Report

Complete The Merge