Now when looking at the grid you will see the User ID at the top right of the spreadsheet. If you click the arrow you can limit the figures to just that user. When graphed, the User ID will be visible, if it is 'dragged' over to the left beside Year you will get a very colourful graph of all the user by all the years. By selecting just some users and just some years you can get a picture of each users.

Some (more) Pivot Table Examples

In addition to the stored reports we have covered pivot tables can be used for many of the 'Standard' reports on the system. Here are some more examples:

QUESTION: I want a graph of Practice Income

QUESTION: What is the Practice Average Transaction Value and how many transactions have I done?

Run the Stored Report: Financial -> Practice ATV Use the 'F'ile option (will be saved as 'atv.csv')

Load into Excel, select Pivot table

• To look at ATV
  • ( Drag Year to Column, Drag Month to Row, Drag ATV to Data)
  • Click the chart option to Graph Average Transaction Value
• To look at Number of Transactions
  • ( Drag Year to Column, Drag Month to Row, Drag Transactions to Data)
  • Click the chart option to Graph Number of Transactions
• To look at Income
  • ( Drag Year to Column, Drag Month to Row, Drag Income to Data)
  • Click the chart option to Graph Practice Income

This option creates reports based on the date, the user and a specific (or range of) analysis codes.

When selected the screen will display:

If you want all data leave the dates empty, if you want all work after a specific date enter the required date in the 1st field and leave the second blank. (See Dates Below for more details).

The same applies to analysis codes, leave the 2nd blank to look for one specific code. If you want to look for clinical text then refer the option 'Clinical Text Contains'.

You can enter a question mark (?) in the analysis code box to give you a list of all the analysis codes on the system.

If you would rather look at your Analysis Groups, then leave the 1st analysis code box blank and enter the group in the 2nd. As with analysis codes, a question mark will list the groups available to you.

To limit the reports to just a specific user, enter the user ID beside 'User who did the work'. If you enter one of your User Groups then the report will be for that group of users. As with Analysis codes, a question mark will list all the groups and users on the system.

The Restrict by is covered in later.

In all cases a further question will appears asking you how you want the work grouped. The options are:

The following examples will show the same data (analysis codes between 85 and 97 since 1st Jan 2001) and the effect of the 'Group by' option.

Group By: Nothing

+----------+--------+-----+------+---------+------------------------------------
| Date     | Animal | Ana | User | Price   | Details
+----------+--------+-----+------+---------+------------------------------------
| 03.01.01 |   8094 |  85 | AL   |  120.85 | Dog Dental Treatment
| 03.01.01 |  12690 |  86 | AL   |   92.41 | Cat Dental Treatment
| 03.01.01 |  15150 |  87 | MAR  |    5.88 | burr teeth
| 04.01.01 |   1430 |  86 | AL   |   92.41 | Cat Dental Treatment
| 04.01.01 |   4234 |  86 | EL   |   92.41 | Cat Dental Treatment
| 04.01.01 |  10574 |  86 | AL   |   92.41 | Cat Dental Treatment
| 09.01.01 |   2053 |  86 | AL   |   92.41 | Cat Dental Treatment
| 12.01.01 |   4495 |  86 | AL   |   92.41 | Cat Dental Treatment
| 16.01.01 |  10700 |  86 | AL   |   92.41 | Cat Dental Treatment
| 18.01.01 |  15150 |  87 | AL   |   73.93 | Rabbit Dental Treatment
| 22.01.01 |   7213 |  86 | AL   |   92.41 | Cat Dental Treatment
| 25.01.01 |   3682 |  86 | AL   |   92.41 | Cat Dental Treatment
[Snip]

Group By: Date

+-----+----------+----------------------+--------+--------+
| Ana | Date     | Description          | Amount | Number |
+-----+----------+----------------------+--------+--------+
|  85 | Jan 2001 | Dog Dental Treatment | 120.85 |      1 |
|  86 | Jan 2001 | Cat Dental Treatment | 866.69 |     10 |
|  87 | Jan 2001 | Rabbit Dental Treatm |  79.81 |      2 |
|  85 | Feb 2001 | Dog Dental Treatment | 531.74 |      6 |
|  86 | Feb 2001 | Cat Dental Treatment | 369.64 |      4 |
|  85 | Mar 2001 | Dog Dental Treatment | 362.55 |      3 |
|  86 | Mar 2001 | Cat Dental Treatment | 646.87 |      7 |
[Snip]

Group by: Analysis

+-----+----------------------+--------+
| Ana | Description          | Number |
+-----+----------------------+--------+
|  85 | Dog Dental Treatment |     30 |
|  86 | Cat Dental Treatment |     75 |
|  87 | Rabbit Dental Treatm |     10 |
+-----+----------------------+--------+

Group by: User

+------+----------+-----+----------------------+--------+
| User | Date     | Ana | Description          | Number |
+------+----------+-----+----------------------+--------+
| AL   | Mar 2001 |  85 | Dog Dental Treatment |      9 |
| AL   | Jan 2001 |  86 | Cat Dental Treatment |     20 |
| AL   | Mar 2001 |  87 | Rabbit Dental Treatm |      3 |
| ANN  | Aug 2001 |  85 | Dog Dental Treatment |      2 |
| ANN  | Jun 2001 |  86 | Cat Dental Treatment |      2 |
| BEN  | Mar 2001 |  85 | Dog Dental Treatment |      2 |
| BEN  | Apr 2001 |  86 | Cat Dental Treatment |      5 |
| BEN  | May 2001 |  87 | Rabbit Dental Treatm |      1 |
| CAT  | May 2001 |  85 | Dog Dental Treatment |     13 |
| CAT  | Dec 2001 |  86 | Cat Dental Treatment |     31 |
| CAT  | Oct 2001 |  87 | Rabbit Dental Treatm |      3 |
[Snip]

This will search the clinical records for lines containing specific text, very similar to the existing 'Search for Code' report except quicker.

When selected the screen will display:

As with the previous option, the dates are optional. Enter the text to search for e.g. mites

+----------+--------+-----+------+-------+--------------------------------------
| Date     | Animal | Ana | User | Price | Details
+----------+--------+-----+------+-------+--------------------------------------
| 02.01.97 |  10921 | 138 | NAD  |  0.00 | New kitten - cat flu. Eating well. Ea
| 10.01.97 |   9032 | 138 | EL   |  0.00 | beak mites
| 18.01.97 |   6981 | 138 | RET  |  0.00 | prurutic, holds rh in a strange way w
| 01.02.97 |  10766 | 138 | 068  |  0.00 | Billateral otitis externa properly ea
| 01.02.97 |  10766 | 138 | 068  |  0.00 | very bad ear mites clean bid for 7 d
[Snip]

Wildcard matches

There may be occasions where the text you are looking for has extra punctuation in it, or is separated by other text. In this case there are some characters with 'special meaning', these allow you to search for these situations.

Using our earlier example of mites, lets assume we are looking for ear mites - have they been entered as 'ear mites' or 'ear-mites'? In this case we want to look for a single character so enter ear_mites as the text to search for - this will find both cases.

What is the gap is longer, use ear%mites this will match:

Note: If you want to search for the '%' or the '_' then you should prefix them with a back slash ('\').

The Restrict by is covered in later.

This is very similar to the above option but used to search for phrases that may or may not appear in the clinical text.

That is you are not sure if the phrase you are looking for was entered as 'Thyroid Lump' or 'Thyroid Tumour' or even 'found a lump, Thyroid?'. This option assigns a 'Score' to each clinical line, very similar to search engines on the web. The higher the score the better the chances are that the line is relevant.

Enter the details as the text search e.g. lump thyroid tumor and you will get results similar to.
(As the clinical line has been truncated to fit the manual you may not see the full text - try this on your machine.)

+--------+--------+------+-------+----------------------------------------------
| Date   | Animal | User | Score | Details
+--------+--------+------+-------+----------------------------------------------
| Apr 99 |  10987 | TWM  | 20.16 | I think I can feel a thyroid lump - has the a
| Jun 97 |   2057 | U13  | 16.35 | Old dog massive tumour LH. One of cats vax-ol
| Aug 00 |   1961 | TOM  | 15.14 | now has large lump on ribs - probable sec. bo
| Nov 98 |   4989 | TOM  | 14.41 | 1.Mamm lump -quite a wide margin excised as w
| Feb 97 |   3957 | U13  | 13.33 | Left ear infected/looks like bite below ear.I
| Jun 97 |   1516 | U13  | 13.07 | Some wt loss.Heart rate ^.cant really count b
| Dec 97 |   1018 | V4   | 12.84 | V since this am & off food a bit, motions loo
| May 97 |   7025 | TWM  | 12.72 | HR 220/min=, wt loss p/phagia, poorly digeste

The higher the score column the more relevent. The results will be returned by highest score not by date.

There is an option to disable building the index required for the Full Text Search. If the snapshot was built without the index and you then try to use it. You will ge a message similar to:

ERROR 1191 at line 1: Can't find FULLTEXT index matching column list

Animals by Ana Group Usage (Or Who are my active animals)

    Group     Call It                  What
 1
 2
 3
 4
 5

  1 Consultation/Examina    2 Vaccinations            3 Neutering
  4 Radiography/ECG         5 Fluid Therapy           6 Worming Treatments
  7 Flea Control            8 Desk Sales              9 Hills Science Plan
 10 Prescription Diets     11 Drugs & Products       12 Laboratory Fees
 13 Hospitalisation        14 Nails/Anal Glands/Ot   15 Anaesthetic Fees
 16 Surgery Fees           17 Dental Treatment       18 Microchip
 19 Visit Fee              20 Euthanasia Fees        21 Weight Control Clini
 22 Puppy/Adolescent/Sen   23 Consumables            24 Miscellaneous
 25 LVI Certification      26 Postage & Packing      27 Prescription Charge
 28 Discount/Surcharge    110 Letter etc.           111 Credit Control
112 Advice

    Group     Call It                         What
 1    4       Diagnostic                      Ignore
 2    7       Flea                            Ignore
 3    6       Wormers                         Ignore
 4
 5

+--------+--------------------+------------+------------+-
| Number | Animal Name        | D.O.B      | Species    |
+--------+--------------------+------------+------------+-
|      4 | Chazz              | 22.03.1995 | Canine     | 
|     19 | Pixie              | 07.01.1997 | Feline     | 
|     31 | Apollo             | 01.02.1985 | Canine     | 
|     32 | Mikki              | 06.08.1998 | Feline     | 
|    106 | Sam                | 01.08.1993 | Canine     | 
|    196 | Harrison           | 01.01.1987 | Feline     | 
[Snip]


-+------------+---------+----------+---------+-
 | Species    | ID Chip | Neutered | Insured | 
-+------------+---------+----------+---------+-
 | Canine     | No      | Yes      | No      | 
 | Feline     | No      | Yes      | No      | 
 | Canine     | No      | No       | No      | 
 | Feline     | Yes     | Yes      | No      | 
 | Canine     | No      | No       | No      | 
 | Feline     | No      | Yes      | No      | 
[Snip]

-+------------+------+---------+------------+
 | Diagnostic | Flea | Wormers | Recall Due |
-+------------+------+---------+------------+
 | No         | No   | Yes     | Jul 2002   |
 | No         | Yes  | Yes     | Apr 2002   |
 | No         | No   | Yes     | Jun 2002   |
 | No         | No   | Yes     | Dec 2002   |
 | No         | No   | Yes     | Oct 2002   |
 | No         | Yes  | Yes     | Aug 2002   |
[Snip]

Once you are happy with the options save the query and then re-run to file. This can be loaded into a spreadsheet to look for trends within your client base (see later section).

Expanding / Drill down

There may be times where your existing grouping does not give you enough control of the options to examine. You could change the basic grouping but this would affect the bills/receipts/estimates you send the clients.

How do you avoid this?

You have 128 group 'slots' available, the 1st 100 will be used for the client bills/receipts - the top 28 are reserved for DataMajor. The system will group the work by the HIGHEST group the analysis code is found in as far as DataMajor is concerned. With bills it will be added to the LOWEST group.

For example, you may want to sub-divide your 'Laboratory Work' group so you can look at Urinalysis, T4 and other blood work. To do this add two new groups ABOVE 100 e.g.

Remember:

DataMajor uses a snapshot of your data, if you change groups or analysis codes you will no be able to report on them until the next time the reporting database is re-built.

Note: When the snapshot is created a pivot table query (Stored Reports -> Pivot Tables -> Animal by Group) will be created and updated each time that will include the basic details and every analysis group currently on the system.

Excel has a useful option 'Filters', these allow you to run the full report and then play around with the different options - do a 'What if' type report.

Select the criteria as per the previous section, save the report and run it to file. Load it into Excel (as per Graphing Results).

You will notice 'arrows' beside each of the column names - these are used to 'filter' or restrict the records displayed.

For example to only look at Dogs that are Insured:

• Click the arrow beside Species and select Canine - you are now looking at only those records relating to Dogs.
• Now click the arrow beside 'Insured' and select 'Yes' - the report is now only Insured Dogs.

Remember:

The filter remains in place until you re-select 'All' from each of the pull down filters you have used. The 'arrow' will be in a different colour if a filter is in use.

Tip: To remove all Dogs and Cats from the list, select the Species filter then 'Custom'. In the pop-up box use the pull down options beside the box to set:

Does not equal	Canine
And
Does not equal	Feline

This filtering makes it easy to look at specific groupings of your data.

Group and Filter Example

You have a range of analysis codes (10 - 20) dealing with vaccinations, you want to look at those records for who has have a 'Young Puppy' vaccination and NOT come back the following year for a booster. Your 'young puppy' analysis code is 15.

If you have not already done so, create a group (above 100) e.g. 101 called 'Young Puppy/Kitten' add in analysis code 15. Rebuild DM

Use 'By Analysis Group', select your vaccination group and set to ignore, select your group 101 and set to ignore, run the query.

You have looked at your active clients, now you want to send a mail shot, you want to 'track' these clients - did the mail shot work?, you may be monitoring Dental uptake - how do you do this?

DataMajor has the ability to 'Tag' the individual animal/client that it found - currently there are four tags - Platimum, Silver, Bronze and Gold - this tag is displayed (as of Nov 2002 Premvet release) on the management card.

When running the Grouping report you are asked if you with to set the marketing tag - if yes is selected the report is run as before but before returing to the menu you have the option to get the system to update the management record tag if required. (If you are a cross updating site this information WILL cross update).

There is a set of stored reports to list the taged animals. You can also use the stored reports to tag clients.

In Scripts

To mark clients within a script you have to keep two things in mind:

• The 1st field MUST be Animal number (PVID) and the last field MUST be the DataMajor number (ClientKey).
• The text SET MARKETING TAG = X has to be present in one of the comment lines.
  The X must be one of N (None), P (Platinum), S (Silver) or G (Gold)

Here is the script to reset the tag to None if the animal is dead with the important lines shown in bold.

## This will set the marketing tag to NONE for all dead animals that
## are currently tagged.  ** SET MARKETING TAG = None **
##
## Premvet WILL be updated - This will Cross update.

SELECT
  a.pvid as 'Number',
  c.marketing as 'Taged as', a.animalname as 'Name',
  ifnull(a.species,'Unknown') as 'Species',
  ifnull(a.breed,'Unknown') as 'Breed',
  date_format(a.dateofbirth,'%M %Y') as 'DOB',
  a.clientkey as 'ClientKey'
FROM animal a,client c
WHERE a.clientkey = c.clientkey AND c.marketing != "None"
AND ( a.dead = 'Dead'  or c.numberofanimals=0 ) ;

The system will use the 1st field to locate and update Premvet where as the last field will be used to update DataMajor there and then with the tag None.

It does not matter which index the report lives in, the system will detect this has to update the system and will act accordingly.

Dates and Ad-Hoc Reports

When running the reports on the Ad-Hoc menu you are prompted for dates. This is fine as long as you are running them as one-off - if you want to save the report and run it on a regular basis you cannot if the date range varies.

However, if you are interested in work done in the last 30 days, the last 6 months, the last 2 years e.g. work in the last xx something. Then you can do this.

The screen will display:

• To search all the records just leave the dates blank.
• To search a specific range fill in both dates.
• To search from a specific point onwards - fill in the 1st date but leave the 2nd blank.
• To run the report for the last xx days/months/years

  Leave the 1st date BLANK and enter the interval in the 2nd e.g.

  30 days	In the last 30 days
  45 days	In the last 45 days
  4 months	In the last 4 months
  1 year	In the last year
  2.5 years	In the last two and half years

  You can enter any number followed by Days, Months or Years.

When the report is added to the stored reports the interval will also be saved and used the next time the report is run.

This section will be populated over time to include special setup options for specific reports. That is, if the report you want does not fit into one of the existing categories it may appear here.

As with the other reports you can save the query to re-run to the printer or graph the results.

Analysis Code/Group by Day/User for last month

This will breakdown work by day of the week and user for a specific analysis code or analysis group. The period is the last calendar month.

You will be asked:

As an example, here is consultations (analysis code 10) for the last month.

+-----------------------------------+-------------------+
| Report For                        | Code  10          |
+-----------------------------------+-------------------+
| Between 28.01.2002 and 28.02.2002 | Consultation fees |
+-----------------------------------+-------------------+
+------+-----------------+-----------+--------+--------+
| User | Name            | Day       | Amount | Number |
+------+-----------------+-----------+--------+--------+
| KIR  | Kirsten         | Monday    |  70.58 |      3 |
| JEN  | Jennifer        | Monday    | 132.62 |      6 |
| TOM  | Tom Melvin      | Monday    | 198.54 |     11 |
| JC   | James C         | Monday    |  74.85 |      3 |
| LEE  | Lee Cairns      | Monday    |  89.81 |      6 |
| LEE  | Lee Cairns      | Tuesday   | 124.75 |      5 |
| JB   | Julian B        | Tuesday   | 116.21 |      5 |
| TOM  | Tom Melvin      | Tuesday   | 178.25 |     10 |
| JEN  | Jennifer        | Tuesday   | 182.52 |      8 |
[Snip]

This report looks at transaction volume and the number of 'slots' you have to work out occupancy rates. This report assumes you would like at least 80% occupancy.

Some transaction e.g. pet food sales will not affect the occupancy rates so you do have the option to ignore some transactions (done via analysis codes). By re-running the query with different work being ignored it will let you see what affect for example, diets/wormers have on your transaction volume.

The report can be run for a monthly or yearly basis.

A slot is either an appointment or operation e.g. 9am - 10am 10 min appointments = 5 slots, two vets = 10 slots, carry out 10 operations a day brings it up to 20 slots another 1 hr appointments in afternoon and evening makes one day = 40 slots, you are open 6 days a week = 240 slots a week.

E.g. Here is a yearly report with 240 slots a week available. (240 * 52 weeks = 12480 slots a year)

+------+--------+-------------+-------+---------+
| Year | Volume | Target 80 % | 100 % | Actual% |
+------+--------+-------------+-------+---------+
| 1997 |   6431 |        9984 | 12480 |   51.53 |
| 1998 |   6720 |        9984 | 12480 |   53.85 |
| 1999 |   6649 |        9984 | 12480 |   53.28 |
| 2000 |   6489 |        9984 | 12480 |   52.00 |
| 2001 |   7226 |        9984 | 12480 |   57.90 |
| 2002 |    926 |        9984 | 12480 |    7.42 |
+------+--------+-------------+-------+---------+

Note: DataMajor will by default, only look at 'live' animals, as an Euthanasia may well use up a 'slot' these will NOT be included in the results. You should take that into account. There is no option to 'alter' the slots over the period, this would have to be done manually when exporting the figures to a spreadsheet.

All payments on the system are given analysis code 255 - the list of clients paying are given on the Vatbook - but sometimes this report is lost or thrown out.

Yes you can use 'Search for Code' or 'Ad-Hoc - Analysis Code search' and these will give you a list of payments - but they do not give you any totals or breakdown.

This Payment Report does the same but will print out a breakdown by payment type and give sub-totals per day.

Note: You can have a range of dates and the will print only.

In addition to the Ad-Hoc reports there is a set of Stored reports that can be used to cover some common situations. These are:

• Who has had 'something' but not 'something else' Look at animals seen in the last XX days, for a specific species (leave blank for all) where some clinical work WAS done but the client never returned for a planned follow up. For example came in for 1st vaccination but have NOT come in the the 2nd

• Who has NOT had specific treatment in a period Animals who have been seen during the specified dates (species is optional) and NOT had some specified clinical work.

• Had Text but not group This is similar to the 1st report except it uses the clinical grouping. For example, you want to list all animals seen in the last XX days where the vet had written 'Advise Dental' but the animal has NOT had any dental work done.

• Text Search output Client details Normally the clinical search options do not include the client details - you may want to contact all client that have purchased a specific product or had clinical work done. This option will look for animals seen in the last XX months, check to see if the work entered matches and include Name, Telephone, Address in the output. For Example, to locate all animals sold the Diet food 'Capolin and Tapioca' in the last 2 months and advise them it has been recalled - just the animal number is not that helpful - use this to get a phone list.

Some of the options have a field 'Restrict By' (or Special in Man Reports), this allows you to enter additional restrictions to the search e.g. just canine's. Each of the menu options that allow restricted searches is given below:

Option	clinical	animal	analysis
Abbreviation	(c)	(a)	(y)
Clinical Text Contains	Yes	Yes	No
Clinical Date By Analysis	Yes	Yes	No
Management Reports	Yes	Yes	No

Only the tables listed above can be used at that point, refer to the listing in Appendix 1 for the field names. You should enter a valid SQL comparison e.g.

The SQL database we are using supports 'ODBC' (Open DataBase Connectivity), this is a 'standard' that allows other applications to access the data. There are many applications that support ODBC the most common is Micro$oft Office tools (Word, Excel, Access) and as such will show you examples based on those.

You can use ODBC to access the data directly from within Excel,, you do not need to run any software on the Premvet Server. Once you have created the initial query it will usually be saved when the spreadsheet is saved allowing you to 'refresh' the data and keep the graphs updated.

Appendix A lists the tables that are available along with a description of all the fields that you may want to use.

Start up Excel and select:

As an example we will graph the trend for Cat Castrations.
(Cat castrations in this example are analysis code 67, adjust as appropriate for your data.)

• Select Premvet as the Data Source and click OK.

  

• Query Wizard - Choose Columns
  • Expand up the Clinical Table by clicking the + beside the name
  • Select Month, Year, LineBalance, AnalysisCode

  

• Query Wizard - Filter Data
  • Select AnalysisCode, then from pull down box select equals,
  • then enter 67 in the final box.
• Query Wizard - Sort Order
  • Click Next as we don't want to sort in this example.
• Query Wizard - Finish
  • Return Data to Excel
  • Select PivotTable Report

The system will extract the data from DataMajor and you will be presented with a blank Pivot Table.
Drop the Month into Row, Year into Column and LineBalance into Data.

You can now graph the details as per previous examples.

Save the spreadsheet, when you recall it at a later date, if you click the red exclamation mark in the pivot table wizard the data will be updated.

One more example, generating a Pie chart of Species.

Start up Excel and select:

• Select Premvet as the Data Source and connect.

• Query Wizard - Choose Columns
  • Expand up the Animal table by clicking the +
  • Select Species
• Query Wizard - Filter Data
  • Click Next as we don't want to filter in this example.
• Query Wizard - Sort Order
  • Click Next as we don't want to sort in this example.
• Query Wizard - Finish
  • Save the query for later if required.
  • Return Data to Excel
  • Select PivotTable Report

The system will extract the data and you will be presented with a blank Pivot Table. Drop the Species into BOTH Row and Data.

Select Graph, right click the background, select Chart Type and for this example pick the 'Bar of Pie' option. You will notice that it is a little congested - Some tips to help:

• Go back to Sheet 1 that shows the Pivot Table, right click it
• Select 'Field Settings' then Advanced.
• In here, change the 'AutoSort' option to 'Descending' using 'Count of Species'.
• Select 'Automatic' under 'Autoshow' and enter a reasonable number beside Top to reduce the odd one off species you will have.
• On the Chart1 screen, right click and select 'Format Data Series', under 'Options' you can adjust the Bar chart to suit the data you are looking at.

• Start up Word, make sure the 'Database toolbar' is visible
  (Use View -> Toolbars to enable it).
• Click 'Insert Database'.
• Select 'Get Data' then MS Query.
• Scroll down the Databases till you see 'Premvet Data', highlite
• Select OK.

Microsoft Query will start up and you can now select what information you want. For example, lets extract all Terrapins.

• Expand up the Client table and select Surname, Title, Initials, Address lines and postcode.
  Expand up the Animal table and select Species.

• Click next and select Species to filter on, pick 'equals' from the pull down box, and select Terrapin from the final pull down box. Click Next and Next again.

• Click 'Insert Data', All and click OK - The client details will be inserted as a table in your current document.

To use MailMerge, the principle is similar:

Select Mailmerge from the Tools option,

1. Create -> Form Letter -> Active Window
2. Get Data -> Open Data Source -> MS Query -> Premvet Data
   Select the fields from the tables as required.
   e.g. Name and Address plus whatever you want to search on

Microsoft Query

Microsoft Query will allow you to look at the data, add columns and filter depending on your query.

• Start up Microsoft Query -> New
• Scroll down the 'Data Source' window until you locate 'Premvet Data', highlite it.
  (You may want to 'un-tick' the 'Use Query Wizard' question as it gives you more control), then click OK.
• Select the tables you want to query (See Appendix 1 for details.)
• Double click the field name(s) you require.
• Repeat above until you have selected the fields you want.
• Select either black exclamation mark (!) on the toolbar or use Records -> Query Now.

Technical Stuff

Utilities Menu Option

This give access to some of the maintenance options:

With the exception of Re-building, Query Maintenance and Setup you should not need to use any of the other options.

(This currently contains only one setting.)

Moved Away,

over a period of time clients will move out of the area, move to different Veterinary Practices etc. e.g. they are no longer a client of yours. You will generally have marked the record cards with a status code 'MA', 'RH' etc.

The first option, 'Moved/Rehomed Status Codes' - enter here the status codes for that type of client e.g. MARHV1. When the snapshot is created it will set a Field 'IsClient' to 'No' on each animal card, it will also use the last consultation date and add it to the Field 'IsClientDate' both of those are in the Animal Table. If ANY animal belonging to a client is marked as such the Client Table field 'IsClient' will also be set to 'No'.

This field is currently used in two stored reports:

Practice Statistics -> Practice Growth

This uses the IsClientDate to show you when the client left the practice, along with registration and Date of Death to summarise the growth.

Practice Statistics -> Lost Clients

This report looks at clients who have not spent any money with you in the last 12 months (+ current year) but did in the previous 3 years.

The reports you see when you select stored reports are all plain text files this menu option allows you to:

As you create more and more queries of your own it is recommended that periodically you 'tidy up' your index entries. Move your 'related' query's to a new index or add them to one of the existing one.

DataMajor works on a COPY of your data, this copy needs to be updated on a regularly basis via a job in cron. When first installed support will have setup the cron job for you.

Some of the common cron settings are:

  5 0 * * *  /usr/local/bin/vet -p wdb >/dev/null    #Every morning at 0:05
  5 0 * * 0  /usr/local/bin/vet -p wdb >/dev/null    #Once a week (Sunday)
  5 0 1 * *  /usr/local/bin/vet -p wdb >/dev/null    #Once a month (on the 1st)

If you need to re-build the snapshot before the automation run you can do this manually. The re-build can take a little while!!.

Use the stand alone utility 'wdb', at an OS prompt enter:

Use the Automation Manager within premvet to alter and adjust the snapshot times rather than editing cron jobs manually.

This will use the default settings (include all animals and covers five years of information). You can specify some options to wdb, these are:

For example, to run the export for 10 years and exclude dead animals you would enter:

Remember the more data you extract the more disk space that will be required so ensure you have the disk space.

There is also an option on the utility menu to do run wdb, as well, when run from the Utility menu you cannot alter any of the defaults.

Merging Multiple DataBases

Where you have multiple sites and wish to create one database with all the information in it. You will need to use a script something similar to:

# Use the MASTER directory here - all the Ana Codes, groups etc.
vet -p wdb "-P 1 -o ubys"    # Pass one to create the files

# Loop on all the directories and create the import file
for x in /w/dir1 /w/dir2 /v/dir3
do
   vet -d $x -p wdb "-P 2"
done
# Finished extracting clients - create the SQL database
vet  -p wdb "-P 3"    # Last Pass to create the files

Multiple Database's on the same server

You can have multiple databases on the same server. That is you have an out-of-hours clinic or a play area and you want to look at this data within DataMajor as well.

Set the environment variable SQLD to the name of the of the database. This can be set in common.tcf (or any of the tcf files). When accessed from the Premvet menus this database name will be set automatically. If using ODBC you will need to specify the same 'Database Name' in the DSN.

If you are writing your own scripts this section of the manual will show you some ways you can interact with the user. That is ask them questions or give them additional help on the screen.

Within your scripts:

To ask the user for some information use the format:

There are some additional 'features' to make it easier for you

• If the text STARTS with quotes (") then the replacement will be taken as comma separated fields (e.g. suitable for the 'in()' command e.g.

  In Script	User Enters	MySQL gets
  !!"Enter Species!!	canine,feline	"canine","feline"

• If the text ENDS with an explanation mark (!) the line is optional and will be skipped - also the FULL line containing the replacement will also be skipped. Make sure you do NOT have multiple statements on the same line if you are using this.

  In Script	User Enters	MySQL gets
  !!Sex (leave blank for all)!!!	NULL	Line Skipped

• If the text contains the word 'DATE' the expected answer should be a valid date. All the Premvet date shortcuts will work e.g. +x, t etc.

  In Script	User Enters	MySQL gets
  !!Enter Start Date!!	15.8.02	20020815
  !!Enter Start Date!!	-10	Date 10 days ago

  If the line contains a less than (<) then date entered will have one day removed - saves the user remembering how many days in a month :-)

  In Script	User Enters	MySQL gets
  !!End Date (<)!!	1.2.02	20020131

• If the text contains the word 'MM.YY' the system will automatically add 01 to the date. This puts in the correct format for MySQL date comparisons.

  In Script	User Enters	MySQL gets
  !!Which Month (MM.YY)!!	8.02	20020801

  Note: The screen will display the MySQL format once the question has been answered.

There may be times where you always want a report mailed to an address. If you add the line:

# MailTo:  xyx@abc.com

Other options

• No CSV Header - The header on the CSV file will be excluded

• Landscape - Force the printout into Landscape mode

• Per_Page - Will output one CLIENT per page, Client details at the top, followed by all related animals. Note: For this option you must specify the client in the 1st column and the animal in the 2nd. Both these fields will be excluded from the output.

You can copy the DataMajor database from the Unix server onto your PC or Laptop to look at and analyse the data away from the practice. To do this you need to install the Windows version of MySQL. This will be found in /usr/local/images/mysql as the file mysql-3.23.42-win.zip. Copy this file to your PC and use WinZip (on the Utility CD) to extract. Run the install script 'setup' and follow the instructions on the screen.

To start up the SQL server either re-boot the PC or locate the admin utility 'winmysqladmin' in the install\bin directory and click it. This will start the server and you will see a set of 'traffic light' appearing in the 'System Tray' which can be used to stop/start the server.

You should setup an ODBC link to this server as per Appendix 2 except set the servername to 'localhost'.

The DataMajor database resides in /usr/local/mysql/data/premvet. This sub-directory should be moved over to your PC that you want to run queries on - you DON'T need to copy the database if your PC is local to the server - this is only where you wish to look at the data on a remote PC e.g. at home.

Copy via CD

To cut the database to CD use the following command for Unix Systems

mkisofs -D -l -L -r -f /usr/local/mysql/data/premvet | cdrecord -v -eject -

If you are cutting the CD on a Windows PC then refer to the documentation that came with the CD burner.

You can then copy the data from the CD into install\data\premvet on your house PC or laptop.

Copy via Samba/VisionFS

In this case, the PC (or laptop) has to be connected to the server. Browse the Unix host for /usr/local/mysql/data/premvet and drag it over to install\data\premvet on the PC.

Copy via FTP

Use an ftp client e.g. Cute FTP (on utility CD), navigate to /usr/local/mysql/data/premvet and 'get' all the files in that sub-directory. If you use the ftp client shipped with Windows make sure to select 'binary' as the filetype.

Copy via Jaz or Zip disk

Insert a DOS jaz (or zip) cartridge into the Unix server, mount the disk, copy the data and un-mount it e.g.:

• mount -f DOS /dev/jaz /jaz
• copy -r /usr/local/mysql/data/premvet /jaz
• umount /jaz

When you use ODBC (or want to write your own SQL) you will need to know what data is available. This appendix lists all the tables and fields that you can use and report on.

Installing the ODBC Driver

This needs to be done once on each PC that will be accessing the data via ODBC.

The ODBC drivers are located in /usr/local/images/mysql on the Unix server. You should either use Samba/VisionFS to copy the file to your PC or copy the file to a floppy.

There are two versions of the ODBC drivers available:

Generally you will want the 3.51 drivers, the older version is there for some applications not supported under 3.51 e.g. Crystal Reports version 7.

Copy the driver to your PC and double click it the install wizard will start and install the drivers.

See the next section for configuring them.

Setting up OBDC Driver

Make sure the drivers are installed as per the previous section.

Running locally - If you are running MySQL locally on your PC, that is you have a copy of the Data physically located on your PC, follow the instructions below except for Host/Server Name enter 'localhost' rather than the name of the Unix server.

Go to Start, Settings, Control Panel and click on ODBC32 or ODBC Data Sources. This will bring up a box with a number of tabs on and various names of data sources.

1. Click on User DSN
2. Click on the Add button,
   scroll down till you see 'MySQL ODBC 3.51 Driver' and
   click it so it is highlighted. Click Finish

3. A new box will come up with the possible settings on it.
   Type in 'Premvet Data' under the Data Source Name.
   Leave Description blank.

4. Under Host/Server name enter the name of your Unix server.
5. Enter 'premvet' beside 'DataBase Name'
6. For User and Password enter vet and vet.

7. In the options box make sure there is a tick beside:
   Change BIGINT to INT,
   Don't prompt on connect.
   

8. To check everything is working click 'Test Data Source', you should see a box appearing: "DataSource 'Premvet Data' Connected Successfully".

Click OK and then OK again.

• Go to System DSN and repeat steps 3-7.
• Go to File DSN and repeat steps 3-7

Click on OK.

This should setup your ODBC and allow you to output from and input to Premvet.

Joins

If you are using ODBC to manipulate the data you MAY need to specify how the data from each table should be 'linked together'. In SQL parlance this is a 'Join'. In general, if you are linking :

• Animals with clinical or animals with recalls then join on the field PVID.
• Animals to Clients join on ClientKey
• User ID to User Name join on UserID
• Analysis code to Analysis Description join on AnalysisCode.

It is worth mentioning - do you really need that join? Join's do use up resources and the reports take longer to run - if you are looking at reports consider not including the client name - will just the record number do? - do you need the full name of the user or will User ID do?