Creating Custom Reports

This section describes the methods for creating custom reports. There are three types of reports, static, dynamic, and grouped. Static reports are based on either Structured Query Language (SQL) or multidimensional expression (MDX) queries of SQL or online analytical processing (OLAP) databases, respectively. Dynamic reports are based on an Extensible Markup Language (XML) framework that includes either an SQL query or a reference to a cube and information on how to render the query results in a pivot table view. Grouped reports are based on either SQL or MDX queries of an SQL or OLAP database, sliced on one or more dimensions.

Static report definitions consist of a query and parameter definitions. When a static report is run, the parameters are presented to the operator and then passed to a rendering component. The rendering component generates an HTML document from the query results. Completed static reports are stored as HTML files on the computer that houses Commerce Server Business Desk. These reports can be opened through Business Desk and are displayed in Microsoft Internet Explorer.

Dynamic report definitions are stored as queries, and the pivot table view is stored in XML format in the Commerce Server Data Warehouse. Query results are displayed in the Microsoft Office XP Web Components (OWC) Pivot Table component.

Grouped report definitions are similar to static report definitions, except they also include the report grouping dimension. Grouping on multiple dimensions will be supported in the order specified for the row dimensions defined in the report definitions.

Definitions for the predefined reports are contained in the files CSReports.sql and CSReports2.sql found in the Microsoft Commerce Server 2002 installation directory. The CSReports.sql file populates the report tables in the Data Warehouse for Microsoft Commerce Server 2002 Standard Edition reports. The CSReports2.sql file populates the additional report tables required for Microsoft Commerce Server 2002 Enterprise Edition reports. A Data Transformation Services (DTS) report preparation task is available to process OLAP cubes and execute stored procedures to prepare imported data for reporting.

It is strongly recommended that you use Windows Authentication to access your report databases. Further, you should use the appropriate database script to set the relevant database permissions. For more information, see Securing the Data Warehouse.

You use Business Desk to run reports and display query results for static and dynamic reports. For more information about using Business Desk to run reports, see Running Reports. Plus, in Commerce Server 2002 you can use Business Desk to create new dynamic reports. For more information about using Business Desk to create new dynamic report definitions, see Creating a New Dynamic Report Definition.

Once you have created a static report, you can propagate it to another computer using the Commerce Server Site Packager.

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.

This section contains:

See Also

Analysis/Reporting Schema

Analysis/Reporting XML Structures

AsyncRpt2 Object

ReportRender2 Object

Running Reports

Creating a New Dynamic Report Definition

Copyright © 2005 Microsoft Corporation.
All rights reserved.