Microsoft SQL Server

This tutorial leads you through the process of creating a version of Stonefield Query for the Northwind database that comes with Microsoft SQL Server. Note that if you're using SQL Server 2005 or later, it doesn't include this database, so either choose a different database or download Northwind from http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en and execute the scripts included in this download to generate the database.

Start the Stonefield Query Configuration Utility and create a new project by clicking the New Stonefield Query Project button ().
Create a new database from the Northwind database that comes with SQL Server. Start by selecting the "Databases" node in the TreeView and clicking the Create button (). Enter "Northwind" for the caption. If you have an ODBC data source already set up for this database, choose ODBC and DSN, select the data source from the drop-down list, and enter the database user name and password. Otherwise, select Connection string and enter a connection string similar to the following:
```
driver=SQL Server;server=servername;database=Northwind;
uid=username;pwd=password
```
where servername is the name of the server, username is the user name, and password is the password.
Click the OK button to begin the discovery process.
During the discovery process, you're notified that the Employees table is involved in a self-join and asked to enter the name of a subtable to create. Enter "Managers" and choose OK.
If the discovery process was successful, you'll see the Northwind database under the "Databases" node in the TreeView. Click the + in front of the Northwind and Tables nodes to see the tables in this database.
Click the + in front of the Orders table, then click the + in front of the Fields node, and click the ShipVia field. This integer field is really an enumerated field; let's assume that 1 means Fedex, 2 means UPS, and 3 means Mail. To define ShipVia as an enumerated field to Stonefield Query, select the Special page in the right side of the Configuration Utility and check the Enumerated Values option. Then, click the Add button to add a new value to the list, and in the grid, enter "1" in the Value column and "Fedex" in the Description column. Do the same for 2 (UPS) and 3 (Mail).
Because the extended price for an order item is a derived value (unit price multiplied by quantity), it isn't stored in the database. However, the user may want to query on that, so let's create a calculated field for it. Expand the Order Details table, expand the Fields node, and click the Create button to create a new field. Enter "total_price" for the name, set the data type to Currency, enter "Total Price" for Caption, set Format to "$," and enter "999,999.99" for Picture.
Select the Calc page and enter the following for Output Expression:
```
UnitPrice * Quantity
```
Turn off the Stonefield Query Expression option, since this expression can be evaluated by SQL Server.
Click "newfield" in the TreeView to save this change. You'll see the name in the TreeView change to "total_price."
Click the + in front of the Products table, then click the + in front of the Fields node, and click the CategoryID field. This field contains foreign key values to the Categories table, so we would rather display the CategoryName field from that table than the foreign key values. Select the Special page in the right side of the Configuration Utility and check the Display Field From Related Table option, then select Categories from the Table drop-down list and CategoryName from the Field drop-down list.
Expand the Configuration node and click Application. Select the Application Name property and enter "Stonefield Query for Northwind" as the value.
Click the Launch Stonefield Query button ().
Since this is the first time Stonefield Query for Northwind is run, the Setup dialog appears. Leave the Location of Stonefield Query files option at the default (the same directory where you created the Stonefield Query project) and click the Finish button.
Create a new report with the following fields: Company Name from Customers; Order ID, Order Date, and ShipVia from Orders; Quantity, Unit Price, and Total Price from Order Details; and Product Name from Products. When you preview this report, you'll see that ShipVia is displayed as Fedex, UPS, or Mail rather than 1, 2, or 3 and that the Total Price for each item is shown.