Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 6, 2003

Reporting Options for Analysis Services Cubes: MS FrontPage 2002

By William Pearson

About the Series ...

This is the eleventh article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("Analysis Services"), with each installment progressively adding features designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

In addition to MSSQL Server 2000 and MSSQL Server 2000 Analysis Services, of which we have made repeated use in the previous articles of the series, additional application considerations apply for this tutorial because it introduces another MS Office component, FrontPage 2002. For those joining the series at this point because of a desire to work with Analysis Services and its components from a FrontPage perspective, it is assumed that Analysis Services is accessible to / installed on the PC, with the appropriate access rights to the sample cubes (which are provided in a Typical installation of Analysis Services). In addition, we will assume that we are performing all steps on a Windows 2000 - family PC, although the steps will be quite similar with later operating systems.

For more information on the hardware/software requirements to prepare for this tutorial, see our last article, Reporting Options for Analysis Services Cubes: MS Excel 2002.


While the majority of the articles of our series to date have focused upon the design and creation of cubes within Analysis Services (see Articles One through Nine of the Introduction to MSSQL Server 2000 Analysis Services series), we began in Article Ten to discuss reporting options for our cubes. This comes in response to the expressed need of several readers for options in this regard - options beyond the mere browse capabilities within Analysis Services.

In our last lesson, Reporting Options for Analysis Services Cubes: MS Excel 2002, we presented an introduction to using Excel PivotTable Reports to retrieve and display information from an OLAP cube. We exposed PivotTable Report features that are available with Microsoft Excel 2002 (most of which were available in Excel 2000) for creating robust and flexible reports. We explored setting up a connection to an OLAP cube, creation of the PivotTable Report, and the general navigation of member information and cube data. We practiced drilling down to (and zooming up from) details of reporting summaries, then exposed the use of intersected dimensions to make the PivotTable Report truly multidimensional. We discussed a few formatting options at relevant junctures in our exploration of the Excel 2002 PivotTable Report.

In this article we will focus our exploration on Office PivotTable Lists; we will design the reporting mechanism from the ground up, using Microsoft FrontPage as the design environment. We will expose various options available to the PivotTable List designer to control the capabilities afforded to the information consumer, specifically through placing restrictive setpoints in design mode, and enforcing those setpoints in the browser through which information consumers access the PivotTable List.

Reporting Options for Analysis Services Cubes: The Office PivotTable List and FrontPage 2002

In this lesson we will continue our exploration of reporting options for Analysis Services cubes. This has become a popular topic, which, as I mentioned in Article Nine, generates many e-mails each week in my inbox. As I have mentioned before, my focus is to offer options for obtaining reports from Analysis Services cubes within a range of similar business intelligence capabilities. My intent is therefore to review the process of establishing connectivity and enabling reporting capabilities for each of the options, and not to compare the product features themselves, to any significant extent. As I also stated in the last article, I will consider providing a similar examination of other reporting products at a later time, based upon any suggestions I receive from readers, if this turns out to be useful.

In this tutorial, we will extend our exploration into the use of MS Office applications for cube reporting to an examination of some of the options offered by the Office PivotTable List (the FrontPage "equivalent" of the Excel PivotTable Report) for report building with Analysis Services cubes. As we did with the Excel PivotTable Report in the first of our Reporting Options articles, we will present an introduction to designing PivotTable Lists in FrontPage 2002, to retrieve and display information from our cubes, first discussing the steps needed to define our data source, and to establish a connection to the cube. Next, we will expose the layout of the PivotTable List and its navigation, and explore its use in browsing and reporting our cube data. We will discuss the nesting of dimensions in PivotTable Report axes to achieve multidimensional reporting within the classical two-dimensional presentation of print media and PC screens, as well as general formatting considerations at relevant points in the tutorial.

Our objectives in the article include:

  • An introduction to the features that are available within Office PivotTable List, and the options and capabilities therein that provide for creating robust and flexible reports;
  • A practical walkthrough of the setup of Microsoft FrontPage 2002 connectivity and other preparatory steps;
  • Exploration of additional facets of the basic use and navigation of the various components that comprise PivotTable List functionality;
  • A focus on designing a PivotTable List, "from the ground up," to provide flexibility in information delivery to meet business needs;
  • A discussion of some of the options for the incorporation of controls over the capabilities afforded to information consumers.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM