Creating Static Reports Using SQL

  • Article

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) into 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 into the ReportParam table. For details on this step, see Adding the SQL Report Parameters.

  6. 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.

  • An e-mail address was provided during registration.

After the steps have been successfully executed, the report will be listed in the Reports module in Commerce Server Business Desk.

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 Email address.


All rights reserved.