Transcript Analysis And Reporting With Office Web Components

Analysis And Reporting
With Office Web
Components
Mike Ammerlaan
Program Manager
Excel/Components
Microsoft Corporation
4-302
Overview


Components overview
Basics:




Chart and PivotTable® data binding
Chart component basics
PivotTable® component basics
Solutions:



Custom reporting using PivotTable®
PivotCharts
Drill through with the PivotTable®
Food For Thought:
A Timesheet Weblication
Web Components Overview


Chart and PivotTable are
COM components
Use them in:

Internet Explorer 4.01 or higher




Edit in FrontPage®, Visual InterDev®, Access
Data Pages
Visual Basic® forms, VBE forms
Other COM control containers
UI-less
Connecting The
Components To Data
Connecting To Data

Passing A Data Object



Build the data objects using script
(e.g., an ADO Recordset)
Pass the data objects to the control
Setting Connection Properties Directly

ConnectionString, CommandText, Data
Member properties
Do It Yourself Data Objects

PivotTable and Chart can connect to any Data
Source Object

PivotTable:
set ptable.DataSource = dsc
ptable.DataMember = name

Chart:
set cds = ChartSpace.ChartDataSources.Add()
set cds.DataSource = dsc
cds.DataMember = Name

PivotTable is a DSO, thus you can
bind Chart to it
Connecting Directly


PivotTable natively supports
CommandText and ConnectionString
for connecting directly to data
PivotTable connects to OLAP data via
the ConnectionString and DataMember
properties


ptable.ConnectionString =
“<Conn String>”
ptable.DataMember = “<Cube Name>”
Chart Specifics
Charting Terminology
ChartSpace
ChartSpace
Title
Chart.Title
Chart
Chart.Series
Chart.Series.
Point
Legend
Chart Object Model Map
ChartSpace
Charts(…)
SeriesCollection(…)
Axes (…)
Points(…)
Legend
Trendlines(…)
PlotArea
ErrorBarsCollection(…)
Title
DataLabelsCollection(…)
Border
PivotTable Specifics
PivotTable Terminology
Tool Bar
Filter
Axis
Total
Column
Axis
Fieldset
Row
Axis
Field
Data
Axis
Member
PivotTable.Selection
(PivotRange)
Title Bar
Aggregate
PivotTable Object Model
PivotTable
ActiveView
Fieldsets(…)
FieldLabel*,
Member*, Total*
Column-, Data-,
Row-, Filter- Axis
Fieldsets(…)
Label
ActiveData
Column-, RowMembers(…)
Cells(…)
* = ForeColor, Font,
BackColor, etc.
Solutions
PivotTable Reports With
Predefined Views
Scenario: Web page for employees to view
popular report types


PivotTable component in Web page,
with HTML user interface
View configuration is done with:
 InsertFieldSet on Axis objects
 Manipulating field objects on
Axis.Fieldsets(…)
PivotCharts
Scenario: Adding charts as views of a
PivotTable




Add a chart object to the page
Bind it to the PivotTable
Add a data series using SetData
Add some logic for showing an
appropriate chart
Supporting Drill Through In
The PivotTable

Catch DblClick Event




Use PivotTable.Selection property to
determine selection
Get Row, Column and Filter Members
Build SQL WHERE clause specifying
member names
Set CommandText of new PivotTable
More Information

Office Web sites




http://support.microsoft.com/support/
OfficeDev/webcomp.asp
http://www.microsoft.com/officedev
Search for msowcvba.chm on your drive
for an API reference
Programming Microsoft
Office 2000 Web Components
by Dave Stearns,
Microsoft Press
Microsoft Script Editor

I recommend Microsoft Script Editor
for editing your scripts:




Based on Visual Interdev, but without a
lot of the unneeded complexity
Comes with Office 2000
To use it, make sure it is installed by using
the Office setup app (it is not installed
by default)
Search for MSE.EXE on your machine
PivotTable Issues

Visual totals



If you exclude Seattle, does WA remain the same
or does it = WA – Seattle?
ActiveView.TotalAllMembers configures this
SR1 PivotTable: PivotTable.Connection
property



Exposes current OLAP session
Execute CREATE MEMBER statements to create
calculated members
Call PivotTable.Refresh and get new ActiveData
object to pick up new totals
Deployment

No technological tie to Office 2000



However, install requires a
MS Office 2000 license per desktop
Auto-install with Microsoft Installer



Can deploy to the without also deploying
Office 2000
Use the CODEBASE attribute, must be a
file:\\ path (I.e. Intranet only)
CAB is 1.6 Mb
Requires MDAC 2.1 for data-access
Common UI Elements

AutoFit



Hide/show UI elements



Grows and shrinks with data
Good for Internet Explorer, bad for forms
Display* properties control visibility
Hide/Show Titlebar, Toolbar, FieldList,
Property Toolbox, Expand indicators,
Drop zones
Titlebar

ActiveView.Titlebar.Caption = “Hello”