Implementing Office Web Component Pivot Tables with ASP.NET
Introduction
A short time after Office 2000 debuted, many developers began to recognize the power of Office Web components. During the time of their initial release, they were known as the Pivot and Chart components. These client-side controls served as a method for extending the Pivot Table and Pivot Chart components of Excel to the Internet environment.
Originally designed for an intranet environment, these Web components allowed users to work with pivot data from various data sources inside a control in Internet Explorer. Users were able to create their own reports using interactive drill up/down capabilities, and to supplement this information with charting. With the introduction of SQL Server 2000’s Analysis Services, which built on SQL Server 7.0’s OLAP Services, Microsoft Excel and the Pivot Chart and Pivot Web controls became the premier tool for exploiting multi-dimensional data within OLAP cubes.
Subsequently, when Office XP was introduced, the Pivot and Chart components underwent significant functionality upgrades, including advances to the user interface as well as reliability. These controls represent a low-cost method of deploying advanced reporting functionality in a quick and efficient manner. However, the methods used to produce these reports (i.e., saving a Pivot Report or Pivot Chart as a Web page using Microsoft Excel, or Microsoft Access) require the user of the report to have direct connectivity to the data source.
Implementing these components in a true Internet environment (external Internet users via a DMZ or Internet-exposed server), or an environment where users do not have direct connectivity to the data source can be accomplished through ASP.NET. Essentially the method involves placing the Web component on a Web form or user control and passing it a specially formatted XML document for its data set in real time.
Technical Architecture
The architecture of the solution includes a simple ASP.NET Web form which possesses a user control. The user control contains a Microsoft Office Pivot or Chart control. Depending on the development environment, several iterations of the control might be available. The versions of these controls will depend on the versions of Microsoft Office installed on the local machine (i.e., Microsoft Office Chart 11.0, 10.0, 9.0). These version numbers correspond with the build numbers of Office.
To use the Pivot Table or Pivot Chart Web component, the control(s) must be added to the toolbox. The controls can be found as a [COM Components] object and be added using the [Add\Remove Items…] menu from the toolbox context menu. Once the control has been added, it can be dragged on to the Web form or user control. This action places an <OBJECT> tag representing the Pivot Table control into the HTML.
<OBJECT style="Z-INDEX: 101; LEFT: 392px; WIDTH: 502px; POSITION:
absolute; TOP: 144px;
HEIGHT: 217px" height="217" width="502" classid="clsid:0002E55A-0000-0000-
C000-000000000046" VIEWASTEXT>
<PARAM NAME="XMLData" VALUE='<xml xmlns:x="urn:schemas-
microsoft-com:office:excel"> <x:PivotTable>
<x:OWCVersion>11.0.0.5531
</x:OWCVersion>
<x:DisplayScreenTips/>
<x:CubeProvider>msolap.2
</x:CubeProvider>
<x:CacheDetails/>
<x:PivotView>
<x:IsNotFiltered/>
</x:PivotView>
</x:PivotTable> </xml>'>
</OBJECT>
One interesting element about the Pivot Table and Chart object is the ability to change the version of the object depending on the CLASSID attribute. Changing the CLASSID to correspond to the version of the pivot control desired is an easy way of changing compatibility to accommodate clients.
For use in a corporate environment with security requirements, options for deployment include deploying the ASP.NET application to an IIS site that uses HTTPs encryption as well as integrated security. Users will have to navigate to the site under SSL and provide account information before being granted access to the pivot application. Furthermore, the application can include functionality to segment reports via the Pivot controls by user credentials supplied to IIS by users.
Additionally, for a truly disconnected data solution (i.e., clients do not have direct access to the data source), a control that supplements the Pivot or Chart control must be present. The presence of the Data Source control will serve as the facilitator that binds together the ADODB XML and the Pivot or Chart control.
The following example illustrates the HTML code that represents the data source. This code can be placed in the .aspx or .ascx HTML without using the Visual Studio IDE. This component is required for the aforementioned solution.
<OBJECT id=dsc classid=CLSID:0002E530-0000-0000-C000-
000000000046 VIEWASTEXT> <PARAM NAME="XMLData" VALUE='
<xml xmlns:a="urn:schemas-
microsoft-com:office:access">
<a:DataSourceControl>
<a:OWCVersion>9.0.0.3821
</a:OWCVersion>
<a:MaxRecords>0</a:MaxRecords>
<a:GridX>24</a:GridX>
<a:GridY>24</a:GridY>
<a:Datamodel version="0816">
</a:Datamodel>
</a:DataSourceControl> </xml>'></OBJECT>
Development Methodology
Providing Data Access with ADO.NET and ADODB
A key component in rendering the data is to use the ADODB object library. This is the fastest method of producing the formatted XML that the Web components expect. In a typical scenario, developers may create a reporting portal, in which all of the data driven menus and HTML is rendered from SQL Server using ADO.NET, while a separate class responsible for providing Pivot Table or Chart data will implement ADODB by adding a project reference to the library through the IDE. The ADODB.StreamXML method will take an ADODB recordset and stream it to a file as formatted XML.
This file can reside in a virtual directory on the server which can be accessed by the Web form or user control rendering the Office Web Component to the user. Again, access to this data will be accomplished through client side scripting.
Binding to the Data
Once the Pivot or Chart control has been added to the ASP.NET development environment, the remaining tasks are to provide the control with data. The control depends on receiving a formatted XML result set which is conducive to multiple axes of the Office Web Component. Since the Office Web Component runs client side, the data must be rendered from the server (populated by ASP.NET via code behind techniques) and sent to the client’s browser.
This arrangement is necessary to deliver this solution to a truly disconnected set of users (i.e., DMZ or Internet users), who do not have direct access to the data source. To facilitate this, the Web form or user control which houses the Office Web Component must contain client side scripting (VB Script or Javascript) which understands the presence of the XML data and then binds it to the Pivot control. Furthermore, the same client script should also bind the measures found in the XML data to the Pivot or Chart object so the user does not see a blank Pivot report or Chart that the user has to build manually.
The following sample written in VB Script indicates the methodology to bind XML Data from the ADODB stream to the client side Pivot table control.
<SCRIPT language=vbscript>
Sub window_onload
PSpace.DisplayFieldList = true
Dim dc
Set dc = dsc.Constants
dsc.RecordsetDefs.AddNew "SupportFiles/PivotData.xml",
dc.dscCommandFile, "dscData"
PSpace.DataSource = dsc
PSpace.DataMember = "dscData"
dsc.ConnectionString = "Provider=MSPersist.1"
PSpace.CommandText = "PivotData.xml"
End Sub
</SCRIPT>
This example illustrates that when the Web form is loaded and the client side VB Script window_onload sub fires, the data is retrieved from the Web server using the RedorsetDefs.AddNew method of the data source control, and is bound to the Pivot Table.
In the above example, the Pivot Table control (Pspace), is bound to the Data Source control (dsc). The first step of the script is to obtain a reference to the Data Source control. Subsequently, the control is bound to the XML file generated by the application (i.e., a code - behind class which connects to the database using ADODB and creates a file in real-time). This newly produced XML file is housed within the application’s virtual directory and is accessible to the page. The file is read into the Data Source control. The script then binds this control to the Pivot Table’s .DataSource property.
The script also ensures that the control’s field list is displayed so users can easily see the list of fields available in the data set once the Pivot Table control is bound.
Deployment
Control Versions
One important note about the deployment architecture is that clients visiting the page with the Pivot object may not have the control installed. Office XP’s Web components and Office 2003 does a nice job of providing native information for the user to download and install the component. If the Office 2000 version of the Pivot control (version 9.0 of the chart or pivot object), must be used due to client requirements, it is important to arrange for the pre-configuration of the clients before visiting the page. This may involve ensuring the client base already has an install of the Office 2000 Web Pivot and Chart components (commonly installed with Office 2000).
Security and Trusted Level
Additionally, when users browse the page, one of the features common to all Web components is the ability to export information from the control into Excel. This is accomplished through a toolbar button within the control. This button launches Excel on the clients’ machine and transfers the data in the Pivot or Chart control to Excel. Users may encounter some security notifications courtesy of Internet Explorer which relate to accessing data across domains. If the user selects to allow this, the export to Excel functionality of the control will function normally. In order to avoid this warning, users should ensure that the site with the control(s) is registered with their browser’s list of trusted sites. Once the site is trusted, most security messages with regard to the control will be suppressed.
Office Licensing Considerations
It is important to note that while Office 2000 users can download and install Office XP and Office 2003 Web components and view reports, much of the advanced functionality of the control is disabled. This includes the ability to export the control data to Excel. Microsoft requires that users of the controls have an actual license for Office XP or Office 2003 to enable the control’s full functionality. If Office 2000 users visit an Office XP or Office 2003 Web component, the control disables much of the advanced functionality and only allows the users a base-level of control interactivity (i.e., the ability interact with the control and its data). Most of the context menus and toolbar buttons will be disabled unless the control determines that the user has the corresponding version of Office.
--
Eric Charran is currently working as a Technical Architect and Web Developer, in Horsham PA. Eric holds Microsoft Certifications in SQL Server and has significant experience in planning, designing, and modeling n-tier applications using SQL Server 2000, as well as architecting and implementing .NET framework based applications, solutions and Web services. Eric's other professional skills encompass Database Administration, Data Warehousing and Application Architecture, Modeling and Design, as well as Data Warehouse Transformation and Population using DTS. Eric is also skilled in developing and implementing ASP.NET and Windows forms applications, using technologies such as COM+, the .NET framework, VB.NET and other .NET languages.
Contributors : Eric Charran
Last modified 2005-04-12 06:21 AM
DataSource for Pivot Table using C#
Please help!!
Lizelle
Replies to this comment
XML structure
Could you show me an example of how the XML page looks like that you link? And how do you specify what you want to display in the page, or does that not matter with the pivot table?
Thank you
Lizelle
connecting xml to pivottable
This is exactly what I want to do. I have created the XML on the server but cannot get the pivot table to look at it. Could you possibly send me the source code for the whole page.
Regards
James
Help with the Pivot Table
Thank you in advice.
Francisco
I need some additionals details about create a XML file from Cube data.
I don't see any examples that use ADODB.StreamXML method in the 2.7 version. What version of the library you use?
In adition, I have a doubt; This method write all the existing data in the cube into a XML file?
I appreciate all the info that you can send my.
Thanks a lot,
Gabriel
Replies to this comment