Five Tips for the Microsoft Office XP Web Components (April 2003)
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Frank C. Rice
Microsoft Corporation
April 2003
Applies to:
Microsoft® Office XP Web Components
Summary: Discover how you can retain the original format of the numbers in a PivotTable component, determine which version of the Office Web Components you have, and more. This article provides the sample code that you can use in you own applications which use the Office XP Web components. (12 printed pages)
Contents
Use OLAP Number Formats with the PivotTable Component
Determine Which Version of the Office Web Components You Have
Hiding Columns in Your Spreadsheet Component Before Printing
Create a PivotTable List Programmatically
Loop Through PivotTable Component Fields and FieldSets
Use OLAP Number Formats with the PivotTable Component
When using the PivotTable component in a Web page to display dimensions and measures from a cube created in Microsoft® SQL Server™ 2000 Analysis Services or Microsoft SQL Server OLAP Services, the number formats defined in the cube don't show up in the PivotTable component.
Important In order for the code in this article to work properly, you must set the security level for your application to Low. The code in this article is meant for demonstration purposes only. Microsoft highly recommends that you maintain the security level for your applications at High during normal use.
There are two ways to have the PivotTable component use the number formats of the Analysis Services or Online Analytical Processing (OLAP) Server. The first method requires setting the UseProviderFormatting property of the PivotTable component to TRUE.
Note The UseProviderFormatting property is a hidden member in the Microsoft Office XP Web Components object model. This member is not documented and, therefore, is not supported by Microsoft Technical Support.
The following code demonstrates how to use the UseProviderFormatting property:
<HTML>
<HEAD><meta name="Example 1" contents= "NOINDEX, NOFOLLOW">
<TITLE>OLAP Number Formats UseProviderFormatting Sample</TITLE>
<META NAME="GENERATOR" Content="Microsoft FrontPage 5.0">
<META HTTP-EQUIV="Content-Type" content="text/html; charset=UTF-8">
</HEAD>
<BODY bgcolor=lightskyblue text="#000000" link="#006600" vlink="#669966" alink="#990000">
Server Name: <input id=ServerName style="width:70%" value="localhost"></input><br>
Database Name: <input id=Catalog style="width:70%" value="Foodmart 2000"></input><br>
Cube Name: <input id=Cube style="width:70%" value="Sales"></input><p>
<button ONCLICK=Connect_OnClick() ID=Button1>Connect</button><p>
<OBJECT CLASSID="clsid:0002E552-0000-0000-C000-000000000046" id="MyPT" VIEWASTEXT></OBJECT>
<script language=VBScript>
function Connect_OnClick()
MyPT.ConnectionString = "Provider=MSOLAP.2;Data Source=" & _
ServerName.Value & ";Initial Catalog=" & Catalog.value
MyPT.DataMember = Cube.value
' Put measures on the view.
MyPT.CommandText = _
"select AddCalculatedMembers(Measures.members) " & _
"on columns " & "from [" & Cube.value & "]"
' View the returned data set in a browser with the following
' statement commented to see the formatting in the
' PivotTable control and then uncomment the statement
' and view the data.
' MyPT.ActiveView.UseProviderFormatting=true
end function
</script>
</BODY>
</HTML>
Insert the sample code into a Web page and open the page in a browser.
Change the name of the server, database or cube, as needed, and then click the Connect button.
Notice the format of the numbers in the return data set. The numbers are formatted as floating point numbers with several decimals.
.gif "Aa155751.odc_owctips01(en-us,office.10).gif")
Figure 1. PivotTable component with floating point number format
Next, remove the comment (apostrophe) from the statement
MyPT.ActiveView.UseProviderFormatting=true,open the page in a browser, and then click the Connect button.The numbers in the return data set are formatted as currency amounts or as standard numbers with two decimal digits, just as they are in the cube.
.gif "Aa155751.odc_owctips02(en-us,office.10).gif")
Figure 2. PivotTable component with the UseProviderFormatting property
The second way of using the OLAP number format uses ActiveX® Data Objects (Multidimensional) (ADO MD) to query against the cube to obtain the Format_String property of the measure. The Format_String property is then applied to the NumberFormat property of a cell in the PivotTable component. The following code illustrates setting the NumberFormat property by using the Format_String property returned from a query:
<HTML>
<HEAD><meta name="Example 2" contents= "NOINDEX, NOFOLLOW">
<TITLE>Set Number Formats from Cube Format_String</TITLE>
<META NAME="GENERATOR" Content="Microsoft Visual Studio">
<META HTTP-EQUIV="Content-Type" content="text/html; charset=UTF-8">
</HEAD>
<BODY bgcolor=lightskyblue text="#000000" link="#006600" vlink="#669966"
alink="#990000">
Server Name: <input id=ServerName style="WIDTH: 70%" value="localhost"></input><br>
Database Name: <input id=Catalog style="WIDTH: 70%" value="Foodmart 2000"></input><br>
Cube Name: <input id=Cube style="WIDTH: 70%" value="Sales"></input><p>
<button ONCLICK=Connect_OnClick() ID=Button1>Connect</button>
<button ONCLICK=Format_OnClick() ID=Button2>Set Formatting</button><p>
<OBJECT id=MyPT style="WIDTH: 502px; HEIGHT: 217px"
classid=clsid:0002E552-0000-0000-C000-000000000046 width=502 height=217
VIEWASTEXT>
<PARAM NAME="XMLData"
VALUE='<xml xmlns:x="urn:schemas-microsoft-com:office:excel">
<x:PivotTable> <x:OWCVersion>10.0.0.2621
</x:OWCVersion> <x:DisplayScreenTips/>
<x:CubeProvider>msolap.2</x:CubeProvider>
<x:CacheDetails/>
<x:PivotView> <x:IsNotFiltered/>
</x:PivotView> </x:PivotTable> </xml>'>
</OBJECT>
<script language=VBScript>
function Connect_OnClick()
MyPT.ConnectionString = "Provider=MSOLAP.2;Data Source=" & _
ServerName.Value & ";Initial Catalog=" & Catalog.value
MyPT.DataMember = Cube.value
' Put measures on the view.
MyPT.CommandText = _
"select AddCalculatedMembers(Measures.members) " & _
"on columns from [" & Cube.value & "]"
end function
function Format_OnClick()
GetFormatsFromCube MyPT
end function
sub GetFormatsFromCube(pt)
if pt.ConnectionString = "" or _
pt.DataMember = "" then
exit sub
end if
if pt.Connection is Nothing then
exit sub
end if
CubeName = pt.DataMember
if Left(CubeName,1) <> "[" then
CubeName = "[" & CubeName & "]"
end if
' We can't get cell properties using an ADO
' recordset so we use a ADOMD cellset.
set cs = createobject("ADOMD.Cellset")
set cs.ActiveConnection = pt.Connection
cs.Open _
"select " & _
"AddCalculatedMembers(Measures.members) " & _
"on columns " & _
"from " & CubeName & " " & _
"cell properties format_string "
for i = 0 to cs.axes(0).Positions.Count - 1
totalName = _
cs.axes(0).Positions(i).Members(0).UniqueName
' The script will throw an error if the Format_String
' property hasn't been set for a measure in the cube
' (such as in the case of the Margin measure) so we
' need to check for that.
if cs(i).Properties("FORMAT_STRING") <> "" then
pt.ActiveView.Totals(totalName).NumberFormat = _
cs(i).Properties("FORMAT_STRING")
end if
next
end sub
</script></p>
</BODY>
</HTML>
Insert the sample code into a Web page and open the page in a browser.
Change the name of the server, database or cube, as needed, and then click the Connect button.
As in the previous example, the format of the numbers in the return data set are formatted as floating point numbers with several decimals.
.gif "Aa155751.odc_owctips03(en-us,office.10).gif")
Figure 3. Unformatted PivotTable component
Next, click the Set Formatting button.
The numbers are now formatted as currency amounts or standard numbers with two decimal digits, just as they are in the cube.
.gif "Aa155751.odc_owctips04(en-us,office.10).gif")
Figure 4. PivotTable component formatted with the NumberFormat property
Before deciding whether to use format properties that are stored on the server, you should consider the following:
When you set UseProviderFormatting to True, the cell contents appear as text instead of appearing as numeric data. Therefore, by default, the cell contents are left-aligned. If the width of the column is too small for all of the contents of a cell, the cell displays text that is truncated, instead of displaying ########, as expected. This may create a false impression about the value in the cell. For example, the value 123456.78 might appear as either 1234 or 6.78, depending upon the text alignment and column width settings.
Determine Which Version of the Office Web Components You Have
There are times that you need the version of the Office Web Components you are using. For instance, you may want to know if you can take advantage of a feature or change in a newer version of the components. Or you may wonder if you need some bit of workaround code for an issue in a particular version. Luckily, there is a Version property that is available in the Office Web Component library. This property will provide a String that contains the entire version number. There are also properties available to retrieve parts of the version information as detailed in the following table:
| Property | Description |
|---|---|
| MajorVersion | A LongInteger value indicating the major version of the component. For the Office XP release of the Web components, this number is 10. |
| MinorVersion | A String value indicating the minor version of the component. For the Office XP release, this value is 0.0. This number is updated if there is a minor release before the next major release. The MinorVersion is a String value in the event that the minor version number is suffixed with a letter such as "1a". |
| BuildNumber | A String value indicating the build number of the component. The build number is incremented with every build of the component dynamic linked library (DLL). For the Office XP release, this value is 4510. The value may also contain a letter which explains the String data type. |
| Version | A String value that returns the entire version number of the component. For the Office XP release, this value is 10.0.0.4510 |
The following code can be used to display the version number of a Chart component in a message box. For example, you could add a CommandButton control to a UserForm, and insert this code in the OnClick event of the button:
...
Dim objOWC As ChartSpace
Set objOWC = New ChartSpace
MsgBox objOWC.Version
...
The following figure is an example of the message box returned for the a Chart component of the Office XP Web Component.
.gif "Aa155751.odc_owctips05(en-us,office.10).gif")
Figure 5. Message box display showing the Office XP Web Component version number
Hiding Columns in Your Spreadsheet Component Before Printing
If you use the Spreadsheet component to keep track of sensitive information such as employee salaries, sales data, or competitor information, there may be times when you need to hide selected columns before printing the data. For example, imagine that you are dashing out the door to meet with a client with a spreadsheet containing proposed service quotes and you realize that the sheet also contains competitor data which you were using for comparison analysis. Normally, you'd have to stop, open the application hosting the spreadsheet, manually hide each column, and then reprint the spreadsheet. Or, you could set up the spreadsheet in advance using the code shown below to hide those columns automatically before printing.
The code shown below comes from an Access form containing a spreadsheet and two command buttons. However the code can be used in any Office application using Microsoft Visual Basic® for Applications (VBA). The first statement in the sample is used to set up the Spreadsheet component to react to events. This is handy as you work with the control in your code but isn't required for the button code to work. The form's OnLoad event procedure is then used the set the owcSS variable to refer to the Object property of the Spreadsheet component, just as you have to do for other ActiveX® controls. The code for the command button CmdBtn1 is used to hide the columns, print the form, and then redisplay the columns. The other button code is used to print the spreadsheet with all of the data displayed.
Private WithEvents owcSS As OWC10.Spreadsheet
Private Sub Form_Load()
Set owcSS = Me.Spreadsheet0.Object
End Sub
Private Sub CmdBtn1_Click()
owcSS.Columns(2).Hidden = True
owcSS.Columns(5).Hidden = True
DoCmd.PrintOut acPrintAll
owcSS.Columns(2).Hidden = False
owcSS.Columns(5).Hidden = False
End Sub
Private Sub CmdBtn2_Click()
DoCmd.PrintOut acPrintAll
End Sub
Create a PivotTable List Programmatically
There is a lot of information and several articles floating around about the Office XP Web Components. There is also other help available for the Web components on the newsgroups and in the Office XP Web Component Toolpack. With all of this information and help available, wouldn't it be nice to be able to quickly create a populated PivotTable component so that you could try out that new method or property before spending a lot of time adapting it to your own application? With the following code you can create a PivotTable List programmatically in Microsoft Access. With a few minor modifications, you should be able to adapt the code to your Office application:
...
Dim frm As Access.Form
Dim pTable As OWC10.PivotTable
Dim pFieldset As OWC10.PivotFieldSet
Dim pField As OWC10.PivotField
Dim pTotal As OWC10.PivotTotal
Dim strExpression As String
' Open the form in PivotTable view.
DoCmd.OpenForm "frmPivotTable", acFormPivotTable
Set frm = Forms("frmPivotTable")
Set pTable = frm.PivotTable
' Add the LastName FieldSet to the Column drop zone.
Set pFieldset = pTable.ActiveView.FieldSets("LastName")
pTable.ActiveView.ColumnAxis.InsertFieldSet pFieldset
' Reference the OrderDate by Month Fieldset.
Set pFieldset = pTable.ActiveView.FieldSets("OrderDate By Month")
' Exclude every field in the Fieldset, except for
' Years and Quarters fields.
For Each pField In pFieldset.Fields
pField.IsIncluded = False
Next
pFieldset.Fields("Years").IsIncluded = True
pFieldset.Fields("Quarters").IsIncluded = True
' Add the OrderDate by Month (Years/Quarters) Fieldset to the
' Row drop zone.
pTable.ActiveView.RowAxis.InsertFieldSet pFieldset
' Add the ShipCountry Fieldset to the Filter drop zone.
Set pFieldset = pTable.ActiveView.FieldSets("ShipCountry")
pTable.ActiveView.FilterAxis.InsertFieldSet pFieldset
' Create a new, empty Fieldset named Sales and
' show it in the Field List.
Set pFieldset = pTable.ActiveView.AddFieldSet("Sales")
pFieldset.DisplayInFieldList = True
' Create a new calculated field within the FieldSet, using
' the expression below:
strExpression = "([UnitPrice]*[Quantity]*(1-[Discount])/100)*100"
Set pField = pFieldset.AddCalculatedField("Sales", _
"Sales", "Sales", strExpression)
' Format the field as Currency.
pField.NumberFormat = "Currency"
' Insert the new FieldSet into the Data drop zone
' to show the calculation for every Order (detail row).
pTable.ActiveView.DataAxis.InsertFieldSet pFieldset
' Create a new Total and add it to the Totals collection
' Base the Total on the Sum of the calculated field you created
' previously.
Set pTotal = pTable.ActiveView.AddTotal("Sales Totals", pField, plFunctionSum)
pTable.ActiveView.DataAxis.InsertTotal pTotal
' Collapse the PivotTable so that summary data is shown by default.
pTable.ActiveData.HideDetails
frm.SetFocus
Set pTotal = Nothing
Set pField = Nothing
Set pFieldset = Nothing
Set pTable = Nothing
Set frm = Nothing
...
Loop Through PivotTable Component Fields and FieldSets
If you've looked through the object model of the Office XP Web components, maybe by using the Object Browser in the Visual Basic Editor, you may have noticed that with the number of objects in the hierarchy, it is important to have a way to group similar objects together. Sometimes this is done by using collections such as with the PivotFields collection that holds PivotField objects. In other instances, properties are used to group similar objects as in the case of Field objects that are contained in the Fields property. Similarly, it is a common task to loop through the objects in a collection or property. The following code demonstrates how to do this by looping through the Field and FieldSet objects in a PivotTable component that resides on an Access form. You can modify this code with a few minor adjustments to run in your own application. The way the code accesses and loops is the same:
...
Dim frm As Access.Form
Dim pTable As OWC10.PivotTable
Dim pFieldset As OWC10.PivotFieldSet
Dim pField As OWC10.PivotField
Dim cntFldSets As Long
Dim cntTemp As Long
Dim cntFields As Long
Dim cntFldTemp As Long
Set frm = Forms("frmPivotTable")
Set pTable = frm.PivotTable
cntFldSets = pTable.ActiveView.FieldSets.Count
cntTemp = cntFldSets
For Each pFieldset In pTable.ActiveView.FieldSets
cntFields = pFieldset.Fields.Count
cntFldTemp = cntFields
For Each pField In pFieldset.Fields
MsgBox pFieldset.Name & " : " & pField.Name & vbCrLf & _
cntTemp & " fieldsets of " & cntFldSets & vbCrLf & _
cntFldTemp & " fields of " & cntFields, vbOKOnly, "PivotTable Field and FieldSets"
cntFldTemp = cntFldTemp - 1
Next
cntTemp = cntTemp - 1
Next
...