Microsoft KB Archive/317630

= INFO: Hyperlink Support in the Office XP PivotTable Component =

Article ID: 317630

Article Last Modified on 8/6/2002

-

APPLIES TO


 * Microsoft Office 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:  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).  Paste the following code in Getempinfo.asp: <% Language = &quot;VBScript&quot; %>   You requested information for Employee # <%=Request.QueryString(&quot;EID&quot;)%>.     Create an HTML file named Pivot.htm that contains the following code:  

<Script Language=&quot;VBScript&quot;>  ******************************************  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 = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program&quot; _ & &quot; Files\Microsoft Office\Office10\Samples\Northwind.mdb; &quot; szDataMember = &quot;Employees&quot;

PivotTable1.ConnectionString = szConnStr PivotTable1.DataMember = szDataMember

End Sub  ******************************************  ****************************************** Sub CreateHyperlinkField Const c_szEmpIDName = &quot;EmployeeID&quot; 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 = &quot;[&quot; & objPivotFieldSetOriginal.Fields(0).Name & &quot;]&quot; szExpression = szTmp & &quot; & &quot;&quot;#http://YourServer/GetEmpInfo.asp?EID=&quot;&quot; & &quot; & szTmp

''' Add the new FieldSet object and field. Set objPivotFieldSet = objView.AddFieldSet(&quot;Employees2&quot;) Set objPivotField = objPivotFieldSet.AddCalculatedField(&quot;Employees2&quot;, &quot;Employees2&quot;, _                       &quot;Employees2&quot;, 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(&quot;Employees2&quot;) End Sub </Script> </li> In the CreateHyperLink routine of Pivot.htm, change  to the name of your Web server.</li> Start Internet Explorer and then open the Pivot.htm file. The Web page contains a PivotTable that contains EmployeeID fields that are hyperlinks.</li> Click any of the EmployeeID hyperlinks to display the results of Getempid.asp.</li></ol>

Keywords: kbinfo kbofficewebpivot KB317630

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.