Export (0) Print
Expand All

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>

  1. Insert the sample code into a Web page and open the page in a browser.
  2. 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.

    Aa155751.odc_owctips01(en-us,office.10).gif

    Figure 1. PivotTable component with floating point number format

  3. 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.

    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">&#13;&#10;
<x:PivotTable>&#13;&#10;  <x:OWCVersion>10.0.0.2621
</x:OWCVersion>&#13;&#10;  <x:DisplayScreenTips/>&#13;&#10;  
<x:CubeProvider>msolap.2</x:CubeProvider>&#13;&#10;  
<x:CacheDetails/>&#13;&#10;
<x:PivotView>&#13;&#10;   <x:IsNotFiltered/>&#13;&#10; 
</x:PivotView>&#13;&#10; </x:PivotTable>&#13;&#10;</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>

  1. Insert the sample code into a Web page and open the page in a browser.
  2. 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.

    Aa155751.odc_owctips03(en-us,office.10).gif

    Figure 3. Unformatted PivotTable component

  3. 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.

    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:

PropertyDescription
MajorVersionA Long Integer value indicating the major version of the component. For the Office XP release of the Web components, this number is 10.
MinorVersionA 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".
BuildNumberA 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.
VersionA 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.

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
...

Show:
© 2014 Microsoft