Creating Reports in ASP.netwith Office Web Components

Inside Microsoft: Reporting Using Pivot Tables 2: Creating Reports in ASP.NET with Office Web Components

Introduction

The previous article of the series introduced PivotTables and showed how the Office Web Components (OWC) PivotTable control can be used in the context of static HTML.

In this article I’ll go on to show you how to use PivotTables in a more dynamic, ASP.NET 1.x-based, environment. I’ll create and explain different types of the PivotTable reports in ASP.NET using C# on server side and JavaScript in the browser. I’ll show the code that creates the PivotTable and binds to the data source on server and client sides and describe how to create the wrapper page for the XML format, if you want to use XML. Finally I’ll touch on security and licensing considerations and explain pros and cons of each solution shown in the article. Advanced topics, like OLAP, are not touched in this article, but I provide the links to read further about these kinds of related topics.

Since Excel uses the PivotTable OWC control when saving sheets with PivotTables, the saved HTML and XML files help to understand the format and usage of XML data files behind the PivotTable control.

System Requirements

The following software components were used when writing and testing the sample code for the article:

* Windows XP + SP1

* Internet Information Service 5.1 or later

* .NET Framework 1.1

* Visual Studio .NET 2003

* MS Excel 2003 and Office Web Components 11 (as part of the MS Office 2003 System)

* Northwind database on a MS SQL Server 2000 (MSDE edition)

* Internet Explorer 6.0 + SP2

Since we demand only the core services of these software components, the concepts reviewed in the article can be applied using other software components too. For example, Windows 2003 can be used instead of XP, or OWC 10.

The minimal configuration required:

* IIS web server running on Windows 2000 or later

* NET Framework 1.x

* Visual Studio .NET 2002 or later

* MS Excel 2000 and Office Web Components 9 (MS Office 2000) or later

* MSDE or SQL Server with the Northwind database installed.

* Internet Explorer 5.0 or later (any browser for server-side solutions, but IE is required for client-side solutions, as it uses an ActiveX control)

Remarks on configuration:

* If you are using IE6 after installing WinXP SP2, the ActiveX control may be blocked. Unblocking can be done by clicking the IE Blocking bar and select Allow blocked content.

* If your database and web browser are on different domains and you would like to access the database directly you should enable Access data sources across domains in the Security Settings in IE.

The Sample Code

The accompanying download contains the following files:

* NWSelect.sql: This is an SQL script to create a new view called SalesData which you will need to install in the Northwind database. Use MS SQL Query Analyzer to run this script. The view contains the sales information that may be important for a business user. You’ll use this view in your PivotTable reports to display information for users throughout the article.

* SalesData.bas: This is a VBA macro to automate the creation of a sample PivotTable in Excel. You can run this code from Excel as it is shown in the Creating PivotTables in Excel section

* NWReport folder: This contains the sample Visual Studio .NET solution. To install the sample, create an IIS virtual directory called NWReport, which points to the folder to which you have installed the solution. Set the value of the dataSource key in the web.config to match to your SQL server name.

Using the PivotTable Component in Visual Studio .NET

The PivotTable component is basically an ActiveX control. Before you use it in Visual Studio .NET as a visual component in your ASP.NET pages, you should add it to the Toolbox as shown in Figure 1.


Figure 1. The PivotTable control in the list of COM Components

The PivotTable components should now appear in the toolbox as shown in Figure 2.


Figure 2. Adding the PivotTable control to the Toolbox

Similarly, if you are intending to use the PivotTable component from your ASP.NET code, you should add a reference to the Microsoft Office Web Components as shown on Figure 3.


Figure 3. Enabling the Office Web Components reference in your project

Additionally you will probably want to include the using OWC11; (or using OWC10;, or using OWC9; if you have Office XP or Office 2000 installed) directive in the header of your code to include the OWC namespace.

Client-Side Usage with a Direct Database Connection

If you place a PivotTable component on your ASP.NET page in design mode you get an empty component in design and runtime mode – these are respectively shown in design.gif and empty.gif.


Figure 4. The PivotTable component without a data connection (design mode)


Figure 5. The PivotTable component without a data connection (runtime)

You’ll want to customize these before use. If you look at the HTML source of the page, you see the PivotTable object with its single XMLData parameter. Notice that this parameter, as its name suggests, really is an XML data parameter.

Despite the Click here to connect to data caption on the component at design time that you saw earlier in Figure 4, you should connect to data and make other customizations in ASP.NET by right-clicking the component and selecting the Properties menu item. If this does not work for you, try to select the component in the designer and press ALT + ENTER to have the PivotTable Property Pages displayed. Configure the data source to point to the SalesData view of Northwind database in your SQL Server. Notice that once the data connection is configured, new property pages are added to the PivotTable Property Pages dialog box – this is shown in ptprops1.gif and ptprops4.gif.


Figure 6. The PivotTable Property Pages dialog box before the data connection is set

Set the caption of the PivotTable to "Northwind sales report" as shown on ptprops4.gif.


Figure 7. The PivotTable Property Pages dialog box after the data connection is set

After you set the data connection, the design and runtime view of the PivotTable changes: the pivot areas appear and some of the icons on the toolbar are enabled in the runtime view (see Figure 8 and Figure 9), including the Field List button (second from the right on the toolbar), thus enabling your users to drag and drop fields into the areas.


Figure 8. The PivotTable component with a data connection configured (design mode)


Figure 9. The PivotTable component with a data connection configured (runtime mode)

If you look at the HTML source of the page again, you see that the XMLData parameter has grown quite large (in my case it is almost 4000 characters). It now describes all configuration parameters you set on the PivotTable Property Pages dialog box page, including the ConnectionString, so if you use the NWReport1.aspx as it is in the sample code, don’t forget to set this value to reflect your SQL Server name. The following code list shows a manually formatted part of the value in the XMLData parameter.





11.0.0.5531

msolap.2


Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Persist Security Info=True;
Initial Catalog=Northwind;
Data Source=localhost;
Use Procedure for Prepare=1;
Auto Translate=True;
Packet Size=4096;
Workstation ID=machinename;
Use Encryption for Data=False;
Tag with column collation when possible=False



"Northwind"
."dbo"."SalesData"

Northwind sales report




CompanyName
adVarWChar
UniqueName




0
0
0
0
2







Northwind sales report






’>


Although your users at least can start to work with this version, they would be probably prefer to have a PivotTable that is pre-populated with data instead of this empty one. Later in this article I’ll show you how to do this.

Using the XMLData parameter

The previous discussion might have given you the idea that you should simply copy the XMLData parameter from a web-published sheet, so you as the developer don’t need to code the PivotTable fields. Since most developers tend not to be very good at creating Excel PivotTables, those sheets could be provided to them, so the developers just make a live connection to the database. This solution is basically equivalent to a similar solution where one user creates the PivotTable using Excel then publishes it as HTML, and then somebody from the IT publishes it on the corporate intranet.

Since the XMLData parameter may contain the pivot structure and the data in a mixed way, applying this solution means that data is displayed to your users as it was at the time of the publishing Excel pivot as HTML. This issue can be handled manually (users can refresh the pivot by clicking an exclamation icon on the toolbar) or automatically (by calling the the Refresh() method of the PivotTable object from the JavaScript after loading the page).

You’ll see a similar solution where the XMLData parameter is computed server-side on the fly later in the section, Using the XMLData to Create Interactive Solutions.

Scripting the PivotTable Component, Client-Side

In this section you’ll create a solution that is ideal in a typical intranet scenario, when your users can attach to the database directly and you would like to enable them to choose the dimensions of the PivotTable themselves. Implementing this solution you’ll use the PivotTable component with JavaScript. You’ll create a new ASPX page in Visual Studio .NET, put a PivotTable control on it and set its id property to PivotTable in design view. Then you’ll change to HTML view and write some JavaScript code in the window.onload() method.

The Code

The complete code for this is available as the NWReport2.aspx file in the sample code. The web page that results from doing this is shown in Figure 10.


Figure 10. The interactive PivotTable in HTML

The JavaScript code to achieve this is quite long so I’ve presented it in one go below, then I’ll go through the significant lines individually. Note that in the code below, as usual you’ll need to replace the connection string with one that is appropriate to your machine. Note that some long strings have been formatted with extra line breaks for display purposes.


<script>

function window.onload()

{
// get reference for the PivotTable object and the active view
var table = document.all.PivotTable;
var view = table.ActiveView;
var constants = table.Constants;
table.AutoFit = true;
// set up the connection
table.ConnectionString = "Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Initial Catalog=Northwind;
Data Source=localhost";
table.CommandText = "SELECT * FROM SalesData";
view.TitleBar.Caption = "Northwind sales report";
// add the Country field to the row area
var country = view.FieldSets("Country");
country.Fields(0).Caption = "Country";
view.RowAxis.InsertFieldSet(country);
// add the OrderDateMonth field to the column area
var orderDateMonth = view.FieldSets("OrderDateMonth");
orderDateMonth.Fields(0).Caption = "Order Date";
view.ColumnAxis.InsertFieldSet(orderDateMonth);
// create calculated field called Sales
var sales = view.AddFieldSet("Sales");
var field = sales.AddCalculatedField("Sales", "Sales", "Sales",
"[UnitPrice]*[Quantity]*(1-[Discount])");
sales.Fields(0).Caption = "Sales";
// create total for the Quantity
var totalQuantity = view.AddTotal("SumQuantity",
view.FieldSets("Quantity").Fields(0), constants.plFunctionSum);
totalQuantity.Caption = "Quantity";
view.DataAxis.InsertTotal(totalQuantity);
// create total for the Sales
var totalSales = view.AddTotal(
"SumSales", field, constants.plFunctionSum);
totalSales.Caption = "Sales";
view.Totals("SumSales").NumberFormat = "$#,##0";
view.DataAxis.InsertTotal(totalSales);
// no details available, so we hide it
table.ActiveData.HideDetails();

}

</script>


Working through this function, first you must get the reference to the PivotTable object that just created, and its ActiveView property that can be used to manipulate the fields. Also you need to declare a variable for the Constants property that stores some pivot-related constants you’ll use later.

Using these objects you’re now ready to set the data connection and to specify which data fields will be displayed in which area of the PivotTable.


var table = document.all.PivotTable;

var view = table.ActiveView;

var constants = table.Constants;


Next you should set up the connection to SQL Server. In this case you get the data from the SalesData view, alternatively a stored procedure could be executed to return a record set.


table.ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Initial Catalog=Northwind;Data Source=YourSqlServerName";

table.CommandText = "SELECT * FROM SalesData";


You should add data fields into pivot areas one by one. You can add fields to the pivot areas, for example, the Country field is added to the row area this way:


var country = view.FieldSets("Country");

country.Fields(0).Caption = "Country";

view.RowAxis.InsertFieldSet(country);


The fields of the record set are populated to the FieldSets collection of the view. In this example the OrderDateMonth field is added similarly to the column area.

You can display not only the raw data fields. It is possible to make calculations on the fields, and display the calculated fields. To illustrate how to create calculated fields we create one called Sales, that contains the value of the sales based on the unit price, quantity and discount. You need to create a new FieldSet using the AddFieldSet method to do this.


var sales = view.AddFieldSet("Sales");

var field = sales.AddCalculatedField("Sales", "Sales", "Sales",
"[UnitPrice]*[Quantity]*(1-[Discount])");
sales.Fields(0).Caption = "Sales";


You should specify how data in the data area is aggregated. The aggregation is available for you through using totals. When creating totals you can choose the type of aggregation (for example sum, count, etc.) for the selected field. We create totals using the AddTotal method and add them to the data area. In this example the SumSales total contains the sum of the sales. We set the NumberFormat property to currency.


var totalSales = view.AddTotal("SumSales", field, constants.plFunctionSum);

totalSales.Caption = "Sales";

view.Totals("SumSales").NumberFormat = "$#,##0";

view.DataAxis.InsertTotal(totalSales);


Finally, the code creates a SumQuantity total similarly to show information on the number of products sold.

Visual Customization of the PivotTable

You can set visual effects, like color or font of the different pivot fields and labels. You can set these visual properties on both client and server side. The following code illustrates this by setting some visual properties of the pivot title bar. You can customize pivot areas and other titles similarly.


view.TitleBar.Visible = true;

view.TitleBar.Caption = "Northwind sales report";

view.TitleBar.Font.Underline = constants.owcUnderlineStyleSingle;

view.TitleBar.Font.Bold = true;

view.TitleBar.Font.Name = "Arial";

view.TitleBar.Font.Size = 16;

view.TitleBar.Font.Color = "#006600";

view.TitleBar.BackColor = "#0000AA";


Server Side Usage

Sometimes it is better to build the PivotTable on the server side. This solution is usually preferred if you want to show the PivotTable to your users as a pure HTML table (for example, because they use low-end browsers) or you want to implement some kind of data caching or transformation for your users. This also means that clients don’t need access to the original data source.

General Consideration when Migrating from Client to Server Side

Migrating from client-side JavaScript code to server-side C# code is really straightforward. There are only some basic rules to keep in mind:

* Objects must be declared and created using their types (like PivotTableClass, PivotView, PivotFieldSet, etc.) instead of using the var declaration.

* Constants are defined in enumerations (like PivotTotalFunctionEnum, UnderlineStyleEnum) instead of the Constants property of the PivotTable object.

* Use [] (brackets) instead of (parentheses) when working with arrays.

* The InsertFieldSet() method expects two more parameters, the reference for a PivotFieldSet object before you insert the new field set and a Boolean value indicating if the field set should be removed from any other axis (we will usenull and false for equivalent behavior). These parameters are optional in JavaScript, but required in .NET.

* The InsertTotal() method expects one more parameter, the reference for a PivotTotal object before you insert the new total. (You can use null for equivalent behavior). This parameter is optional in JavaScript, but required in .NET.

Using the XMLData to Create Interactive Solutions

In this section I will show you how to create an interactive PivotTable solution on the server side. It can be used, for example, if the building up of the PivotTable requires server-side decisions (for example, another system determines if a given field should be added to the pivot), or you would like to hide the script that builds the pivot from your users.

The PivotTableClass has an XMLData property that is equivalent to the XMLData parameter you saw previously when I configured the PivotTable from the designer (NWReport1.aspx). If you create and build your PivotTable on the server side, then pass the value of this property to the XMLData parameter of the PivotTable ActiveX object, the PivotTable behaves much the same as when you build it on the client side.

In this case you create a new ASPX page, put a PivotTable control on it, and change the XMLData value in the HTML source like this:

’>


Then you should migrate the client-side JavaScript code to C# as described in the previous section and put this code into the GetXmlData() method of the code-behind file of your page. Finally, you should pass back the value of the XMLData property to the caller.


return(table.XMLData);


Important to note that Visual Studio .NET replaces the value of the XMLData parameter you set above with the default value when you change the designer from HTML view to design view, so if you see an empty PivotTable when viewing this page in your browser, probably that is the reason.

For the sample code, I created the page NWReport3.aspx to demonstrate this technique for you. The output of the page is identical to the output of NWReport2.aspx shown on Figure 10.

Create Static HTML Solutions

If the end-user’s browser does not support ActiveX, or the machine does not have OWC installed or the bandwidth is limited, the PivotTable can be displayed as pure HTML table. Of course this solution has a serious disadvantage: the HTML table is not interactive. In this section I’ll quickly work through this approach.

Using the HTMLData Property

In this example you’ll create the static HTML output from the pivot data instead of interactive reports seen previously. To achieve this result you should create a new ASPX page and drop a PivotTable control on it. Then you can put the code you used in the GetXmlData() method in the previous section in the Page_Load() method, but instead of passing back the XMLData property we write the HTMLData property of the PivotTableClass to the response stream. The HTMLData property contains the HTML formatted PivotTable.


Response.Write(table.HTMLData);


I created NWReport4.aspx to demonstrate this technique for you. The output of the page is shown on Figure 11.


Figure 11. The static PivotTable in HTML

Error handling

Another useful feature of the HTMLData property is that it may contain additional information if some exception is thrown from the component. Typically you’ll see the rather unhelpful System.Runtime.InteropServices.COMException: Unspecified error exception while building your application. Fortunately the message in this exception often contains more details, which you can retrieve using the following try/catch block:


try

{
// PivotTable manipulating code comes here

}

catch(System.Runtime.InteropServices.COMException ex)

{
Response.Clear();
Response.Write(ex.Message + ex.StackTrace);
Response.Write(table.HTMLData);
Response.End();

}


As an example, the result of an incorrectly specified SQL Server name is shown in Figure 12:


Figure 12. Using the HTMLData property to obtain additional information about an error

Using a Wrapper Component as the Data Source

In this section you’ll create a wrapper component on the server side that can be specified as the data source on the client side without having the client directly connect to the data source. This way you can provide the PivotTable functionality for users who have no direct database access either because of lack of user rights on database or because of the network topology (for example, users on the corporate extranet should access the database only from the internal side of the firewall). The wrapper component may come in handy if you would like to create data caching or data manipulating functionality in the middle tier (e.g., the application or the web server).

To achieve this result, you should refer back to the HTML page that was published from the Excel PivotTable in the previous article. Recall a cachedata XML file is also created during the export. This XML file is refreshed each time you save the Excel sheet if you checked the AutoRepublish every time this workbook is saved option when saving the Excel file in HTML format. If you did not, you should save it again to have the refreshed data in the XML. If you check the contents of this file you see that Excel stores the data for the PivotTable in this file in the following structure:




xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"

xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"

xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">


1





…











…


z:row Col1="Vins et alcools Chevalier" Col2="14" Col3="12"/>

…







The schema of the data is defined in the schema element, which contains an attribute element for each column in a row (e.g., a field in the record). The AttributeType element describes the display name and its datatype element the data type for the columns.

The data for the PivotTable is contained within the data element, in which a row element represents a data row. The row element has attributes with names defined in the attribute elements in the schema, these attributes contain the data for a column.

It’s important to note that the PivotTable component is very sensitive to the format of the XML in this file. It means that although the should be equivalent to the format, the PivotTable accepts only the latter format.

In this sample you’ll create a SalesData.aspx page that outputs the data in the XML format I showed above. The file NWReports5.aspx in the sample code demonstrates this technique – it is identical to NWReports2.aspx except that it consumes data from the wrapper page instead of the direct database connection. To achieve this, only the data connection setup part of the JavaScript need be modified. As you can see, in this case you’ll use the Microsoft OLEDB Persistence Provider (mspersist) in the NWReports5.aspx to bind to an XML stream that contains the persisted record set:


table.ConnectionString = "provider=mspersist";

table.CommandText = "salesdata.aspx";


The harder part is the wrapper page (SalesData.aspx). First, you create a GetSalesData() method that queries the database and returns the DataSet to the caller:


public DataSet GetSalesData()

{
{
SqlConnection connection =
new SqlConnection("Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=Northwind;
Data Source=" + ConfigurationSettings.AppSettings["dataSource"]);
connection.Open();
SqlCommand getSalesData =
new SqlCommand("SELECT * FROM SalesData", connection);
getSalesData.CommandType = CommandType.Text;
SqlDataAdapter dataAdapter = new SqlDataAdapter(getSalesData);
DataSet salesData = new DataSet();
dataAdapter.Fill(salesData);
connection.Close();
connection.Dispose();
return salesData;
}

}


Next, you should call GetSalesData() from the Page_Load() method of the SalesData.aspx page. Then, after an XSL transformation you write back its content to the Response stream in the required format. The XSL transformation in question transfers the data from the XML format as it was generated from the DataSet into the XML format expected by the PivotTable component – just as you’ve seen in the case of the cachedata XML file. Note that since during the XSL transformation you get data rows in format instead of the format, you’ll need a temporary MemoryStream to read the XML content to a string and replace the wrong format to the right one. Although this isn’t an elegant solution, it must be done to have the PivotTable read the XML.

Notice that calling SalesData.aspx from your browser shows you the transformed XML data, but if you give query string parameters when calling the page (e.g., SalesData.aspx?qqq), the row data XML is passed back. This can help to develop and debug the XSL transformation. The SalesData.xslt used for the transformation is created experimentally, knowing the XML format of the DataSet and the persisted Excel data.


private void Page_Load(object sender, System.EventArgs e)

{
// Get the SalesData and transform it using the XSLT
DataSet salesData = GetSalesData();
XmlDataDocument xmlDataDoc = new XmlDataDocument(salesData);
XslTransform xslTran = new XslTransform();
xslTran.Load(MapPath("SalesData.xslt"));
// Create a memory stream for temporal storage of the result set XML
MemoryStream stream = new MemoryStream();
XmlTextWriter writer =
new XmlTextWriter(stream, System.Text.Encoding.UTF8);
writer.Formatting = Formatting.Indented;
writer.Indentation = 2;
writer.IndentChar = ‘ ‘;
// If the query string is not empty, then we write the data set XML itself
// instead of the transformed XML to the stream
// to help build and debug the XSLT file for transformation
if (Request.QueryString.Count!=0)
{
xmlDataDoc.WriteTo(writer);
}
else
{
xslTran.Transform(xmlDataDoc, null, writer, null);
}
// Flush the writer and reset stream position to the beginning
writer.Flush();
writer.BaseStream.Position = 0;
// Then read to the end of the stream
StreamReader reader = new StreamReader(stream);
string formattedOutput = reader.ReadToEnd();
// Replace row tags to equivalents that are
// acceptable by the PivotTable component
formattedOutput = formattedOutput.Replace(">rn ", "/>");
// Write the result to the Response stream
Response.ContentType = "text/xml";
XmlTextWriter xmlWriter =
new XmlTextWriter(Response.OutputStream, System.Text.Encoding.UTF8);
xmlWriter.WriteRaw(formattedOutput);
xmlWriter.Close();
Response.End();

}


The output of the NWReport5.aspx is identical to the output of NWReport2.aspx shown earlier in Figure 10.

Choosing your Solution

So far in this article I’ve presented a number of different approaches to providing PivotTables in ASP.NET 1.x pages. In this section I’ll look in a bit more detail at some of the issues surrounding which approach is likely to be best in different situations.

When to Calculate Fields

In the samples in this article you calculated fields from JavaScript and C#. If you prefer, fields can also be calculated in SQL; a simple example is the OrderDateMonth field you calculated from the OrderDate field in the SalesData view:


CHAR(7">RIGHT, os.OrderDate, 102), 5) OrderDateMonth


Alternatively you can calculate fields during the XSL transformation, if you use the wrapper page approach.

The method you choose should depend on whether you want to place the load on SQL Server (this happens if you put field calculations into the SQL stored procedure or database view), or on the web server (as when you use XSLT or server side field computing in C# to calculate fields) or on the client (choose JavaScript to compute fields). Whether you provide the fields you used as the base of the calculation to the PivotTable, or only the calculated fields, will most likely depend primarily on whether the user may need those fields and secondarily on the available bandwidth (although this should not be a problem in the case of an intranet). Remember that recovering the original fields from the PivotTable data is almost always harder than obtaining the PivotTable from those fields, and it may not be possible at all. Think of the aggregation of first name and last name, or using only a part of the information, like in the case of the OrderDateMonth field, where the exact date information is lost during the calculation.

Problems with Interactive OWC

In some cases you may find that the interactive features in OWC are not available for you. In other cases you may get strange warning messages. The reasons for these are given below.

* The full interactive functionality of OWC is only available for the users who have Office installed. Without Office the OWC provides view-only functionality.

* The other thing that may prevent the normal usage of OWC is related to the ActiveX settings in your browser. Since OWC is rendered on the client when you use its interactive mode, the Web browser used to display reports must support ActiveX and ActiveX controls must be enabled. Finally, OWC must be on the client or the client must be able to download OWC.

* Alerts like "This page accesses data on another domain. Do you want to allow this?" are related to the Internet Explorer Security settings and can be handled by assigning the web server to the Trusted Sites zone.

You can find links to more information about these topics in the related links for this article.

Comparing the Solutions

Each sample I provided has advantages and disadvantages. In this section I’ll try to give you an overview of pros and cons to help to choose the method that is the most adequate to your requirements (from the point of network topology, security and performance).

First I should mention that Microsoft recommends using OWC only on the client side with interactive mode. Although using the components on the server side is also possible, try to avoid this setup when scalability of the solution is critical for your business. Further information about this topic can be found in the related links.

The use of the HTMLData to produce static HTML reports is clearly recommended if you don’t want your users to change the content of the report or to get more information by adding other fields to the PivotTable. It’s probably not the best method for advanced users, as it means they can’t change the dimensions of the PivotTable but it may be useful for users unfamiliar with manipulating PivotTables, as it has the simplest user interface (e.g., a simple HTML table). Another advantage is that users do not have access to the data source, since all data access is made by the web server components. You can always use this solution if users do not have an ActiveX capable browser, OWC installed, or a licensed version of Office to support the interactive pivots. Also, this solution will produce the least network traffic as it only transfers the data necessary for displaying the required view in HTML format, and not the whole XML data document. This solution is shown in Figure 13.


Figure 13. Static HTML scenario: Using the HTMLData property

When you have to choose between interactive PivotTables built with JavaScript (Figure 14) or with C# and the XMLData property (Figure 15).


Figure 14. Interactive client-side scenario: JavaScript


Figure 15. Interactive server-side scenario: C#

The decision between the two interactive scenarios should be made in the first instance from considering whether the business logic you use to build the PivotTable is available on the client or whether additional information from the server is required. For example if some other business system or business rules determines whether some field should be added to the PivotTable, that condition is easier to check on the server side. Remember that the method you choose does not affect the fact that the PivotTable from the browser connects using the credentials of the interactive user (although the web server also connects to SQL Server when building the PivotTable on the server side). The C# code is used only to build the XMLData parameter for the PivotTable component, and that parameter tells the browser where to get the data from.

If you would like to hide the PivotTable building logic from your users, you should probably choose the XMLData parameter and thus avoid building the PivotTable on the client side. If your users can’t connect directly from their browser to the database, you should use the XMLData parameter, as in this case the whole XML data document is included in the HTML page, while when using JavaScript, the browser should be able to access the database. Using a wrapper page provides a workaround for this latter limitation.

If you have to create interactive reports but the users don’t have direct access to the database (for security or network reason) or there is no database at all, but you have data available as DataSet from an alternative data source (for example, you read the content of a CSV file into a DataSet), then you have an ideal scenario for using a wrapper page, as in Figure 13. Otherwise, this method is not recommended because of the additional overhead placed on the web server.

Further Work

If you want the the users see to have advanced functionality and the PivotTable to be displayed with corresponding graphs I suggest using another ActiveX object, the Chart component from the OWC suite, in conjunction with the PivotTable. Since most people are visually orientated, displaying graphs and charts for them between the PivotTables would probably assist the effectiveness of their work, as well as their perception of your applications.

Using PivotTable with OLAP systems, like SQL Server 2000 Analysis Services, is an advanced topic, but here – when hierarchical data comes into the scope – is where PivotTables can become exceptionally useful. If you plan to use the PivotTable component (or OWC in general) to build OLAP reporting and business intelligence solutions you can find related articles to help your start in the related links for this article.

Conclusion

In this article I’ve shown you how the PivotTable component of OWC is a powerful tool for data publishing and report generation. You learned how to create both client-side and server-side PivotTables using ASP.NET 1.x. I also demonstrated the use of an XML data wrapper page; this may help to create interactive PivotTables when there is no direct connection between the client and the data source. Finally I presented a short comparison of the different approaches demonstrated in the article, to help you choose the optimal solution for your environment.

Using the techniques demonstrated here you can start to build interactive reports for busines