Creating Static Reports Using SQL

The steps involved in creating a static SQL report are described as a running example in the following sections:

1. Write the SQL query of interest. For details on this step, see Writing the SQL Query.
2. Add parameter tags to the query. For details on this step, see Adding SQL Parameterization.
3. Create the base report definition as a set of INSERT statements (one of which is for the query) in the Report table. For details on this step, see Creating the SQL Base Report Definition.
4. Insert the dimensions for the report into the ReportDimension table. For details on this step, see Adding the SQL Report Dimensions.
5. Add the report parameters corresponding to the query parameter tags in the ReportParam table. For details on this step, see Adding the SQL Report Parameters.
6. Display the report name in the Reports module, then run and display or export the report. For details on this step, see Displaying and Exporting the SQL Report.

This running example creates a static report of site visitors with the following attributes:

• Registration occurred between 3/1/2000 and 3/30/2000.
• The UserType equals 1 (the user is a registered user).
• An e-mail address was provided during registration.

Once the report is run, it will display the UserID, DateCreated, DateRegistered, Email, FirstName, LastName, TelephoneNumber, UserTitle, and UserType attributes of the first 25 matched users sorted by e-mail address.

Note

• The Microsoft Commerce Server 2002 Software Development Kit (SDK), which is included with Commerce Server 2002, contains six SQL script files that enable you to create static and dynamic reports that query either SQL or OLAP data sources. The script files to create these reports are located in the installation folder under \Microsoft Commerce Server\SDK\Samples\Business Analytics\Scripts. For more information, see New Report Scripts.

Copyright © 2005 Microsoft Corporation.
All rights reserved.