Creating a PivotTable

<%@ Language="VBScript" %>
<% Option Explicit %>
<!--METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
'--- Errors will be handled manually in the code
'--- For troubleshooting, comment out this line 
'--- to see the default ASP error output
On Error Resume Next

'--- Declarations
Dim XlwApp' As SAExcelApplication
Dim wsPivot' As SAWorksheet
Dim wsData' As SAWorksheet
Dim rngData' As SARange
Dim fldProduct' As IPivotField
Dim fldSales' As IPivotField
Dim fldMonth' As IPivotField
Dim pvtTable' As IPivotTable


'--- Create an instance of SoftArtisans ExcelWriter
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
CheckError Err, "SoftArtisans ExcelWriter could not be created."

'--- Open the workbook with the data we will make into a pivot table
XlwApp.Open("C:\reports\pivotdata.xls")
CheckError Err, "The ""pivotdata.xls"" data workbook could not be opened."

'--- Get a reference to the data sheet
Set wsData = XlwApp.Worksheets("DataSheet")

'--- Create a second worksheet and name it PivotSheet
Set wsPivot = XlwApp.Worksheets(2)
wsPivot.Name = "PivotSheet"

'--- The data in the workbook is in a named range called DataNamedRange
'--- Get a SARange object representing that range
Set rngData = XlwApp.NamedRange("DataNamedRange")
CheckError Err, "Could not find ""DataNamedRange"" named range."

'--- Every SAWorksheet has a PivotTables collection
'--- Pass a SARange object containing the data for
'--- the pivot table
'--- The range must be a contiguous region of data with
'--- no skipped columns.  The first row of the range must contain
'--- column headers.  All columns must begin on the same row.
Set pvtTable = wsPivot.PivotTables.CreatePivotTable(rngData, 2, 2)
CheckError Err, "Failed to create pivot table."

'--- Properties of IPivotTable
'--- Most properties of IPivotTable are listed here
'--- for demonstration purposes, though they don't all need
'--- to be set explicitly in your code
With pvtTable
  '--- True by default
  .AutoFormat = True
  .DataCollectionInRow = True
  .EnableDrillDown = True
  .EnableFieldDialog = True
  .EnableWizard = True
  .GrandTotalsForColumns = True
  .GrandTotalsForRows = True
  .PreserveFormatting = True
  .RepeatItemLabels = False
  .ShowValueForEmptyCells = True
    
  '--- False by default
  .ShowFriendlyErrorValue = False
  .InOutlineForm = False
  .MergeLabels = False ' Center label horizontally and vertically
  .SetPrintTitles = False
  .SubtotalHiddenPageItems = False
    
  .AutoFormatType = saxlAutoFormatReport1
  .Name = "Sales Report 2003" ' Programmatic name for the report
        
End With

'--- The Fields collection is populated automatically
'--- based on the range of data passed to the CretePivotTable method
Set fldProduct = pvtTable.Fields("Product")
Set fldSales = pvtTable.Fields("Sales")
Set fldMonth = pvtTable.Fields("Month")

'--- Month will be displayed on the left column (Y-axis)
fldMonth.Type = saxlPivotFieldRow
'--- Product name will be displayed across the top (X-axis)
fldProduct.Type = saxlPivotFieldColumn

'--- Sales will be displayed in the body of the table
fldSales.Type = saxlPivotFieldData
'--- Sum up all sales numbers for the table values
fldSales.SummarizeBy = saxlSummarizationSum

'--- Make the pivot table worksheet the active sheet
'--- when the workbook opens
wsPivot.SelectSheet

'--- Stream the workbook to the client
'--- Use saOpenInPlace or saDefault
'--- Pivot tables will not open properly with saOpenInExcel
XlwApp.Save "BasicPivotReport.xls", saOpenInPlace
CheckError Err, "An error occurred saving the workbook."

'--- Dereference ExcelWriter
Set XlwApp = Nothing
Response.End

'--- This subroutine is called after every major operation
'--- to see if an error has occurred.
'--- If an error has occurred, display a message and stop the script
Sub CheckError(ByRef ThisErr, ByRef msg)
  If ThisErr.number <> 0 Then
    Response.Clear
    Response.Write "<b>" & msg &"</b><br>" & _
        ThisErr.Description & " (" & ThisErr.Source & ")"
    Response.End
  End If
End Sub
%>