INFO: Hyperlink Support in the Office XP PivotTable Component (317630)


The information in this article applies to:

  • Microsoft Office XP Web Components
This article was previously published under Q317630

SUMMARY

This article describes the Office XP Web Components PivotTable List hyperlink implementation and provides an example of how to use dynamic hyperlink addresses in the Office XP Web Components PivotTable list.

MORE INFORMATION

The PivotTable list (PivotTable) uses the generic Hyperlink object. You cannot modify the Hyperlink object properties such as the Address property. If you want to change the data in your PivotTable, you must modify the source data. However, you can use a calculated field, along with the Microsoft Access hyperlink notation that is supported by the PivotTable, to dynamically update the hyperlinks that are in your PivotTable list.

For example, the Microsoft Access sample Northwind database contains an Employees table with an EmployeeID field. The EmployeeID field is a numeric field that is automatically populated as new employees are added to the database. If you have a corresponding Web site in which you can retrieve employee information by EmployeeID (such as http://Northwind/Employees/1.htm or http://Northwind/EmployeeData.asp?EmployeeID=1), you can create a calculated field in your PivotTable that uses the Access hyperlink notation to link your PivotTable directly to your employee information Web page.

Sample

To create a calculated field that uses the Access hyperlink notation, follow these steps:
  1. Use any text editor or Hypertext Markup Language (HTML) editor to create a file named Getempinfo.asp in the root folder of your Web server (such as C:\Inetpub\Wwwroot).
  2. Paste the following code in Getempinfo.asp:
    <% Language = "VBScript" %>
<HTML>
<BODY>
<P>You requested information for Employee # <%=Request.QueryString("EID")%>.
</BODY>
</HTML>
  3. Create an HTML file named Pivot.htm that contains the following code:
    <html>
<body onload="InitializePivotTable()">
<p>
<object classid="clsid:0002E552-0000-0000-C000-000000000046" id="PivotTable1"></object>
</p>

<Script Language="VBScript">
''' ******************************************
''' This procedure initializes the PivotTable so that
''' you can view the PivotTable on the Web page.
''' ******************************************
Sub InitializePivotTable()
    ConnectPivotTable
    CreateHyperlinkField
    BuildPivotTable
End Sub
''' ******************************************
''' Connect the PivotTable to the Employees table
''' in the Microsoft Access Northwind sample database.
''' ******************************************
Sub ConnectPivotTable()
    Dim szConnStr
    Dim szDataMember

    ''' This assumes the default install location for Northwind.mdb.
    ''' Change the path as required for your Northwind.mdb.
    szConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program" _
            & " Files\Microsoft Office\Office10\Samples\Northwind.mdb; "
    szDataMember = "Employees"

    PivotTable1.ConnectionString = szConnStr
    PivotTable1.DataMember = szDataMember

End Sub
''' ******************************************
''' ******************************************
Sub CreateHyperlinkField()
    Const c_szEmpIDName = "EmployeeID"
    Dim szTmp
    Dim szExpression
    Dim objPivotFieldSetOriginal
    Dim objPivotFieldSet
    Dim objPivotField
    Dim objView

    ''' Initialize the variables.
    Set objView = PivotTable1.ActiveView
    Set objPivotFieldSetOriginal = objView.FieldSets(c_szEmpIDName)

    ''' Build the desired expression for the calculated field.
    ''' displaytext#addresss#subaddress#screentip <<only using first two in this sample
    szTmp = "[" & objPivotFieldSetOriginal.Fields(0).Name & "]"
    szExpression = szTmp & " & ""#http://YourServer/GetEmpInfo.asp?EID="" & " & szTmp 

    ''' Add the new FieldSet object and field.
    Set objPivotFieldSet = objView.AddFieldSet("Employees2")
    Set objPivotField = objPivotFieldSet.AddCalculatedField("Employees2", "Employees2", _
                        "Employees2", szExpression)
    objPivotField.IsHyperlink = True

    ''' Set the field captions.
    objPivotFieldSet.Caption = objPivotFieldSetOriginal.Caption
    objPivotField.Caption = objPivotFieldSetOriginal.Fields(0).Caption

    ''' Hide the original FieldSet object in the FieldList object.
    objPivotFieldSetOriginal.DisplayInFieldList = False

End Sub
''' ******************************************
''' BuildPivotTable can be as complex as you need to create
''' the view that you want. The example is a simple layout.
''' ******************************************
Sub BuildPivotTable()
    PivotTable1.ActiveView.RowAxis.InsertFieldSet _
                    PivotTable1.ActiveView.FieldSets("Employees2")
End Sub 
</Script>
</body>
</html>
  4. In the CreateHyperLink routine of Pivot.htm, change YourServer to the name of your Web server.
  5. Start Internet Explorer and then open the Pivot.htm file. The Web page contains a PivotTable that contains EmployeeID fields that are hyperlinks.
  6. Click any of the EmployeeID hyperlinks to display the results of Getempid.asp.
Modification Type:MajorLast Reviewed:8/6/2002
Keywords:kbinfo kbOfficeWebPivot KB317630
©2002 Microsoft Corporation. All rights reserved.