Creating a sales report in Excel

Introduction

Microsoft Excel for the Mac includes the ability to import live data from a database using an ODBC connection. This tutorial will describe how you can import sales data from an on-line sales database into your Excel spreadsheet. Once the data is in the spreadsheet, you can use Excel to perform further analysis of your sale data.

These instructions assume you have already downloaded and installed the appropriate ODBC driver for your database and you have configured a DSN (data source name). If you have not yet purchased a license key, you will only see the first 3 rows from any database query.

Please contact us at support@actualtech.com if you have any questions or suggestions regarding these instructions.

Connecting to your sales database

Let's suppose you want to see how many products you are selling, according to category and country. There are many different shopping cart databases you might be using (such as osCommerce), but they all have similar tables defined for storing customer, product, and sales information. We will use the NorthWind example database, whose defined tables should map easily to your own database tables. Please contact us if you have any problems using the tables in your database.

In this example, we will start with a new spreadsheet, although you could import your data into an existing spreadheet if you wanted.

Start Microsoft Excel and select File->New Workbook from the menu. Position your cursor in cell A1, and select the Data->Get External Data->New Database Query...

new database query

When the "iODBC Data Source Chooser" dialog is displayed, select the DSN you configured previously and press OK:

select your DSN

After you enter your database's user ID and password (if needed), you will see the main window for Microsoft Query. MS Query is included with Microsoft Office 2004 (a separate download in MS Office X), and is used specify the information you want imported into Excel. One of the Windows will include a list of tables defined in your database:

table list

For our report, we will select the following tables one at a time and press the "Add Table" for each one: Categories, Products, Orders, Order Details, and Customers:

tables

The tables we have selected are related to each other. For example, the Categories table and the Products table are related to each other by the fact that they both contain a field named CategoryID. We need to link the tables together by drawing a line between the related tables (this is known as a "join").

When we drag a line between one table to the related table, MS Query will display a dialog like this:

join

For this example, we will just accept the defaults and press OK.

After we have drawn the links between the tables, the next step is to specify the fields from each table that we want to appear in our results. We add a field to our results by double-clicking the field name within the table:

selected column

We'll then go ahead and select the following columns:

selected columns

Now that we've specified the columns that will appear in our results, we can try out our query by pressing the "Test!" button. If there is no error in our query, we should see the records that will be imported into the spreadsheet:

test results

Once we're satisfied with our query, we press the "Return Data" button in the lower right corner of the window to import our results into Excel. Excel will then prompt us for the location to place the results:

Excel prompt

We'll just accept the default and press OK. The results are then imported into the spreadsheet:

Excel spreadsheet

Once the data is in your spreadsheet, you can perform all the functions on the data supported by Excel, such as summation. You can also re-run the query by right-clicking on your results and selecting Refresh Data:

Refresh Data

For more information

To learn more about the Excel database connectivity, please visit our MS Office Resource Center.