Linking to Microsoft Excel

Overview

The database used by Smart (in most cases, Microsoft SQL Server) is designed (subject to access control) to be easily accessed by other programs. If you make use of this capability you will be able to quickly extract information which is not available in standard Smart reports.

This access relies on two industry standards, ODBC (Open Database Connectivity) which enables the program to connect to the Smart database, and SQL (Structured Query Language) which provides a way to specify what information you wish to extract.

Microsoft Excel uses an addon called Microsoft Query to simplify the process of connecting and extracting data. As your confidence and experience grows you can use Microsoft Query to create powerful queries. You can also seek the assistance of Smart Support to formulate complex queries (this assistance is usually covered by your maintenance agreement.

Although this section describes the use of Microsoft Excel, similar principles apply when linking to the Smart database from other applications which use ODBC and SQL (there are many of them).

Accessing the database as described in this section means that you are prevented from updating information in the Smart (Skorpion can take no responsibility for such updating).

The instructions below apply to Microsoft Excel 2000. Other versions of Excel may work differently but the principles are the same.

Prerequisites

1. You must have Microsoft Excel and Microsoft Query installed on your PC. To see whether you have Microsoft Query installed, open Excel with a new, blank, spreadsheet, and select \Data \Get External Data \New Database Query. If any of these selections are greyed out, you do not have Microsoft Query installed. It should be available on your Microsoft Office CD.

2. You need to establish an ODBC connection to the Smart database (you only need to do this the first time you connect). If you are the only person who will use this facility on your PC, follow the instructions below. However, if different people use different logins on your PC, and they also need access to Smart from Excel, your system administrator needs to create a 'System DSN' data source so that other users on your PC don't have to go through the same setup process.

3. Your version of Smart should be at version 3.32 or later to be able to access Smart as described below. If you have an earlier version of Smart your database can be upgraded to allow Excel access without upgrading your Smart program. Please contact Smart support for assistance.

Extracting Data

1. Create a new spreadsheet in Excel ( \File \New)

2. Select \Data \Get External Data \New Database Query from the top Excel menu. (If this selection is greyed out, you must install Microsoft Query from your Microsoft Office CD.)

If you have previously created and saved a query, select it from the Queries tab.
Check the Use the Query Wizard box below the list unless
- you are familiar with Microsoft Query and Smart, or
- you are running a query supplied by SMART support.
If SMART does not already appear in the list of Databases, create a new data source.
Select SMART in the list of Databases.

3. If you are not using the Query Wizard or the following window does not appear, go to the next section.

Using the Query Wizard

The following window will appear if you are using the Query Wizard:

This window and the next two will enable you to select columns (see below) and filter and sort them. Finally you will be able to select from these options:

If you are confident that your query is correct you should take the first option (Return Data to Microsoft Excel). Otherwise, take the second. The OLAP option is for advanced use only.

Now go to Selecting Tables

Not using the Query Wizard.

This window appears if you start the query without the wizard or if you select \Tables \Add Tables from the Microsoft Query Main Menu.

If you wish to run a SQL query supplied by Smart support, close this window and refer to the Supplied Queries section, below.

Selecting tables for the query

The items in this list are tables or views (sometimes called files) which in turn contain columns (sometimes called fields) containing the information you want.

Many of the items in this list are for system use only. Generally items that will be of interest to you appear in lower case and begin with a capital letter. Items which have been predefined for you include

Customer: Name and address information for Smart customers
VJobs: Jobs and job-related information
VKeys: Location keys, including related service providers, contractors, customers, and locations
VLocations: Name and address for Locations, including the linked customer and Alarm Company
VPatrols: Records of scheduled and completed patrols

Other items may be added from time to time by a Smart upgrade or by Smart support (with your permission).

Generally speaking you will want to select only one item from the list. Items starting with V are views, which combine a number of tables together and shield you from the complexity of the database.

If a + sign appears to the left of the table or view name, click on it to select from a list of columns. Contact Smart support for assistance in identifying which columns to choose.

When you have made your selection(s), click Next to continue. You will eventually reach the main Microsoft Query window:

For detailed assistance with Microsoft Query, access the Microsoft Query help system (press F1 or choose Help from the main menu).

Note that by pressing the SQL button you can view the SQL query which has been automatically assembled by Microsoft Query. Once you become familiar with SQL you may want to use this window to create or modify your own queries. (If required, Smart support staff can email you complex queries which can be pasted into this window - see below). SQL is a powerful business tool and there are many excellent books and courses on this topic.

Supplied Queries

Queries can be supplied by SMART support staff or other suppliers in one of two forms:

Excel queries are supplied as a file with a .dqy suffix, e.g. Your query.dqy. This file contains a standard SQL query with some additional information which allows Excel to connect to your server and format the information. This format is used when you save a query from Microsoft Query.
SQL queries are supplied as a file with a .sql suffix, e.g. Your SQL query.sql. Queries in this format can be run with any SQL query tool, including Query Analyser and WinSQL. To run a query in this format with Microsoft Query, press the SQL button on the Microsoft Query toolbar and paste the query into the window which appears, then press OK. If the query is complex you may be shown the message SQL Query can't be represented graphically. Continue anyway? You can continue but you won't be able to use the query wizards to modify the query.

Running your query

Once you have set up your query, you can view the result by clicking the ! button on the toolbar.

You can save your query for later use by selecting \File \Save. You must save the query if you think you may want to change it and re-run it.

When you are ready to view the results of the query in Excel, choose \File \Return Data to Microsoft Excel. This window will appear:

You can enter the name of the cell where you want the data to commence, or click in the worksheet behind the window to select a cell. Then press OK to transfer the data from the Smart database to Excel.

Depending on the amount of data being retrieved, this can take some seconds to occur. The data has now been copied to Excel. Changes in the Smart database will have no effect on the data in Excel (unless the query is re-run).

If you wish to re-run the query, select \Data \Get External Data \Edit Query or ..\Run Saved Query.

Date and Time Fields

The SMART database stores dates and times in the same column, so for example 27/01/2001 16:59 (a different format may be used, depending on your Excel configuration). To show dates and times in separate columns, make a new column and copy the date/time into it. Then right click on the cell or column and choose Format Cells.. Format one column as a date and the other as a time.

Creating a new Data Source

Create a new Data Source as follows:
- Double click on <New Data Source>. The following window will appear:

Make the entries as shown and click the Connect... button. The following window will appear. Click the Options button to display the lower part of the window.

Complete the entries as follows:
- Server: If the PC you are working on is being used as the Smart database server, you can use the word (local) as shown. Otherwise you must enter the name of your Smart database server. This is displayed at the top of your Smart window, e.g. (in this case (local) is the name of the server.)
- Use Trusted Connection: Clear this box.
- Login ID: Enter Reporter, as shown
- Password: Enter Reporter
- Database: (there might be a slight pause before selections are available in this box). If Smart is not available in the database list, or an access error is displayed, check that your database has been upgraded to Smart version 3.32 or later and that the Reporter access level has not been modified by your system administrator.
Press OK and you will be returned to the previous window.
- Select VJobs from the default table list (line 4.)
- Check Save my user ID unless you wish to prevent other users of this PC accessing the Smart database.
- Press OK to save and continue.
You will be returned to the Choose Data Source window (see above). Double click on SMART to select the Smart database.