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.
Important
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).
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).
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.
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).
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).
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.)
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.
Note
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).
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.
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 =
Microsoft.Office.Interop.Owc11.ChartChartTypeEnum.chChartTypeArea3D;
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.
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.
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).
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.
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" />
</object>
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"
id="PivotTable1">
<param name="ConnectionString" value="Provider=MSOLAP;Data Source=.;Initial Catalog=C18" />
<param name="DataMember" value="Sales" />
<param name="DisplayFieldList" value="True" />
</object>
And the following code will do likewise for an OWC ChartSpace control:
<object classid="clsid:0002E55D-0000-0000-C000-000000000046"
id="ChartSpace1">
<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" />
</object>
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 < and > with < and >, respectively. |
