Microsoft SQL Server - Stonefield Query SDK

This tutorial leads you through the process of creating a version of Stonefield Query for the Northwind database that comes with Microsoft SQL Server.

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 Stonefield Query Studio and create a new project by clicking the New Stonefield Query Project button ().
Enter a project name (for example, "SQL Server Sample"), leave Short name at the default (same as Project name), and specify a folder for the new project's files. Click Next.
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 Finish button to create the project and 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 in the Databases panel. Click the + in front of the Northwind node 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, click the Special button in the properties pane and turn on 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.

Click the Calc button 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 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. Click the Special button and turn on the Display Field From Related Table option, then select Categories from the Table drop-down list and CategoryName from the Field drop-down list.
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 report 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.