SQL Server 2005 : Using Excel (part 2) - Using PivotTables and Charts in Applications and Web Pages

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/20/2012 11:49:51 AM

Using PivotTables and Charts in Applications and Web Pages

Although the temptation to use Excel as your OLAP front end is compelling, such a client environment is a far cry from a true custom-developed application. Many developers will greatly prefer to host PivotTables within applications rather than ceding control of OLAP functionality to Excel. The good news for them is that PivotTables are available as programmable controls from Office Web Components (OWC), which ships with Office. PivotTables can therefore be used from .NET Windows Forms applications and from any HTML page (including pages in an ASP.NET or Active Server Pages application). In fact, the cube browser is an example of one such application.


For applications and Web pages that use OWC controls to execute correctly, the user must have Office or OWC installed.

One advantage of using the OWC PivotTable control in your own application is that you can provide your own front end. Rather than make the user select a server, database, cube/perspective, or even dimensions and measures, your application can configure some or all of these settings programmatically. You can programmatically show, hide, or format various parts of the PivotTable as well, giving users exactly what they need without overwhelming them with connection and metadata options.

Using PivotTables in Windows Forms Applications

To use the PivotTable control from a Windows Forms application, start by opening an existing Windows Forms project in Microsoft Visual Studio 2005 or creating a new one. Right-click the Visual Studio Toolbox window, and select Choose Items... from the shortcut menu (Figure 13).

Figure 13. Invoking the Visual Studio 2005 Toolbox’s Choose Items option

In the Choose Toolbox Items dialog box, on the COM Components tab, check the box next to the Microsoft Office PivotTable 11.0 object and click OK (Figure 14).

Figure 14. The COM Components tab of the Choose Toolbox Items dialog box, with the Microsoft Office PivotTable 11.0 component selected

More Info

You might find earlier versions of the PivotTable component on your system. Version 10 was supplied with Office XP/Excel 2002, and version 9 was supplied with Office 2000/Excel 2000. You might want to experiment with these versions of the component if you intend to deploy your application to machines running those earlier versions of Office and Excel.

After you add the PivotTable component to your toolbox, you can drag and drop an instance of it onto a form. Try to position it at the upper-left corner of the form’s client area; if you have trouble doing this, set the control’s Location.X and Location.Y properties to 0 in Visual Studio’s Properties window. After you add and position the control, resize your form to accommodate the control’s full width and height.

The control should now appear with a mostly disabled toolbar and its client area displaying the Microsoft Office Web Components logo in gray over a white background, with a hyperlink at the bottom center of the control labeled “Click here to connect to data.” Click the Commands And Options toolbar button (third from the right) on the control. (You can also press the Control key while clicking the hyperlink, but you may need to click the link more than once.) The Data Source tab of the Commands And Options dialog box (Figure 15) appears.

Figure 15. The Commands And Options dialog box as it first appears when the PivotTable control’s Commands And Options toolbar button is clicked

Data Source Configuration

Click the Edit... button to display the Select Data Source dialog box (essentially, a standard File Open dialog box pointing to your My Data Sources folder). From here, you can select an existing Data Source file, but initially there will be none to select, so click the New Source... button on the bottom right to bring up the Data Connection Wizard (Figure 16).

Figure 16. The PivotTable control’s Data Connection Wizard, with the Microsoft SQL Server OLAP Services (Analysis Services) data source option selected

On the Welcome page of the wizard, select Microsoft SQL Server OLAP Services from the data source list and click Next. On the Connect To Database Server page, type your server name, make sure the Windows Authentication option is selected, and click Next again. On the Select Database And Table page, select the desired Analysis Services database from the drop-down list at the top, make sure the Connect To A Specific Cube Or Table check box is selected, and select the desired cube or perspective from the list below it. Click Next.

On the Save Data Connection File And Finish page, supply a name and description for your data source file (or accept the defaults) and click Finish. This will put you back in the Select Data Source dialog box, where you should select the data source file you just created and click Open (or simply double-click on the data source file name). After a brief pause, the Commands And Options dialog box will reappear. You can close it.

More Info

PivotTable data source files, which have an .odc extension, are saved by default in your My Documents\My Data Sources folder. Data source files have different icons depending on whether they connect to a particular database, connect to a specific cube within a database, or are merely designed to prompt the user for a server or a data source.

If you open an .odc file in Notepad, you will see that it contains very specialized HTML. Search for the <odc:ConnectionString> tag, and you will find the OLE DB connection string that is necessary to connect to the server, database, and cube you specified when you built the data source.

Perhaps more interestingly, if you open a cube-specific (or relational table/view-specific) data source file in Internet Explorer and choose to allow blocked content when prompted, you will see a Web page that hosts a PivotTable control that is already connected to the file’s data source.

Configuring PivotTable Controls at Design Time

The PivotTable control should now appear, still empty, in your form. In place of the OWC logo and connection hyperlink, the PivotTable’s Row, Column, and Filter and Detail Fields areas should be visible. The toolbar should appear with more of its buttons enabled, and a title area should appear between the toolbar and the Filter Fields area that reads “Microsoft Office PivotTable 11.0” (Figure 17).

Figure 17. A connected but empty PivotTable control, shown at design time in the Visual Studio Windows Forms designer

Click the (now enabled) Field List button on the toolbar (second from the right) to show the PivotTable Field List window. Notice that a parent node for the cube appears at the top of the list, and immediately beneath it is a node labeled “Totals” that contains all the measures in the cube. In most other respects, the PivotTable Field List is the same one we saw earlier within Excel.

Although you are technically working with the control in the design-time environment (rather than using it at run time), the control itself is almost fully functional. You can add dimensions and measures in the Windows Forms designer at design time without writing code and without directly setting properties. To do so, select items from the Field List window (Figure 18) and add them to the PivotTable’s areas using the area drop-down list and the Add To button at the bottom of the window. (Drag and drop will not work at design time.)

Figure 18. The Field List window, which you invoke by clicking the PivotTable control’s Field List toolbar button

After you configure your PivotTable, you should be able to use it interactively, even within the designer. Run your application (using the Debug/Start Debugging main menu option or the Start Debugging toolbar button) to see that users can easily do likewise at run time.

Setting Properties

Close your application and return to Visual Studio’s design-time environment. Some properties of the PivotTable can be set through the Visual Studio Properties window; others must be set in the Commands And Options dialog box that we just discussed. You can open this dialog box in three ways: by clicking the control’s Commands And Options toolbar button (third from the right), by right-clicking the PivotTable control and choosing Commands And Options... from the shortcut menu, or by clicking the control’s SmartTag and choosing the ActiveX –Commands And Options... option.


When launched through the SmartTag, the Commands And Options dialog box appears with a normal-sized title bar labeled “Properties” rather than a toolbox-sized title bar labeled “Commands And Options,” but it is in fact the same dialog box.

In addition to letting you set the PivotTable’s connection and data member information on the Data Source tab, the dialog box lets you set a number of the PivotTable’s appearance and formatting properties on the Format, Filter and Group, Captions, Report, Behavior, and Protection tabs. You might find setting the Report Title Bar caption to be especially useful because it lets you remove the default “Microsoft Office PivotTable 11.0” caption initially displayed on the control.

The specific tabs available in the Commands And Options dialog box depend on exactly where you right-click the PivotTable control before selecting the Commands And Options shortcut menu option.

Using Charts in Windows Forms Applications

Most of what we’ve already covered about PivotTable integration into your Windows Forms applications also applies to charts, but a few differences are worth noting. We’ll now quickly cover how to embed Excel charts in an application, in the context of what we’ve already described for PivotTables.

To start, right-click the toolbox and once again select the Choose Items... shortcut menu option. This time, you’ll want to add the Microsoft Office Chart 11.0 object (on the COM Components tab). You can then add an instance of the object to a new form, following the same procedure you followed with the PivotTable control. The default size of the ChartSpace control is larger than that of the PivotTable, so you might want to put Visual Studio into full-screen mode (choose View/Full Screen from the main menu or press Shift+Alt+Enter), resize the form, and then exit full-screen mode (by clicking the Full Screen button to the right of the main menu or by using either of the techniques you used to enter full-screen mode).

Configuring the Data Connection

Once the control is on the form, it should display the Office Web Components logo in gray. To connect the chart with specific data, you right-click the control and choose Data... from the shortcut menu. This brings up the chart’s Commands And Options dialog box. (You might need to right-click the control two or three times before the shortcut menu appears.) Although this dialog box shares a name with the PivotTable’s Commands And Options dialog box, it is significantly different (Figure 19).

Figure 19. The Commands And Options dialog box for the ChartSpace control as it first appears after you choose Data... from the control’s shortcut menu

Select the Data From A Database Table Or Query option. This causes the Data Details and Type tabs to appear and should also cause the caption of the button in the Data Source tab’s “2” section to change from Data Sheet... to Connection... and enable the button. Click the Data Details tab or the Connection... button to activate the tab (Figure 20), and then click the Edit... button on the Data Details tab.

Figure 20. The Data Details tab of the ChartSpace control’s Commands And Options dialog box, after the data source has been configured

You’re now back in the Select Data Source dialog box we discussed earlier in the context of the PivotTable control. Select an existing .odc Data Source file (you can first click New Source... to create a new one, as described earlier). After a short pause, the Commands And Options dialog box will reappear. From here, you can select a particular cube or perspective by clicking the Data Member, Table, View, Or Cube Name radio button (if necessary) and then making a selection from the drop-down list right below it. If your data source pointed to a specific cube or perspective, the drop-down list selection should already reflect the data source’s configuration.

Configuring Fields and the Chart Type

After you configure your data connection, close the Commands And Options dialog box. The ChartSpace control should now render with a partially enabled toolbar and empty chart area. As with the PivotTable Field List, you can show the Chart Field List using the appropriate toolbar button or shortcut menu option, and then add the appropriate measures, attributes, or hierarchies to the chart using the area drop-down list and Add To button at the bottom of the Chart Field List window.

The chart’s default type is Clustered Column. To change the type, click the Chart Type toolbar button (first actual button from the left, just to the right of the Microsoft Office logo) to reopen the Commands And Options dialog box, and then click on the Type tab. You can also click the Show/Hide Legend toolbar button (fourth actual button from the left, not counting the Office logo) to show a legend for the Series data.

Using the Properties Window with PivotTable and ChartSpace Controls

Although you can set properties for both the PivotTable and ChartSpace controls through the Commands And Options dialog box, you might want to assign values to certain of these properties directly in the Visual Studio Properties window. A number of useful properties are revealed there, and in many cases you can set their value through enumerated options in combo boxes. Noteworthy among them are ConnectionString, DataMember, DisplayFieldList, and DisplayToolbar. Using these four properties, you can configure many attributes of a PivotTable or ChartSpace control without using the Commands And Options dialog box (although you will still need the dialog box to change the default type of a chart).

Assigning a value to the ConnectionString property is easier than you might expect, and it avoids the need to build a data source. You simply use the standard OLE DB connection string format, using MSOLAP as the provider name.

Provider=MSOLAP;Data Source=localhost;Initial Catalog=C18

To connect to the Sales cube within the database, you can then set the DataMember property to Sales.

If you leave the DisplayFieldList property set to the default value of True, the Field List window will appear when the application is run and the form is shown (although at design time, the Field List window will appear and then quickly become hidden again). Set the property to False if you don’t want the Field List to appear initially when users execute your application.

For Charts Only

For a chart, you’ll likely want to set both the AllowPropertyToolbox property (which allows the Commands And Properties dialog box to appear at run time) and DisplayToolbar properties to True. The latter is set to True automatically when you configure the data connection through the Commands And Options dialog box. However, the property’s default value is actually False, and when you set the ConnectionString and DataMember properties though the Properties window, you must set DisplayToolbar to True explicitly. You might also want to change the chart’s type from the default Clustered Column; to do so, you must use the Type tab of the Commands And Options dialog box.

Other useful ChartSpace-specific properties include HasChartSpaceTitle and HasChartSpaceLegend, which make the chart’s title and legend visible, respectively. To change the chart’s title from the default “Chart Workspace Title,” click on the title itself, open the Commands And Options dialog box, and on the Format Tab edit the text in the Caption text box (near the bottom). Close the dialog box when you’re done.

UI Niceties

For both PivotTables and charts, you’ll likely want to set the Anchor property to Top, Bottom, Left, Right to ensure that the control will resize properly when the form is resized (for proper PivotTable resizing, you will also need to set the AutoFit property to False). For PivotTables, this will allow users with large, high-resolution monitors to see lots of data if they so choose; for charts, it will ensure that the series and category labels do not “crunch” together and overtype each other.

Setting PivotTable and ChartSpace Properties Programmatically

Most of the design-time property settings we’ve described can also be set in code. This gives you the opportunity to create your own UI, solicit from your users the particular cube or perspective they’d like to see, and then present them with an empty but connected PivotTable or chart, a displayed Field List, and the ability to drag and drop the data items of their choice onto the appropriate areas of the control.

That application references the Microsoft OLE DB Service Component 1.0 Type Library to allow programmatic invocation of the standard Data Link dialog box. This dialog box lets users specify their connection strings through a standard Windows UI rather than having to type them in. It then interrogates the PivotTable or ChartSpace object using special code to populate a combo box with a list of cubes and perspectives from the Analysis Services database indicated by the user’s connection string.

The sample code demonstrates how a PivotTable or ChartSpace control’s ConnectionString, DataMember, and other properties can be set through code by fetching the connection string from the Data Link dialog box and the desired cube or perspective name through the ComboBox. One nice thing about working in code is that several properties that are not available through the Properties window can be set programmatically. For example, you can set the chart type in a single line of code. If you want to set the chart type of a ChartSpace control named echMain to 3D Area, you can do so with the following line of code:

echMain.Charts[0].Type =

What About the Web?

In case you were wondering, both the PivotTable and ChartSpace controls can be used within Web pages as ActiveX controls. As long as blocked content is enabled in Internet Explorer or the page’s site of origin is included in your list of trusted sites, both controls will render properly within Internet Explorer on any machine running an appropriate version of Office.

OWC and FrontPage

An easy way to begin Web development with Office Web Components is to compose your pages (or at least start composing them) using Microsoft FrontPage. In FrontPage, you can easily insert OWC controls onto a page, and you get a design-time experience similar to that of the Windows Forms designer in Visual Studio. FrontPage might not be everyone’s Web development tool of choice, but it is good for generating the appropriate <object> tag HTML snippets. You can then insert these snippets into Web pages in other design tools, including ASP.NET pages in Web site projects with Visual Studio 2005. We’ll quickly cover both Web development environments now.

To see the FrontPage OWC design-time support in action, start up FrontPage on your system, create a new HTML page (or use the default page created by FrontPage upon startup), and enter design mode by clicking the Design button at the far lower-left corner of the FrontPage window. Choose Insert/Web Component... from the main menu to open the Insert Web Component dialog box.

In the dialog box, scroll down to the end of the Component Type list (on the left) and select Advanced Controls (the last item in the list). From the Choose A Control list (on the right), select ActiveX Control. The dialog box should appear as shown in Figure 21.

Figure 21. The Insert Web Component dialog box in FrontPage 2003, with the Advanced Controls component type and ActiveX Control control type selected

Click Next and select Microsoft Office PivotTable 11.0 (or Microsoft Office Chart 11.0) from the Choose A Control list, and then click Finish.

At this point, you should see an instance of your selected control on your HTML page. If you inserted a ChartSpace control, the Commands And Options dialog box should be visible (Figure 22); if you inserted a PivotTable control, you must click the “Click here to connect to data” hyperlink or the Commands And Options toolbar button on the control to show the Commands And Options dialog box.

Figure 22. The ChartSpace control’s Commands And Options dialog box, invoked in FrontPage design view

Under ideal conditions, you could configure the control’s data connection and contents right away. Unfortunately, default safety settings on your PC will likely prevent you from making a cube selection in the Commands And Options dialog box; even if you type the cube name manually, these same settings will probably prevent you from viewing any of your cube’s measures or dimensions in the Field List window.

To correct this, simply save your page and then refresh it by using the F5 key, by choosing View/Refresh from the main menu, or by clicking the Refresh button (fifth from the right) on the FrontPage Standard toolbar. FrontPage will then advise you that the page uses a data provider that might be unsafe and that the page is trying to connect to a data source under your login’s identity (Figure 23 and Figure 24).

Figure 23. The unsafe data provider warning message in FrontPage

Figure 24. The identity impersonation warning message in FrontPage

Click OK in each message box. The page and the control will then render properly, and you can use the Commands And Options dialog box and the Field List window by clicking the appropriate buttons on the control’s toolbar. From here, you can customize your control to configure it just the way you want. Bear in mind that FrontPage does not have a Properties window like the one in Visual Studio, so the Commands And Options dialog box and the Field List window are your only tools for customizing the control on the page. Once you’re done, you can save the page and close FrontPage.

Blocked Content in Internet Explorer

At this point, the page can be viewed in Internet Explorer, either from the file system or through a Web server. However, the page will render properly upon initial display only if the page is served from a Web server (including your own IIS instance on your local PC) and that Web server has been entered into your list of trusted sites in Internet Explorer. If you open the file directly from the file system or from an untrusted Web site, both the PivotTable and ChartSpace controls will initially fail to render and will display warning messages, as shown in Figure 25.

Figure 25. Our FrontPage-designed OWC Web page shown in Internet Explorer 6, with active content blocked

The error messages displayed are misleading because they suggest that the OWC controls are not properly installed. You can cause the page to render properly by simply clicking the yellow security bar at the top of the page, selecting the Allow Blocked Content... option on the shortcut menu, and clicking Yes in the subsequent Security Warning message box.

Programming OWC Controls in ASP.NET Applications

We’ve covered all you really need to know about composing Web pages using the OWC PivotTable and ChartSpace controls. Meanwhile, as a developer, especially as a .NET developer, you’d probably prefer not to use FrontPage as your development tool. For example, it might make a great deal of sense to develop an ASP.NET application that features its own UI through which your users indicate the server and cube they’re interested in looking at. From there, you might dynamically generate a page containing OWC controls preconfigured according to the user’s selections, with the Field List window displayed.

In trying to build such an application, you’ll face two hurdles. First, the ASP.NET page designer in Visual Studio 2005 does not allow you to design an OWC-control-based page manually. Second, adapting a FrontPage-generated page is extremely difficult because all property values in the FrontPage-generated HTML are inserted into the control’s XMLData property.

The solution is fairly easy: You simply need to know the basic HTML markup to emit for the PivotTable and chart controls, as well as the additional HTML required to pre-configure each control’s properties. In general, the HTML you need for either control has the following structure, where controlname is the actual name of the control, controlclassid is the GUID that identifies the control, and propertyname and propertyvalue represent the name of a property you want to preconfigure and the value you want to assign to it, respectively. (You can include multiple property name/value pairs.)

<object classid="controlclassid" id="controlname">
    <param name="propertyname" value="propertyvalue" />

This might seem abstract, but it’s actually quite straightforward. For example, the following HTML code, embedded in an ASPX page, will properly display a PivotTable control, connected to the Sales cube, on the local server, with its Field List window visible:

<object classid="clsid:0002E55A-0000-0000-C000-000000000046"
  <param name="ConnectionString" value="Provider=MSOLAP;Data Source=.;Initial Catalog=C18" />
  <param name="DataMember" value="Sales" />
  <param name="DisplayFieldList" value="True" />


And the following code will do likewise for an OWC ChartSpace control:

<object classid="clsid:0002E55D-0000-0000-C000-000000000046"
  <param name="ConnectionString" value="Provider=MSOLAP;Data Source=.;Initial Catalog=C18" />
  <param name="DataMember" value="Sales" />
  <param name="DisplayToolbar" value="True" />
  <param name="AllowPropertyToolbox" value="True" />
  <param name="DisplayFieldList" value="True" />


With knowledge of this rather simple HTML, you can quite easily create an ASP.NET interface that requests server, database, cube, and control type (PivotTable or ChartSpace) from the user and then presents a page with the requested control connected to the requested cube.

More Info

Neither of the previous code snippets contains code to pre-configure the measures (data fields) or dimensions (row and column fields). It is possible to do this (in fact, we proved that when we designed the pages in FrontPage), but it requires rather complex programming of the controls’ XMLData property. If you are interested in manipulating this property, you might want to reverse-engineer the FrontPage-generated HTML. You’ll find that HTML much easier to read if you replace all occurrences of &lt; and &gt; with < and >, respectively.

  •  Microsoft SQL Server 2008 R2 : Hierarchyid Data Type (part 2) - Modifying the Hierarchy
  •  Microsoft SQL Server 2008 R2 : Hierarchyid Data Type (part 1) - Creating a Hierarchy, Populating the Hierarchy, Querying the Hierarchy
  •  Using SQL Server 2005 Integration Services : Extensibility (part 4) - Custom Connection Managers
  •  Using SQL Server 2005 Integration Services : Extensibility (part 3) - Script Components
  •  Using SQL Server 2005 Integration Services : Extensibility (part 2) - Custom Components
  •  Using SQL Server 2005 Integration Services : Extensibility (part 1) - Script Tasks
  •  .NET Compact Framework 3.5 : Working with Data Sets (part 3) - Reading and Writing a Data Set as XML
  •  .NET Compact Framework 3.5 : Working with Data Sets (part 2) - Data Binding
  •  .NET Compact Framework 3.5 : Working with Data Sets (part 1) - Creating and Accessing DataSet, DataTable, and DataView Objects
  •  .NET Compact Framework 3.5 : Examining ADO.NET
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us