Creating Custom Reports

Article
11/12/2009

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:

Creating Static Reports Using SQL. Describes creating static reports using parameterized SQL query statements.
Creating Static Reports Using MDX. Describes creating static reports using parameterized MDX query statements.
Creating Dynamic Reports. Describes creating dynamic reports using the XML framework.
Creating Grouped Reports. Describes updating grouped report SDK scripts and creating new grouped reports.
Packaging Custom Reports. Describes packaging custom static reports so that they can be unpacked and run on another computer.

Creating Custom Reports

See Also

Additional resources