译者说明：欢迎访问我的Blog: http://blog.csdn.net/daidaoke2001/
译文中的错误或不当之处望不吝指出，这也是我坚持翻译工作的最大动力。
我的Email：tangtaike@163.com
如需转载，请事先通知。
高级编程技巧
至此，您已经学会了如何完成一些基础的编程操作，现在让我们来讨论一些更高级的技巧。在第七章的销售分析和报表解决方案中会涉及到许多这些高级技巧，因此现在我只对这些技巧作一些简单的介绍，您可以参考第七章，其中有关于本节中所包含的代码的详细解释。
保存和恢复视图
任何在报表系统中使用透视表组件的开发者都有可能需要向他们的用户提供如下功能：保存创建的报表视图，并在以后恢复这个报表――但报表中的数据将是最新的数据。透视表控件能够很容易的完成这个任务。在第七章的解决方案中讲述了这种技巧。
最常见的保存和恢复视图的方法是使用控件顶级接口的XMLData属性。这是一个读/写的属性，它返回一个XML格式的庞大的字符串。别将它和用作数据源的XML流混淆了――XMLData属性返回的是当前视图的布局，格式化，过滤，排序，和等等其它的定义信息。这个定义信息的字符串对视图进行了完整的描述，但是不包含任何数值。
如果需要保存当前视图的定义，应该读取这个属性的值，并将它保存在一个以后您可以重新提取它的地方。一个常用的方法是将这个字符串提交给一个ASP页面或CGI程序，该页面或程序会接着将这个字符串写入一个文件中，或写入数据库中并与当前用户进行关联。当用户需要再次查看报表时，程序应该从永久存储设备中提取这个字符串，并用它来设置XMLData属性。属性被设置后，透视表控件会清除所有当前显示的数据，连接到原始数据源（如果还没有连接到数据源），并执行相应的查询以重新创建报表。因此用户会在报表中看到所有最新的数据，但报表的布局和用户存储报表时完全一样。
对于视图中那些不再有效的部分，透视表控件会简单的丢弃它们。例如，如果在保存视图后，视图中的一个字段集在数据源上被删除了，透视表控件会忽略所保存的与这个字段集有关的任何信息，而不会在视图中尝试恢复它。对于不再包含在查询返回的数据集中的汇总值或成员，也是一样的。
可以运行随书光盘中的XMLDataProperty.htm文件来观看如何使用XMLData属性。页面顶部的按钮允许您获得XMLData属性的值，以及设置这个属性的值。此外，还可以清空透视表报表。请尝试着使用获得属性的值，清空透视表报表，以及重置属性这三个功能。
注意，连接信息（连接字符串，以及cube名称或命令文本）也包含在XMLData属性返回的字符串中。如果在用户保存报表和重新打开它之间数据源的位置发生了变化，透视表将无法连接到数据源，并产生一个错误。如果可能会发生这种情况，您应该添加错误处理代码来进行捕捉，并在将字符串传递给透视表控件之前调整其中的连接信息。连接字符串是存储在<x:ConnectionString>标签中的，数据成员(OLAP数据源的cube的名称)是存储在<x:DataMember>标签中的，而命令文本(用于表列数据源)是存储在<x:CommandText>标签中的。可以使用VBScript或微软VBA中的InStr和Replace函数轻松的查找和替换这些标签的内容。第七章中有关于XMLData属性和如何改变数据源的更多信息。
锁定视图
报表系统经常需要发布两种类型的报表：一种是每个人都可能需要查看的标准报表，另一种是用户可以修改报表结构，并保存的特殊报表。您可能需要对标准报表进行设置，以便用户不能在报表中删除或添加字段，但仍能够进行钻取和过滤。您也可能需要禁止过滤功能，只允许在视图中进行展开和收缩的操作。透视表组件提供了一些设置选项，可以帮助您锁定视图。它还提供了一些事件，可以用来监控当前用户正在进行什么操作；但是，并不存在禁止这些操作的通用机制。
XML历险
所有这些令人惊奇的XML的功能都应该归功于另一位OWC小组的著名开发者，Kevin Grealish，他花费了无数个夜晚来研究xml解析器，名称空间，以及那些不断变化的格式和标准。当我们开发透视表组件时，关于名称空间的xml标准仍然不断的被大幅度的修改。因为我们必须要读取Excel2000发布的XML数据，所以要使我们的代码处理xml的功能与Excel保持一致，这是一件艰苦长期的工作。而如果透视表报表的数据源是表格数据，我们的代码还必须与用来装载Excel保存的XML数据流的MDAC永久提供者保持一致。最后Kevin解决了这个矛盾――所以下次在您使用XMLData属性或使用XML作为数据源时，记得要感谢Kevin呦。
如果不允许用户在行轴或列轴上添加或删除字段，应该将透视表控件的AllowGrouping属性设置为False。当该属性为False时，透视表控件就会禁止用户在行轴或列轴上添加或删除字段。不过用户仍然可以将字段集添加到过滤轴上，以及将新的汇总值添加到视图中。
如果不允许用户改变任何过滤设置，应该将AllowFiltering属性设置为False。当这个属性为false时，透视表组件允许用户打开过滤下拉列表，但是不允许用户改变当前的过滤设置――换句话说，用户可以看到当前的过滤条件，但不能改变它们。控件还会禁止用户将字段集添加到过滤轴上。
为了保证用户不能改变应用到报表上的格式化信息，可以将AllowPropertyToolbox属性设置为False。这会使工具条上的属性工具箱按钮，以及相应上下文菜单中的菜单项不可用。这样用户甚至都不能打开属性工具箱了。但是，用户还是可以使用键盘格式化命令，例如Ctrl-B,Ctrl-I,和Ctrl-U。请查看随书光盘中的LockDownView.htm例子文件，以练习这些属性的用法。
判断所选择的部分，以进行穿透钻取
和图表组件一样，透视表组件在顶级接口中也有一个Selection属性，用来返回当前被选择的对象。而且这个属性返回的对象的类型也是变化的，因此需要使用VBScript和VBA中的TypeName函数来判断返回对象的类型。请运行随书光盘中的DeterminingSelection.htm文件以查看透视表控件可以从这个属性返回哪些不同类型的对象。
如果知道当前被选择的是什么，就可以模拟一些有趣的功能。在这里讨论一个这样的功能：通过穿透钻取以获得详细信息。
OLAP系统善于显示高层的数据摘要，并允许用户通过“向下钻取”来获得各层的详细信息。但是，用户最终会到达超立方体中的最底层，并常常需要“穿透钻取”的功能以获得组成最低层汇总值的下层详细信息。如果当前使用的是一个表列数据源，则透视表控件可以自动完成这个工作，因为在表列数据源的情况下，详细数据总是可用的。但是，对于OLE DB for OLAP来说，还没有一种通用方法，可用来提取合计后面的详细信息的集合。尽管如此，垂直解决方案通常都能获得足够的信息，以找出那些包含了详细数据的表列数据源，并从而能够确定应该组成一个怎样的sql语句，以获得组成这个合计值所需的所有成员的详细信息数据行。？？？？？？。
下面取自随书光盘中DeterminingSelection.htm例子文件的代码，显示了如何从被选择的汇总值中获得所需的全部信息，以生成提取汇总值的详细信息的sql语句。
Sub PivotTable1_SelectionChange()
' Local variables
Dim sel ' Temporary selection object
Dim sFilters ' Current filter strings
Dim fSet ' Temporary fieldset reference
' Grab the current selection
Set sel = PivotTable1.Selection
' There are many types of objects that the selection
' could be, depending on what was selected
' Examples include PivotAggregates, PivotTotals,
' PivotMembers, PivotFields, and PivotView
' You can use the TypeName function to determine the type
' of the object
'
' If the user selected an aggregate number,
' the TypeName function will return "PivotAggregates"
' Set the type name label
lblType.innerText = TypeName(sel)
' If the type is "PivotAggregates", show how to get
' the row and column members that define that aggregate
' You could of course make this a Select Case statement
' and handle other selection types
If TypeName(sel) = "PivotAggregates" Then
' PivotAggregates could contain many items, but since
' this is a sample, I will just work with the first item
Set pivotagg = sel.Item(0)
' Set the value label
lblVal.innerText = pivotagg.Value
' Get the total caption, the row and column members,
' and the current filters
lblTotal.innerText = pivotagg.Total.Caption
lblColMems.innerText = BuildFullName(pivotagg.Cell.ColumnMember)
lblRowMems.innerText = BuildFullName(pivotagg.Cell.RowMember)
For Each fset In PivotTable1.ActiveView.FilterAxis.FieldSets
sFilters = sFilters & fset.Caption & "=" & _
fset.FilterMember.Caption & ", "
Next
lblFilters.innerText = sFilters
Else
' Selection was something other than a PivotAggregates
' object. Clear the labels.
lblVal.innerText = ""
lblTotal.innerText = ""
lblRowMems.innerText = ""
lblColMems.innerText = ""
lblFilters.innerText = ""
End If 'typename(sel) = "PivotAggregates"
End Sub 'PivotTable1_SelectionChange()
这段代码首先使用TypeName函数来判断被选择部分的类型；如果类型是PivotAggregates，则表明用户选择了一个汇总值。代码就会提取汇总值的数值，以及汇总值所属的合计的值。（规定了一个汇总值只能属于一个合计。）接着，代码使用辅助函数BuildFullName，来创建一个包含轴上这一层的所有成员的字符串。BuildFullName函数的代码如下：
Function BuildFullName(PivotMem)
' Local variables
Dim pmTemp ' Temporary PivotMember reference
' Start by getting the current member's name
sFullName = PivotMem.Caption
' Set the temporary reference to the current member
Set pmTemp = PivotMem
' Navigate up the parent hierarchy until you hit nothing
While Not(pmTemp.ParentMember Is Nothing)
Set pmTemp = pmTemp.ParentMember
sFullName = pmTemp.Caption & "-" & sFullName
Wend
' Return sFullName
BuildFullName = sFullName
End Function 'BuildFullName()
代码的关键部分是中间的While循环。前面提到过，每一个成员都有一个ParentMember属性，用于返回这个成员的父成员（如果存在的话）。如果不存在父成员，属性将返回Nothing（在其它语言中它是一个指向Null对象的指针）。循环根据在层中向上游历时所遇到的每一个成员的Caption属性来生成一个字符串。当然，您可能需要使用Name属性，或甚至是UniqueName属性（而不是Caption属性）来获得SQL语句中WHERE子句所需要的值。
显示空成员
缺省情况下，透视表组件是不显示报表中那些每个单元格都不包含数据的行或列的。但是，有时没有数据的行或列也是有意义的，您需要显示它们。可以使用下面的代码来实现这个功能：
PivotTable1.ActiveView.RowAxis.DisplayEmptyMembers = True
PivotTable1.ActiveView.ColumnAxis.DisplayEmptyMembers = True
DisplayEmptyMembers是行或列轴的一个属性，缺省值为False。对于OLAP数据源来说，这个设置会影响透视表组件发往数据源的MDX查询语句，因此，如果这个属性被设置为True，会传回更多的数据。而对于表列数据源来说，它也会影响用来查询临时cube的MDX语句，但是因为此时客户端包含了所有的数据，所以这个属性几乎不会影响性能――除非临时立方体非常稀疏。
显示可视合计值
缺省情况下，透视表组件只显示“可视合计值”。这意味着报表中的小计和总计是当前报表中显示的数据的合计。如果一个成员被过滤了，那么这个成员之上的小计和总计就不会包括这个成员的值。这样，即使您过滤了某些成员，报表中显示的所有数据也会被累加起来。
假设在行轴上有一个地理层，其中包含两个级别：州和城市。每个州的小计代表了它的所有城市的合计。现在如果过滤掉城市Seattle和Redmond。华盛顿州的合计应该不变，还是变成现在仍然显示在报表中的所有城市的合计？这确实是一个争论的很激烈的问题。不过幸运的是，透视表控件支持这两种模式。
透视表组件缺省显示可视合计值。如果需要控件显示所有成员的合计值，而不考虑成员是否可视，请使用下列代码：
PivotTable1.ActiveView.TotalAllMembers = True
在TotalAllMembers属性为True的情况下，透视表控件会在小计和总计的旁边显示一个小星号，以显示这个数值并不是仅代表可视成员的。显示星号的行为是和Excel 2000的OLAP透视表报表功能中的行为相同的。如果您能够在透视表控件的下方使用HTML对星号的含义进行解释，那就更完美了。
将合计标题显示为行标题
缺省情况下，透视表组件在报表上将合计标题显示为最内层的列标题。但有时，可能需要将这些合计标题显示为最内层的行标题。您可以使用下面这一行代码轻松的完成这个任务：
PivotTable1.ActiveView.TotalOrientation = plTotalOrientationRow
TotalOrientation属性可以被设置为PivotViewTotalOrientationEnum常量中的一个值，该常量包含标明行方向的值，和标明列方向的值。透视表控件只能将合计的标题放置在最内层的行轴或列轴上。
自动展开
缺省情况下，透视表组件会在将所有的字段和成员添加到视图中时，使它们保持收缩状态。用户可以沿轴以任何路径进行展开，以获得他们需要的更详细的信息。您可能需要对您的报表进行配置，使得在所有的字段和成员被添加到视图中时，会自动展开。因为这可能会产生一个巨大、难以处理的显示界面，所以应该只在自动展开功能产生的是一个大小适当的报表时采使用这个功能。
可以使用下面这一行代码来打开自动展开功能：
PivotTable1.MemberExpand = plMemberExpandAlways
这个属性的值取自PivotTableMemberExpandEnum枚举值，该枚举值中其它可用的值有plMemberExpandNever和plMemberExpandAutomatic（缺省值）。
当在一个web服务器上使用透视表控件生成报表的GIF图形时（下一节我们将讨论这个功能），这个属性也很有用。设置这个属性为plMemberExpandAlways，以便成员总是被展开，这样就能保证在生成图形时，报表中的所有信息都被显示出来――这是很重要的，因为用户得到的只是报表的一个静态图形，是不能展开图形中的成员的。
在Web服务器上使用透视表组件
和其它Office Web组件一样，透视表组件也能以没有用户界面的方式被使用，您可以以内存对象的形式来创建透视表组件，然后连接到一个数据源，使用程序动态的生成一个报表，最后生成当前报表的GIF图形，或通过遍历报表视图中的元素来生成您自己的html形式的报表。在web服务器上使用透视表控件的方式，可能比书写MDX查询语句，然后使用ADO MD，最后手工格式化查询结构要更加吸引人。
当在一个服务器上使用透视表控件时，可以使用目前为止我们所讨论的所有编程技巧。唯一一个您需要了解的新的编程元素是ExportPicture方法。这个方法几乎和第三章中讨论的图表组件的ExportPicture方法完全一样。下面的代码可以实现将当前报表输出为一个GIF图形文件：
Set fsoTemp = CreateObject("Scripting.FileSystemObject")
strFilename = fsoTemp.GetTempName
PivotTable.ExportPicture Session("strTempFilePath") & strFilename, _
"gif", PivotTable.MaxHeight, PivotTable.MaxWidth
Response.Write "<IMG SRC='" & Session("strTempURLPath") & _
strFilename & "'>"
Session("Pivot" & Session("cntPivotImages")) = strFilename
Session("cntPivotImages") = Session("cntPivotImages") + 1
这段代码的关键行是对ExportPicture方法的调用。FileSystemObject（微软脚本运行时库的一个类）的一个实例获得了一个用于新的GIF图形的临时文件名，然后程序将这个文件名加上根路径，形成的结果传递给ExportPicture方法，作为第一个参数。和图表组件一样，第二个参数必须是”gif”。最后两个参数指明了高度和宽度，但这里和图表组件中的编程不同（图表组件中可以将输出的图形改变成任何尺寸），您必须使用透视表组件的MaxHeight和MaxWidth属性，否则生成的报表图形可能不完整。
使用在服务器上生成报表图形的功能，可以创建最极端的受限版本。用户可以在任何平台，任何可显示GIF图形的浏览器中查看报表图形，但报表是完全不能交互的。
附录：英文原文。
Advanced Programming Techniques
Now that you know how to perform some of the basic programmatic operations, let's discuss a few of the more advanced techniques you can use with the PivotTable component. Many of these are implemented in the Sales Analysis and Reporting solution covered in Chapter 7, so I will briefly discuss these techniques here and refer you to that chapter for a more detailed explanation of the code involved.
Saving and Restoring Views
Anyone using the PivotTable component for a reporting system will at some point want to enable users to save a report view they have constructed and recall that report later—but with current data. The PivotTable control makes this quite easy to do. In fact, the solution in Chapter 7 illustrates this technique.
The basic approach to saving and restoring a view is to use the XMLData property at the top-level interface of the control. This property is read/write and returns a large string in an XML format. Don't confuse this with using an XML stream as a data source—the XMLData property returns a definition of the current view's layout, formatting, filters, sorts, and so on. The string completely describes the view but does not contain any data values.
If you want to save the current view definition, get this property's value and save it where you can retrieve it later. A typical approach is to post this string to an ASP page or a CGI program, which would in turn write the string to a file or database associated with the current user. When the user wants to view that report again, your code retrieves this string from the persistent storage and sets the XMLData property to the retrieved string. When you set this property, the PivotTable control throws away any data it is currently showing, connects to the original data source (if it's not connected already), and executes the appropriate query to re-create the report. The user will see any new data that appears in the data source, but the report layout will be the same as it was when the user saved it.
The PivotTable control silently discards parts of the view that are no longer valid. For example, if the user had a fieldset in the view that was removed from the data source after the view was saved, the PivotTable control will ignore any information saved with that fieldset and will not attempt to restore it in the view. The same is true for totals or members no longer contained in the data set.
To experiment with the XMLData property, run the XMLDataProperty.htm file on your companion CD. The buttons at the top of the page allow you to get the XMLData property and set it back. Plus, they let you clear the PivotTable report. Try getting the property, clearing the PivotTable report, and resetting the property.
Note that the connection information (connection string and name of the cube or command text) is also included in the string returned by the XMLData property. If the location of the data source changes between the user saving the report and reopening it, the PivotTable will fail to connect and will generate an error. If this is a possibility, you should include error-handling code to catch this and to adjust the connection information in the string before handing it to the PivotTable control. The connection string is stored in the <x:ConnectionString> tag, the data member (cube name for an OLAP source) is stored in the <x:DataMember> tag, and the command text (used for a tabular source) is stored in the <x:CommandText> tag. You can use the InStr and Replace functions in VBScript or Microsoft VBA to easily find and replace the contents of these tags. See Chapter 7 for more information on the XMLData property and changing data sources.
Locking Down a View
Reporting systems often present two types of reports: standard reports that everyone will probably want to see and ad hoc reports that users can construct themselves and save. You might want to configure those standard reports so that users cannot remove or add fields to the reports, but they can still drill down and filter. You might also want to disallow filtering, allowing only expanding and collapsing within the view. The PivotTable component offers a few settings to help you lock down the view. It also raises some events that you can use to monitor what users are doing; however, no general mechanism for denying those actions exists.
Adventures in XML
All this wonderful XML stuff only works because another of OWC's star developers, Kevin Grealish, spent many a late night wrestling with the XML parser, namespaces, and seemingly daily changes in formats and standards. While we were in the middle of developing the PivotTable component, the XML standards for namespaces were fluctuating quite a bit. Since we had to read in XML data that Excel 2000 published, it was a continual dance to keep Excel and our code bases in sync. When the source of the PivotTable report was on-sheet data, we also had to stay in sync with the MDAC persistence provider used to load the XML-Data streams saved by Excel. This discrepancy eventually worked itself out—so the next time you use the XMLData property or use XML as a data source, thank Kevin.
To prohibit users from adding or removing fields from the row and column axes, set the AllowGrouping property of the PivotTable control to False. When this property is False, the PivotTable control prohibits the user from inserting or removing fields from the row or column axis. Users can still add fieldsets to the filter axis and add new totals to the view.
To prohibit users from changing any of the filter settings, set the AllowFiltering property to False. When this property is false, the PivotTable component will let users open the filtering drop-down lists but prohibit them from changing the current filter settings—in other words, they can view the current filters, but they can't change them. The control also prevents the user from adding more fieldsets to the filter axis.
To make sure the user cannot change formatting applied to the report, set the AllowPropertyToolbox property to False. This will disable the Property Toolbox button on the toolbar and the corresponding context menu item, prohibiting the user from even opening the Property Toolbox. However, users can still use the keyboard formatting commands, such as Ctrl-B, Ctrl-I, and Ctrl-U. To experiment with these various properties, see the LockDownView.htm sample file on your companion CD.
Determining Selection for Drill Through
Like the Chart component, the PivotTable component has a Selection property at the top level that returns the currently selected object. As with the Chart component, the type of object returned by this property varies, so use the TypeName function in VBScript and VBA to determine the object type. To see the different kinds of objects the PivotTable control can return from this property, run the sample file DeterminingSelection.htm on your companion CD.
You can emulate several interesting features if you know what is currently selected. I will discuss one of these features here: drilling through to details.
OLAP systems are good at showing users a high-level data summary and letting them drill down to deeper and deeper levels of detail. However, a user will eventually reach the lowest level of the hierarchies in the hypercube and often will want the ability to drill through to the underlying details that make up that lowest-level aggregate. In the case of a tabular data source, the PivotTable control can do this automatically because the detail data is readily available. However, there is not yet a general way in OLE DB for OLAP to retrieve the set of details behind an aggregate. Despite this fact, vertical solutions often have enough domain knowledge to pinpoint which tabular data source contains the detail data and to determine how to construct a SQL statement to get the detail rows given all the members that intersect to produce that aggregate.
The following code, taken from the DeterminingSelection.htm sample file on the companion CD, shows how to get all the information from a selected aggregate to formulate a SQL statement that gets the details:
Sub PivotTable1_SelectionChange()
' Local variables
Dim sel ' Temporary selection object
Dim sFilters ' Current filter strings
Dim fSet ' Temporary fieldset reference
' Grab the current selection
Set sel = PivotTable1.Selection
' There are many types of objects that the selection
' could be, depending on what was selected
' Examples include PivotAggregates, PivotTotals,
' PivotMembers, PivotFields, and PivotView
' You can use the TypeName function to determine the type
' of the object
'
' If the user selected an aggregate number,
' the TypeName function will return "PivotAggregates"
' Set the type name label
lblType.innerText = TypeName(sel)
' If the type is "PivotAggregates", show how to get
' the row and column members that define that aggregate
' You could of course make this a Select Case statement
' and handle other selection types
If TypeName(sel) = "PivotAggregates" Then
' PivotAggregates could contain many items, but since
' this is a sample, I will just work with the first item
Set pivotagg = sel.Item(0)
' Set the value label
lblVal.innerText = pivotagg.Value
' Get the total caption, the row and column members,
' and the current filters
lblTotal.innerText = pivotagg.Total.Caption
lblColMems.innerText = BuildFullName(pivotagg.Cell.ColumnMember)
lblRowMems.innerText = BuildFullName(pivotagg.Cell.RowMember)
For Each fset In PivotTable1.ActiveView.FilterAxis.FieldSets
sFilters = sFilters & fset.Caption & "=" & _
fset.FilterMember.Caption & ", "
Next
lblFilters.innerText = sFilters
Else
' Selection was something other than a PivotAggregates
' object. Clear the labels.
lblVal.innerText = ""
lblTotal.innerText = ""
lblRowMems.innerText = ""
lblColMems.innerText = ""
lblFilters.innerText = ""
End If 'typename(sel) = "PivotAggregates"
End Sub 'PivotTable1_SelectionChange()
This code first uses the TypeName function to determine the type of selection; if that type is PivotAggregates, you know that the user has selected an aggregate number. The code then retrieves the value of the aggregate and the total to which the aggregate belongs. (An aggregate by definition belongs to just one total.) Next, the code uses a helper function, BuildFullName, to build a single string representing all members up the hierarchy on the axis. The code for BuildFullName follows:
Function BuildFullName(PivotMem)
' Local variables
Dim pmTemp ' Temporary PivotMember reference
' Start by getting the current member's name
sFullName = PivotMem.Caption
' Set the temporary reference to the current member
Set pmTemp = PivotMem
' Navigate up the parent hierarchy until you hit nothing
While Not(pmTemp.ParentMember Is Nothing)
Set pmTemp = pmTemp.ParentMember
sFullName = pmTemp.Caption & "-" & sFullName
Wend
' Return sFullName
BuildFullName = sFullName
End Function 'BuildFullName()
The key part of this code is the While loop in the center. As mentioned earlier, every member has a ParentMember property that returns the parent for the member if one exists. If it does not, the property returns Nothing, which is a Null object pointer in other languages. The loop builds a single string using the Caption property of each member as it walks up the hierarchy. Of course, you might want to use the Name property or even the UniqueName property (instead of the Caption property) to retrieve values needed in a SQL WHERE clause.
Showing Empty Members
By default, the PivotTable component will not display any report row or column that has no data values in any of its cells. However, sometimes the fact that a row or column has no data is meaningful, and you want to display it anyway. To do so, write the following code:
PivotTable1.ActiveView.RowAxis.DisplayEmptyMembers = True
PivotTable1.ActiveView.ColumnAxis.DisplayEmptyMembers = True
DisplayEmptyMembers is a property of the row or column axis, and by default, its value is False. For an OLAP source, this setting affects the MDX query the PivotTable component sends to the data source, so more data is downloaded when this property is set to True. For a tabular data source, this also affects the MDX used to query the temporary cube. But since all the data is on the client by that time, it hardly affects performance—unless the cube is exceedingly sparse.
Displaying Visual Totals
By default, the PivotTable component displays what are known as visual totals. Displaying visual totals means that the subtotals and grand totals in the report are the totals of the data currently displayed in the report. If a member is filtered out, the subtotals and grand totals above that member do not include that member's value. This way, all the data shown in the report adds up, even if you filter out some members.
Suppose you have a geographical hierarchy on the row axis with two levels: State and City. The subtotal for each state reflects the total of all cities in it. Now suppose you filter out the cities Seattle and Redmond. Should the total for Washington state be the same number as before, or should it be the total of all cities still shown in the report? This is actually a hotly debated issue. Fortunately, the PivotTable control supports both modes.
The PivotTable component shows visual totals by default. If you want this control to show totals for all members regardless of their visibility, use this code:
PivotTable1.ActiveView.TotalAllMembers = True
The PivotTable control will place small asterisks next to the subtotals and grand totals when TotalAllMembers is True to indicate that the values are not reflecting the visibility of members. The asterisks are shown to match Excel 2000's OLAP PivotTable reports feature behavior. Ideally, you should put an explanation of the asterisk in HTML below the PivotTable control.
Showing Total Captions as Rows
By default, the PivotTable component displays total captions as the innermost column headings on the report. Occasionally, it is desirable to show those total captions as the innermost row headings instead. You can easily accomplish this with the following line of code:
PivotTable1.ActiveView.TotalOrientation = plTotalOrientationRow
The TotalOrientation property is set to one of the PivotViewTotalOrientationEnum constants, which include values for row or column orientation. The PivotTable control does not yet support placing the total caption anywhere but the innermost level of the row or column axis.
Expanding Automatically
By default, the PivotTable component will leave all fields and members collapsed when they are added to the view. Users can then expand any path along the axis about which they want to see more detail. You might want to configure your report to automatically expand all fields and members as soon as they are added to the view. Since this can result in a large and unwieldy display, you should do this only if auto-expansion will generate a reasonably sized report.
To turn the auto-expansion behavior on, write the following line of code:
PivotTable1.MemberExpand = plMemberExpandAlways
The value of this property is taken from the PivotTableMemberExpandEnum enumeration, with the other possible values of plMemberExpandNever and plMemberExpandAutomatic (which is the default).
This property also comes in handy when using the PivotTable control on a web server to generate a GIF image of your report, which we'll discuss in the next section. Setting this property so that the members always expand ensures that all information in the report is exposed when the image is written—which is important, considering that the user will get only a static image of the report and will not be able to expand members.
Using the PivotTable Component on a Web Server
Just as the other Office Web Components can be used without a user interface, you can create the PivotTable component as an in-memory object, connect to a data source, construct a report programmatically, and generate a GIF image of the current report or build your own HTML representation by walking the elements of the view. Using the PivotTable control on a web server might be a more compelling option than writing MDX queries, using ADO MD, and handcrafting a formatted result.
All the programmatic manipulation discussed so far can apply to using the PivotTable control on a server. The only new coding element you need to learn about is the ExportPicture method. This method is almost identical to the Chart component's ExportPicture method discussed in Chapter 3. To export the current report to a GIF image file, write the following code:
Set fsoTemp = CreateObject("Scripting.FileSystemObject")
strFilename = fsoTemp.GetTempName
PivotTable.ExportPicture Session("strTempFilePath") & strFilename, _
"gif", PivotTable.MaxHeight, PivotTable.MaxWidth
Response.Write "<IMG SRC='" & Session("strTempURLPath") & _
strFilename & "'>"
Session("Pivot" & Session("cntPivotImages")) = strFilename
Session("cntPivotImages") = Session("cntPivotImages") + 1
The key line in this code block is the call to the ExportPicture method. An instance of FileSystemObject (a class from the Microsoft Scripting Runtime library) retrieves a temporary filename for the new GIF image, and then the code hands that filename plus a root path to the ExportPicture method as the first parameter. As with the Chart component, the second parameter must be "gif". The last two parameters indicate the height and width, but unlike programming the Chart component (which can scale its content to any size), you must use the PivotTable component's MaxHeight and MaxWidth properties to avoid cropping the report image.
Generating a report image on the server creates the most extreme version of a locked-down report. Users can view such an image on any platform and in any browser that can display a GIF image, but the report will not be interactive.
