OLAP

Build an OLAP Reporting App in ASP.NET Using SQL Server 2000 Analysis Services and Office XP

Jeffrey Hasan and Kenneth Tu

Code download available at:OLAP.exe(373 KB)

This article assumes you're familiar with ASP.NET and SQL Server

Level of Difficulty123

SUMMARY

Many organizations analyze their business-critical data using Online Analytical Processing (OLAP) technology. OLAP-based data mining provides a way to query multidimensional data sets and drill down into the data to find patterns. ASP.NET and the Microsoft Office Web Components (OWC) enable Web-based OLAP reporting. The OWC controls include PivotTable and Chart components that can be embedded in a Web page and scripted by programmers. In this article, the authors build a Web-based OLAP reporting app using ASP.NET, OWC, and SQL Server 2000 Analysis Services to illustrate the process.

Contents

Important Terms and Concepts
Web-based OLAP Reporting App Architecture
Office Web Components
Configuring the OLAP Source for HTTP Access
OLAP Security Access
Building the Application
Connect the Control to the Data Source
Dynamically Generate a Custom PivotTable Report
Save and Retrieve Custom Reports
Using OWC Events
Binding a Chart to the PivotTable Control
Creating Custom Groupings
Client Requirements for HTTP Use
Conclusion

Agrowing number of companies are using online analytical processing (OLAP) and data mining technology to analyze their complex data sets. OLAP is specifically designed for analyzing multidimensional data sets, where patterns and relationships between the data are not immediately obvious. Microsoft® SQL Server™ 2000 Analysis Services is a popular choice for OLAP processing and data mining because it is tightly integrated with SQL Server 2000 and continues to integrate even more tightly with every new release of SQL Server.

As OLAP technology becomes more popular, developers are increasingly being asked to create custom reporting solutions that rely on OLAP-based multidimensional data sources. This is a complicated endeavor since OLAP-based reporting, or data mining, demands flexibility and a high level of interactivity. Data mining, by definition, is not static. Rather, the user must have the ability to construct any view of the data within the constraints of how the underlying data cube is structured. Compare this for a moment with traditional queries on relational database management systems (RDBMSs), which are constrained by tightly defined relationships between the member tables. RDBMS queries can certainly get creative, but only to a point. With OLAP-based data structures, you have much more flexibility in what you can query. Traditional static, canned reports add little value in helping you view and understand OLAP-based data.

Software developers face a four-fold challenge when they are asked to create an OLAP-based custom reporting solution. First, the solution must be able to connect with, and query, a multidimensional data source. Second, it must allow the user to drill down into the data with flexibility and ease. Third, it must let the user save a custom drill-down report because you cannot predict what data view the user finds important. Fourth, the solution must provide a readable interface that includes charts since reporting is more effective when it is visual.

Allow me to add an optional fifth requirement: the OLAP-based reporting solution should be Web based; that is, it should be implemented as a server-based Web application and be accessible from a Web browser (either over the Internet or on an intranet). With five stringent requirements, what's a software developer to do?

As it turns out, Microsoft provides a set of components called Office Web Components (OWC) that help you build sophisticated and user-friendly custom OLAP-based reporting solutions for the Web. The OWC components are a collection of COM controls for publishing spreadsheets, pivot tables, and charts to the Web. They provide the same rich, interactive experience for the user as does Microsoft Excel. The OWC components also expose a rich programmatic interface, which provides a high degree of functionality and flexibility around which you can build a custom solution. Figure 1 shows the OWC PivotTable® component, which allows you to connect to an OLAP data source and run queries against it using a convenient drag and drop interface.

Figure 1** OWC PivotTable Component **

The purpose of this article is to address the needs of developers who are using ASP.NET and Office Web Components to develop a custom OLAP-based reporting solution for the Web. We will present an application architecture that incorporates XML-based Web Services, and we'll show you how to build it and configure your OLAP data source for access over HTTP. In addition, we will describe how to deploy the solution to your users. Most Web applications are fully installed on a Web server and require no interaction with the client during the deployment process. However, OLAP and OWC introduce special requirements for the client who wants to use your custom solution.

Important Terms and Concepts

OLAP technology is replete with specialized terms that can be a barrier to understanding its real benefits. This article assumes you have general familiarity with OLAP and specific familiarity with SQL Server 2000 Analysis Services. Even so, before proceeding we should clarify the important terms and concepts both in this article and within the wider world of OLAP and data mining.

First, it is important to distinguish between OLAP and data mining technologies. An OLAP-based data source, or repository, may be subdivided into one or more cubes, which are multidimensional data structures that may be queried using MDX. A cube contains a set of attributes called dimensions, which roughly correspond to database fields, except that they also contain a hierarchical collection of levels. For example, an annual time dimension may be subdivided into levels of quarters, months, and weeks. A cube also contains a collection of measures, which are the actual data values and are typically numeric. For example, a retail cube will allow you to view unit sales (measure) according to store location (dimension), and time of year (dimension).

SQL Server 2000 Analysis Services enables you to perform OLAP processing, courtesy of a Microsoft Management Console (MMC) snap-in called the Analysis Manager. This is simply a pivot table interface that is integrated into Analysis Services. OLAP-based data is usually viewed using a pivot table, which allows you to drag and drop multiple dimensions and measures into a spreadsheet-like layout. The PivotTable control translates the drag and drop operations into MDX queries, which it relays to the OLAP data source and, in turn, receives a resultset.

Analysis Manager installs a PivotTable service on the database server, which includes an OLE DB provider that allows connections to OLAP data sources. The provider is named Microsoft OLE DB Provider for OLAP Services 8.0. Without it, no connection to the OLAP data source would be possible. The connection string for this provider includes a DataSource attribute for the repository name and a DataMember attribute for the name of the cube that you are connecting to. The PivotTable service must also be installed on the client machine; otherwise, the client's PivotTable control will be unable to communicate with the OLAP data source.

Web-based OLAP Reporting App Architecture

Figure 2 describes the high-level architecture of our Web-based OLAP reporting application. The reporting application architecture includes three pathways, which are numbered to match the sequence of the application's workflow. They combine to provide the following feature set:

• Connection between a client-side PivotTable control and an OLAP data source over HTTP, through an XML Web Service.
• Dynamic querying of the OLAP data source directly from the client-side PivotTable control (without any intermediate components).
• The ability to save and retrieve custom PivotTable reports to a standard relational database structure, through an XML Web Service.

Figure 2** OLAP Architecture **

XML is the central player in this application architecture. The OWC PivotTable component natively serializes its contents to XML. Not only can you write out the data as XML, but you can also load the PivotTable component with XML data, as long as it conforms to the XML schema that is defined for Excel. This property allows ASP.NET developers to create XML-based Web Services that communicate bidirectionally with an OWC PivotTable control. One Web method can generate formatted XML for populating the PivotTable control (Pathway 1), while another can accept the serialized output from a PivotTable control and save it to a persistent medium such as a database or a file (Pathway 3).

Once a connection has been made between the PivotTable control and the OLAP data source, then the user is free to start assembling a custom view of the data using the PivotTable control's drag and drop UI. Every time the control is updated, it dynamically creates a multidimensional expression (MDX) query and sends the request directly to the OLAP data source through the HTTP gateway (Pathway 2). This communication occurs independently of the Web server; the Web page talks directly to the database. In this architecture, the role of the Web server is to establish the initial connection between the PivotTable control and the OLAP data source, and to enable saving and retrieving custom PivotTable reports.

Office Web Components

OWC version 10 provides out-of-the-box functionality that is not only powerful, but is also well known to the user. Your custom solution can thereby gain quicker acceptance from users who are already familiar and comfortable with Excel. Figure 3 summarizes the OWC components (version 10) that ship with Office XP. Note that Office 2000 shipped with a prior version of OWC (version 9), which provides much more limited programmatic interfaces and user interfaces. Much of our discussion of OWC version 10 also applies to OWC version 9, although not everything that we discuss will be supported in the earlier version.

Figure 3 Office Web Components (version 10)

ASP.NET developers have few resources to turn to for incorporating OWC components into a Web project. Most of the online code samples assume that you are interacting with the OWC components using a client-side scripting language, such as VBScript. While this approach is useful, it ignores a more powerful approach that ASP.NET makes possible. Namely, you can create a server-side instance of an OWC component, make a connection, set properties, and then use this instance to generate XML data for a separate, client-side instance of the PivotTable component. This programming logic is contained inside the OLAP Data Access Objects portion of the architecture diagram (see Figure 2), along Pathway 1. The XML can then be delivered to the client-side component using an XML-based Web Service, also shown in Pathway 1.

Figure 4 summarizes the OWC components that we will work with in this article—namely, the PivotTable and Chart components. The Spreadsheet and DataSource controls are not covered here because they do not factor into the architecture that we're discussing. Figure 5 and Figure 6 summarize the programmatic interfaces of the PivotTable and Chart components, respectively.

Figure 6 Chart Component's Programmatic Interface

Figure 5 PivotTable Component's Programmatic Interface

Figure 4 PivotTable and Chart-related Objects

The PivotTable component has a complicated interface, but the most important aspect to remember is the XMLData property. This is an XML representation of the complete PivotTable contents, including the connection information to the OLAP data source. The XMLData also stores the dimensions and measures currently in view and the formatting that has been applied. The XMLData property may be set and retrieved, and it is an important means of customizing a PivotTable component dynamically.

Configuring the OLAP Source for HTTP Access

Before we can begin building the Web-based OLAP reporting application, we must configure the Analysis Services data source for access by Microsoft Internet Information Services (IIS). The IIS Web site must be set up on the same server where Analysis Services is installed, and it must contain a single file called msolap.asp. This file provides the bridge between requests coming over an HTTP port and the Analysis Services data source. The file is located under the path \Program Files\Microsoft Analysis Services\bin. We recommend that you configure the data source for access over a secure connection (HTTPS) using a Secure Sockets Layer (SSL) certificate. This step requires that you purchase an SSL certificate for the Analysis Services server. You can also use a nonsecure (HTTP) connection to access the data source, but we don't recommend this approach.

Once IIS is installed on the Analysis Services server, you can configure a new Web site for HTTPS access. First create a new Web site directory outside of the default Web root directory (\Inetpub\wwwroot\). We recommend that you stop the default Web site for security purposes. In addition, you should close port 80 on your firewall and open port 443 for HTTPS access once the SSL certificate is installed.

Copy the msolap.asp file from the \Program Files\Microsoft Analysis Services\bin directory into the new Web site directory, and use IIS Manager to configure the new Web site directory as a Web Application and add a Host Header Name for the Web site (for example, olap.mycompany.com).

Purchase and install the SSL certificate on the server. The Web site must already be accessible over port 80 during the purchase verification process. Once the certificate is installed, you can close port 80 and open port 443 on your firewall. Use IIS Manager to set Directory Security so that Anonymous Access is disabled and Basic Authentication is enabled. Finally, reset IIS to apply the changes.

Once HTTPS access has been set up, you must update the OLE DB connection string that points to the OLAP data source. The format of the connection string is as follows:

Provider=MSOLAP;user id=Domain\Username;password=Password;Data Source=https://ServerName;Initial Catalog=OLAPDataSourceName

For more details, consult the Knowledge Base article 279489, "How to Connect to Analysis Server 2000 By Using HTTP Connection".

OLAP Security Access

Security credentials are essential once your OLAP data source becomes accessible over HTTPS because your data source is now potentially available to anyone who can browse your Web site. For example, a remote user can open Excel and connect a PivotTable to your OLAP data source simply by providing the URL for the Analysis Server and the name of the Initial Catalog.

Analysis Services implements cube security roles based on Windows NT® user accounts. Basically, you create one or more local Windows NT user accounts on the server and assign them to a SQL Server database role. You then assign the database role to a cube role and assign it to the specific cube. The cube role essentially inherits the Windows NT user ID and password credentials. You can then add the user ID and password credentials to the OLAP data source connection string.

Note that one OLAP data source may contain multiple cubes. You can connect to a specific cube by setting the OWC PivotTable control's <DataMember> XML tag value to a valid cube name (explained in the next section). This cube must be assigned to a role that corresponds to the credentials in the OLAP data source connection string.

Building the Application

Now it is time to build this application using ASP.NET and OWC 10. The application's user interface is contained within a single Web Form called owc10.aspx, shown in Figure 7. The interface contains an embedded client-side OWC PivotTable control as well as a set of buttons for invoking connections to an OLAP data source and for saving and retrieving custom reports. This functionality is directly provided by a set of Web methods in the XML Web Service found in wsOLAP.asmx. The user interface hooks into these Web methods using a set of client-side JavaScript functions in the olap.js script file. These JavaScript functions delegate most requests to the Web Service methods by making client-side calls to the methods. This is accomplished using a DHTML behavior file called webservice.htc.

Figure 7** Web-based OLAP Reporting App **

The sample app, called OLAPReport, is available for download from the link at the top of this article as a Visual Studio® .NET 1.1 project. It uses the Foodmart 2000 data warehouse as its OLAP data source and the Sales cube as its data member. Foodmart 2000 installs with SQL Server 2000 Analysis Services.

Once you have downloaded and installed the project files, there are a few steps you need to follow for the compiled project to work correctly. First, you will need to modify the OLE DB connection string, which the application uses to reference the OLAP data source. The connection string is stored as a custom application setting in the Web.config file:

<appSettings> <add key="OLAPConnectionString" value="Provider=MSOLAP.2; Data Source=https://ServerName;Initial Catalog=Foodmart 2000> </appSettings>

Notice that the connection string contains no security credentials (such as a User ID and password). These credentials are only required if you have implemented security roles in your Analysis Server. We encourage you to add security roles, as described earlier. Once you have them in place, simply add the credentials to the end of the connection string.

Remember that you will need to configure your Analysis Server for HTTP access in order for this connection string to work. These steps were also described earlier. The project is initially set to use https://localhost/olap as its data source, so you will need to create a virtual directory named olap under the default Web site and copy the msolap.asp file to that directory. As a shortcut, you can simply replace the Data Source value with your machine name, omitting the leading https://. This will allow a standard connection to the OLAP data source, rather than an HTTP connection. This works for demonstration purposes, but remember that if you don't enable HTTP access, you cannot expose your Analysis Server to users who are outside your network domain.

Finally, before proceeding you may need to set a reference to the OWC10 COM component from the OLAPReport project. (The setup project should take care of this for you, but it is useful to know the steps in any case). To add the reference, open Solution Explorer, then right-click the project icon and select Add Reference from the popup menu. Once the dialog opens, switch to the COM tab, and locate the component named Microsoft Office XP Web Components (OWC10.DLL). Click the Select button, and then click OK. Visual Studio .NET will then generate a .NET wrapper for the component and will add a project reference to OWC10.

Connect the Control to the Data Source

Now let's implement each of the application's three features in turn. First, let's discuss Pathway 1—how to set a connection between a client-side OWC control and the OLAP data source. This function is initiated using the Set Connection button on the user interface (see Figure 7). This button calls the JavaScript function initializePivotTable and passes in the name of the data cube to connect with—in this case, "Sales."

The JavaScript function in turn makes an asynchronous call to the InitializePivotTableXML Web method (enabled by the DHTML behavior called webservice.htc, which we introduced earlier). The Web method connects to the OLAP data source and then generates the PivotTable's XMLData. The Web method's output is handled by a JavaScript function called onInitializePivotTableResult. Figure 8 shows the code for the JavaScript functions, while Figure 9 shows the code for the Web method. The code in these two figures is simple and, best of all, it is reusable. Quite simply, these functions allow us to generate custom XMLData on the Web server for delivery to the client-side PivotTable control.

Figure 9 Generate XMLData for a PivotTable Control

<WebMethod()> Public Function InitializePivotTableXML(ByVal _ strDataMember As String) As String Dim m_XML As String Dim strOLAPConn As String = _ ConfigurationSettings.AppSettings("OLAPConnectionString") Try Dim objPT As PivotTableClass = New PivotTableClass objPT.ConnectionString = strOLAPConn objPT.DataMember = strDataMember m_XML = objPT.XMLData objPT = Nothing Catch err As Exception m_XML = "<err>" & err.Source & " - " & err.Message & _ "</err>" Finally End Try Return (m_XML) End Function

Figure 8 Connecting to an OLAP Data Source

function initializePivotTable(strDataMember) { // This function calls the InitializePivotTableXML() Web // method var iCallID = service.svcOLAP.callService (onInitializePivotTableResult, 'InitializePivotTableXML', strDataMember); } function onInitializePivotTableResult(result) { // This function handles the InitializePivotTableXML() // Web method result text = result.value; // result string // Evaluate return result if (!result.error) { // Assign the XML to the PivotList XMLData value frm1.PivotTable1.XMLData = text; } else { alert("Unhandled error - " + result.errorDetail.code + " " + result.errorDetail.string); } }

In the process of establishing a connection, we are effectively initializing the OWC control with a blank report for the Sales data cube. The empty report populates the Field List with all the available fields and measures for the data cube. The user can then create a custom report by dragging and dropping fields into the Row and Column fields, and Measures into the Totals fields in the PivotTable. Every time that the user drops a field onto the PivotTable, the control sends a query directly to the data source, which in turn sends back more query results. This exchange is conducted independently of the Web Service, which is only required for establishing the initial connection between the control and the data source. This exchange is represented by Pathway 2 in Figure 2.

Dynamically Generate a Custom PivotTable Report

Now let's dig deeper into the PivotTable object model by generating a custom report programmatically. The custom report we want to build allows the user to select two cities and to compare the Store Sales for the Drinks Product Family. We use the same approach as Pathway 1, which means we call a Web method to generate XMLData that describes the report. We then assign the XMLData from the Web method to the embedded control's XMLData property. The interface provides two dropdown lists of city names and a button called Load Custom Report (as shown in Figure 7). This button fires off a JavaScript function called LoadCustomReport, which accepts as input the two city names from the Web Form. This function, in turn, calls a Web method called LoadCustomPivotTableReport, which programmatically assembles the XMLData for the custom report. Finally a JavaScript handler function receives the XMLData from the Web method and then assigns it to the embedded PivotControl's XMLData property. Figure 10 shows the code for the Web method.

Figure 10 LoadCustomPivotTableReport Web Method

<WebMethod()> Public Function LoadCustomPivotTableReport(ByVal _ strCity1 As String, ByVal strCity2 As String) As String Dim m_XML As String Dim strOLAPConn As String = _ ConfigurationSettings.AppSettings("OLAPConnectionString") Dim objPT As PivotTableClass = New PivotTableClass Dim objPTView As PivotView Dim fldCity, fldName, fldProdFamily As PivotField Dim fSetCustomers, fSetProduct As PivotFieldSet Try objPT.ConnectionString = strOLAPConn objPT.DataMember = "Sales" objPT.AllowFiltering = False objPTView = objPT.ActiveView objPTView.TitleBar.Caption = "City Comparison of Drink Sales" ' Define the column elements objPTView.ColumnAxis.InsertFieldSet(objPTView.FieldSets("Time")) objPTView.ColumnAxis.FieldSets("Time").Fields("Year").Expanded = True ' Define the row elements fSetCustomers = objPTView.FieldSets("Customers") objPTView.RowAxis.InsertFieldSet(fSetCustomers) fSetCustomers.Fields("Country").IsIncluded = False fSetCustomers.Fields("State Province").IsIncluded = False fSetCustomers.Fields("Name").IsIncluded = False ' Define the members of the row elements fldCity = fSetCustomers.Fields("City") fldCity.IncludedMembers = New Object() {strCity1, strCity2} ' Exclude all other field row members in the fieldset fSetProduct = objPTView.FieldSets("Product") objPTView.RowAxis.InsertFieldSet(fSetProduct) fSetProduct.Fields("Product Department").IsIncluded = False fSetProduct.Fields("Product Category").IsIncluded = False fSetProduct.Fields("Product Subcategory").IsIncluded =False fSetProduct.Fields("Brand Name").IsIncluded = False fSetProduct.Fields("Product Name").IsIncluded = False fldProdFamily = fSetProduct.Fields("Product Family") fldProdFamily.IncludedMembers = "Drink" ' Define the measures objPTView.DataAxis.InsertTotal(objPTView.Totals("Store Sales")) objPTView.DataAxis.Totals("Store Sales").NumberFormat = _ "Currency" ' Return the XML data to the client side script m_XML = objPT.XMLData objPT = Nothing Catch err As Exception m_XML = "<err>" & err.Source & " - " & err.Message & "</err>" Finally End Try Return (m_XML) End Function

The LoadCustomPivotTableReport method uses many of the PivotTable properties that are described in Figure 5. Also keep in mind the following tips:

• Use the InsertFieldSet method to insert the appropriate PivotFieldSet into the row or column axis.
• Use the IncludedMembers property to define the distinct PivotField values to include in the table. The IncludedMembers property requires a Variant array, which in the .NET Framework equates to an Object array. Any other array type will generate an error.
• Use the IsIncluded property to enable or disable a particular PivotField in the PivotFieldSet.
• Use the InsertTotal method to insert the appropriate measure into the totals grid.
• The NumberFormat property defines the measure's format. The property accepts either a format code (such as, "0.###") or a predefined format such as "Currency" or "Percent".

Save and Retrieve Custom Reports

Now that we have gone through the effort of creating this custom report, we do not want to repeat this process every time we need to generate a new one. Thankfully, the data required to duplicate this report is contained in the XMLData property. Thus, saving a report is simply a matter of saving the contents of the XMLData property to a persistent storage medium, such as a relational database. Loading a report is simply a matter of retrieving the stored XMLData and rebinding it to the PivotTable. This feature is shown as Pathway 3 in Figure 2.

The application's Web Service includes two methods for saving and retrieving the XMLData of custom reports. In this example, we will actually write the XMLData to a text file called OLAPReport1.xml. The interface provides two buttons: Save Report and Load Saved Report. The Save Report button calls a JavaScript function that then sends the PivotTable's XMLData to the following Web method:

function SaveReport() { // Purpose: Call Web Service method to save the report var strReportXMLData = frm1.PivotTable1.XMLData; var iCallID = service.svcOLAP.callService(onSaveReportResult, 'SaveReport', strReportXMLData, 'OLAPReport1.xml'); }

The Web method in turn writes the XMLData to a file. The Load Saved Report button calls a JavaScript function that does the opposite, as shown in Figure 11. This function calls a Web method that opens the text file, retrieves the XMLData, and then passes it back for binding to the PivotTable control.

Figure 11 Load the XMLData for a Custom Report

function LoadSavedReport() { // Purpose: Call Web Service method to load the saved // report var iCallID = service.svcOLAP.callService(onLoadSavedReportResult, 'LoadSavedReport', 'OLAPReport1.xml'); } function onLoadSavedReportResult(result) { // Purpose: This function handles the // wsOLAP.onLoadSavedReportResult() Web Service result var text = result.value; // result string // Evaluate return result if (!result.error) { // Assign the XML to the PivotList XMLData value frm1.PivotTable1.XMLData = text; } }

The file approach is convenient for demonstration purposes, but a production system should use a relational database for storing the XMLData for custom reports. This approach provides better concurrency management and also allows you to store reports in a more organized way.

Using OWC Events

The OWC 10 components provide several useful events that you can capture in client-side event handlers. These may be written either in VBScript or in JavaScript. In VBScript you use basic event notation, as shown here:

Sub <ObjectName>_<EventName>

In JavaScript you must use for-event syntax:

<script language="javascript" event="EventName" for="ObjectName">

The sample application demonstrates how to capture the Query and CommandExecute events. The event-handling code is shown in Figure 12. When the Query event is fired, the application appends some event logging to the text area field on the left side of the Web page. When the CommandExecute event is fired, we check to see if it is a refresh command. If so, the application appends the date and time to the event logging text area.

Figure 12 JavaScript and VBScript Event Handler

<script language="javascript" event="Query" for="PivotTable1"> { var sLog = document.Form1.Text1.value + ""; document.Form1.Text1.value = "Query Event Fired. " + sLog; } </script> <script language="vbscript"> Sub PivotTable1_CommandExecute(Command, Succeeded) Dim ptConstants Set ptConstants = document.Form1.PivotTable1.Constants ' Check to see if the PivotTable list has been ' refreshed. If Command = ptConstants.plCommandRefresh Then ' Write the current data and time to the text box. document.Form1.Text1.value = vbCrLf & _ "PivotTable Last Refreshed on " & Date & " at " _ & Time & vbCrLf & document.Form1.Text1.value End If End Sub </script>

Binding a Chart to the PivotTable Control

In many cases, a graphical representation of the data is very valuable to the user. Fortunately, OWC provides a ChartSpace control which provides exactly that type of functionality. To improve upon our custom report, we are going to generate a bar chart to represent the data. Although this might seem like a monumental task, the ChartSpace control will help us greatly in this effort. One of its useful features is that it can bind another control, such as the PivotTable control, as its primary data source. It will then display the current PivotTable view in a graphical format. The end effect is that changes in the PivotTable view will be automatically reflected in the ChartSpace control.

In our case, once we have generated the custom PivotTable report, we simply have to assign the PivotTable control to the ChartSpace control's DataSource property:

// Bind the Chart control to the PivotTable control frm1.ChartSpace1.Clear(); frm1.ChartSpace1.DataSource = frm1.PivotTable1; // Set the chart type to 3DBar. frm1.ChartSpace1.Charts(0).Type = 50;

We need to call the Clear method before assigning the DataSource property in order to remove the instance of the previous chart. The ChartSpace control provides many customizable properties, including the Type property, which accepts a ChartChartTypeEnum enumeration value to define the chart layout.

Creating Custom Groupings

Finally, let's look at an important new feature of OWC 10—the ability to create custom groupings. Our report currently groups the store sales measure based on the Time field set. In this case, Time is grouped into Year/Quarter/Month. Suppose we want to group our custom report using a grouping other than the defined fields in the data cube. We can do this by using the custom grouping features in OWC 10. A button labeled Apply Custom Grouping calls a JavaScript function which in turn calls the ApplyCustomGrouping Web method. The input parameter is the XMLData for the current loaded report. The Web method will then load a PivotTable object and use the AddCustomGroupField method to define the custom group, in this case a half-year time grouping. The code is shown in Figure 13.

Figure 13 Creating Custom Groups

<WebMethod()> Public Function ApplyCustomGrouping(ByVal _ strReportXMLData As String) As String Dim m_xml As String Dim objPT As PivotTableClass = New PivotTableClass Dim objPTView As PivotView Dim fsTime As PivotFieldSet Dim fsHalfYear As PivotField Try objPT.XMLData = strReportXMLData objPTView = objPT.ActiveView ' Set a variable to the Time field set. fsTime = objPTView.FieldSets("Time") ' Add a custom group field named "Group1" to the Time field ' set. fsHalfYear = fsTime.AddCustomGroupField("CustomGroup1", _ "CustomGroup1", "Quarter") ' Add a custom field set member. This member includes all ' "Q1" and "Q2" members under 1997. fsHalfYear.AddCustomGroupMember _ (fsTime.Member.ChildMembers("1997").Name, _ New Object() {"Q1", "Q2"}, "1stHalf") ' Add another custom fieldset member to include all "Q3" ' and "Q4" members under 1997. fsHalfYear.AddCustomGroupMember _ (fsTime.Member.ChildMembers("1997").Name, _ New Object() {"Q3", "Q4"}, "2ndHalf") ' Collapse the fieldset at the custom member level fsHalfYear.Expanded = False m_xml = objPT.XMLData objPT = Nothing Catch err As Exception m_xml = "<err>" & err.Source & " - " & err.Message & _ "</err>" Finally End Try Return (m_xml) End Function

Please note that we have added one custom group ("CustomGroup1") with two members ("1stHalf" and "2ndHalf") to encompass the year. The custom members are added using the AddCustomGroupMember method. When you exercise this function on the Web Form, the ChartSpace control also incorporates the custom grouping into the bar chart—yet another benefit of using the integrated components found in Office Web Components 10.

There is one significant issue with the custom group support. The XMLData does not currently save the proper data to persist the names of the custom groupings. If you were to save the XMLData (as we did in the Save Report functionality earlier), and then load the PivotTable with the saved XMLData, the caption for the custom group ("1stHalf", "2ndHalf") would appear as GUIDs. Nonetheless, the integration of the OWC 10 components greatly improves upon the previous version and makes it a very useful tool for Web-based OLAP reporting.

Client Requirements for HTTP Use

Now that we have built our Web-based OLAP reporting application, there are four configuration steps that every Web client must implement in order to start using the application. First, every Web client must have a local licensed copy of Office XP installed on their machine. As an alternative, they must have access to a licensed copy of Office XP on a server to which they have network access. The OWC components come with Office XP and are subject to the same licensing requirements.

Second, every Web client must be using Internet Explorer 5.0 or higher. There are several reasons for this. The Web application uses the Web Service DHTML behavior, which requires Internet Explorer 5.0 or higher. In addition, the client-side OWC components are Microsoft COM components, which do not natively run in non-Microsoft browsers. (We are aware that some browsers provide plug-ins for running client-side COM controls, but we have not tested this ourselves with OWC).

The third configuration step is that every Web client must have the correct OLE DB provider installed: specifically, Microsoft OLE DB Provider for OLAP Services 8.0 or higher. This provider is installed with the redistributable PivotTable Service, which includes a set of DLLs and the required version of Microsoft Data Access Components (MDAC). You can install the PivotTable Service from the SQL Server 2000 CD-ROM under \Msolap\Install\Pts. You will need to choose one of the two installation files provided: PtsLite.exe or PtsFull.exe. The latter installs the PivotTable Service and MDAC, while the former installs the PivotTable Service only. If you are unsure which version you need, simply install PtsFull.exe. You will almost always need the PivotTable Service on each Web client machine because only Microsoft Analysis Services installs the service automatically. You may use the Excel PivotTable wizard to determine if the machine the Web client is running on already has the PivotTable Service installed.

The fourth configuration step is that every Web client must add the application's Web domain to their list of trusted sites in Internet Explorer. The application resides on a different domain than the client machine, so the OWC PivotTable component must cross a domain boundary every time it queries the OLAP data source. For this reason, the application's Web domain must be included as one of the client's trusted sites. To ensure this is reflected, open Internet Explorer and click on the Tools | Options menu. Select the Security Tab, click the Trusted sites icon, and then click the Sites button.

Alternatively, you can provide your clients with a registry file that will automatically update their list of trusted sites. Here's a sample registry key update:

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains\mycompany.com] "https"=dword:00000002

Note that the registry file must be run once for every user on the client machine. This is because it updates the HKEY_CURRENT_USER registry hive. For example, if two domain users share the same machine then they will each be required to run the registry file for their respective accounts.

Conclusion

Web-based OLAP reporting solutions present a fascinating technical challenge because they involve several sophisticated technologies, including SQL Server 2000 Analysis Services, Office Web Components, XML, and ASP.NET. The architecture that we presented here is designed to utilize all of these technologies to the best of their capabilities. Other architectures are certainly possible, especially as current technologies evolve in the future.

For related articles see:
Creating a PivotTable List Control
Understanding the Chart Control
Analysis Services
Office

Jeffrey Hasan is a technical architect and software developer who specializes in developing enterprise applications using Microsoft technologies. He has coauthored numerous books and articles. E-mail Jeff at jeff@asptechnology.net.

Kenneth Tuis a software developer specializing in Microsoft technologies with extensive experience developing both Windows and Web-based enterprise applications. He and Jeffrey coauthored Performance Tuning and Optimizing ASP.NET Applications (APress, 2003). E-mail Ken at ken@asptechnology.net.