📄 08.txt
字号:
8-1
（2）编写数据透视表和数据来源连接的程序代码。
Sub PivotVideoCube()
dim strConnect
strConnect = "Data Source=Local;Provider=MSOLAP;Initial Catalog=图书销售系统DSS"
Document.PivotTable1.ConnectionString = strConnect
Document.PivotTable1.DataMember = "图书销售分析"
Document.PivotTable1.DisplayFieldList = True
Set view = Document.PivotTable1.ActiveView
view.ColumnAxis.DisplayEmptyMembers = 1
view.RowAxis.DisplayEmptyMembers = 1
End Sub
（3）设置OWC开启时的外观。
Sub FormatPivotList()
Set view = Document.PivotTable1.ActiveView
Document.PivotTable1.BackColor = "CornSilk"
view.FilterAxis.Label.BackColor = "CornSilk"
view.RowAxis.Label.BackColor = "CornSilk"
view.ColumnAxis.Label.BackColor = "CornSilk"
view.DataAxis.Label.BackColor = "CornSilk"
view.FilterAxis.Label.Font.Color = "#B5A689"
view.RowAxis.Label.Font.Color = "#B5A689"
view.ColumnAxis.Label.Font.Color = "#B5A689"
view.DataAxis.Label.Font.Color = "#B5A689"
view.Label.BackColor = "#485EB4"
view.Label.Caption = "图书销售分析"
view.Label.Font.Size = 16
view.Label.ForeColor = "White"
view.TotalFont.Bold = 0
view.TotalBackColor = "CornSilk"
view.MemberForeColor = "Black"
view.FieldLabelForeColor = "Black"
For Each fieldset In view.Fieldsets
For Each field in fieldset.fields
field.SubtotalBackColor = "Wheat"
Next
Next
For Each total in view.Totals
total.HAlignment = 3
total.CaptionHAlignment = 3
Select Case total.Caption
Case "Price", "Total Amount"
total.NumberFormat = "$#,###"
Case "Quantity"
total.NumberFormat = "#,###"
End Select
Next
End Sub
（4）设置数据透视表的高度及宽度。
Sub ConfigurePivotList()
Document.PivotTable1.MaxWidth = 20000
Document.PivotTable1.MaxHeight = 20000
End Sub
（5）设置图表的数据来源。
Sub ConfigureChart
set document.webchart.datasource = document.PivotTable1
document.webchart.ScreenUpdating = 0
document.webchart.haschartspacelegend = true
document.webchart.charts.add
set ch = document.webchart.charts(0)
ch.setdata 0, 0, -2
ch.setdata 1, 0, -1
ch.setdata 2, 0, 0
End Sub
（6）设定图表的外观。
Sub FormatChart
Document.WebChart.Interior.Color = "Wheat"
Document.WebChart.Charts(0).Interior.Color = "Wheat"
Document.WebChart.Charts(0).PlotArea.Interior.Color = "CornSilk"
Document.WebChart.Border.Color = "#B5A689"
End Sub
（7）将数据透视表显示在网页上。
Sub ShowPivotList
btnSeriesByColumn.disabled = true
btnSeriesByRow.disabled = true
btnShowPivotList.disabled = true
btnClear.disabled = false
btnShowWebChart.disabled = false
Document.WebChart.Style.Display = "none"
Document.WebChart.ScreenUpdating = 0
Document.PivotTable1.Style.Display = ""
End Sub
（8）将图表显示在网页上
Sub ShowWebChart
btnSeriesByColumn.disabled = false
btnSeriesByRow.disabled = false
btnShowPivotList.disabled = false
btnClear.disabled = true
btnShowWebChart.disabled = true
Document.PivotTable1.Style.Display = "none"
Document.WebChart.ScreenUpdating = 1
Document.WebChart.Style.Display = ""
End Sub
（9）在图表上按行来分析。
sub SeriesByRows
set ch = document.webchart.charts(0)
ch.setdata 0, 0, -2
ch.setdata 1, 0, -1
ch.setdata 2, 0, 0
end sub
（10）在图表上按列来分析。
sub SeriesByColumns
set ch = document.webchart.charts(0)
ch.setdata 0, 0, -1
ch.setdata 1, 0, -2
ch.setdata 2, 0, 0
end sub
（11）清除数据透视表中的所有内容将其还原。
Sub ClearCube
Document.PivotTable1.ActiveView.AutoLayout
End Sub
（12）在网页中加入5个按钮，分别调用相应的子程序。
<p align="center">
<input TYPE="button" id="btnClear" ONCLICK="ClearCube()" VALUE="清除">
<input TYPE="button" id="btnSeriesByColumn" ONCLICK="SeriesByColumns()" VALUE="按列分析">
<input TYPE="button" id="btnSeriesByRow" ONCLICK="SeriesByRows()" VALUE="按行分析">
<input TYPE="button" id="btnShowPivotList" ONCLICK="ShowPivotList()" VALUE="显示数据透视列表">
<input TYPE="button" id="btnShowWebChart" ONCLICK="ShowWebChart()" VALUE="显示图表">
</p>
（13）最后控制所有程序的执行顺序，这段代码应该放置在数据透视表的上方。
<script for="window" event="onload" language="VBScript">
PivotVideoCube
ConfigurePivotList
FormatPivotList
ConfigureChart
FormatChart
ShowPivotList
</script> <script language="VBScript">
8-2
（1）用下面的代码可以返回一个随机产生的临时文件：
<%
Set FileO = server.CreateObject("Scripting.FileSystemObject")
TempName = FileO.GetTempName & ".gif"
%>
（2）连接到数据源及执行MDX查询。
<%
Dim sMDX
Dim conMD
Dim CellSet
Dim Columns
set conMD = Server.CreateObject ("ADODB.Connection")
set CellSet = Server.CreateObject ("ADOMD.Cellset")
' 连接到数据来源，这里读者应该能自行写出连接字串了，如果还不清楚，可以返回本书前面的章节复习一下。
If len(Request("Columns")) = 0 then
OnColumns="图书类别"
Else
OnColumns = Request("Columns")
End if
If len(Request("Rows")) = 0 then
OnRows="书店所在地"
Else
OnRows = Request("Rows")
End if
If len(Request("Measures")) = 0 then
OnMeasures="销售额"
Else
OnMeasures = Request("Measures")
End if
sMDX = "SELECT [" + OnColumns + "].CHILDREN ON COLUMNS, " sMDX = sMDX + "[" + OnRows + "].CHILDREN ON ROWS "
sMDX = sMDX + "FROM " + Session("CubeName")
sMDX = sMDX + " WHERE (MEASURES.[" + OnMeasures + "],"
if Request("性别") = "男" then
sMDX = sMDX + "[性别].[男])"
ELSEIF Request("性别") = "女" then
sMDX = sMDX + "[性别].[女])"
ELSE
sMDX = sMDX + "[性别].[All 性别])"
END IF
' 依照用户所选的dimension及measure执行MDX语法到数据来源查询
' 图书销售分析的内容
CellSet.Open sMDX, conMD
' X坐标有多少字段
columns= CellSet.Axes(0).Positions.Count
' Y坐标有多少字段
Rows= CellSet.Axes(1).Positions.Count
%>
（3）将查询的结果显示在网页上。
' 显示X坐标标题
<%= CellSet.Axes(0).Positions(column).Members.Item(0).Caption%>
' 显示Y坐标标题
<%= CellSet.Axes(1).Positions(row).Members.Item(0).Caption%>
（4）显示图表
<%
set chartspace1 = createobject("owc.chart")
set c = chartspace1.constants
chartspace1.border.color = c.chcolornone
' 新增一个图表
chartspace1.charts.add
' 图表的类型
IF Barchecked = "checked" Then
chartspace1.charts(0).type = c.chcharttypecolumnclustered
ELSE
chartspace1.charts(0).type = c.chcharttypepie
End if
row=0
column=0
Do While Column < Columns
chartspace1.charts(0).seriescollection.add
chartspace1.charts(0).seriescollection(Column).caption =
CellSet.Axes(0).Positions(column).Members.Item(0).Caption
chartspace1.charts(0).seriescollection(Column).setdata c.chdimcategories,
c.chdataliteral, rgCategories
Do While Row < Rows
rgValues(Row)= CellSetItem(column,row) Row=Row+1
Loop
chartspace1.charts(0).seriescollection(Column).setdata c.chdimvalues, c.chdataliteral,rgValues
Column=Column+1 Row=0
Loop
' 是否要出现长条图说明，true表示要出现
chartspace1.charts(0).haslegend = true
chartspace1.exportpicture Session("ChartPath") + tempname, "gif", 640, 420 cellset.Close
conMD.Close
%>
8-3
接，以传递下面几个参数：
ColumnASP：
ColumnExtASP：
RowASP：
RowExtASP：
MeasureASP：
ColumnDrillDownASP：将X坐标展开
RowDrillDownASP：将Y坐标展开
ColumnLevelsDrillDownASP：
RowLevelsDrillDownASP：
（3）用户单击展开所要执行的MDX语句。
' 如果按下X轴的细项展开后，令ColumnDrillDown的值为Yes
If len(Request("ColumnDrillDownASP")) = 0 then
ColumnDrillDown="No"
Else
ColumnDrillDown=Request("ColumnDrillDownASP")
End if
' 如果按下Y轴的细项展开后，令RowDrillDown的值为Yes
If len(Request("RowDrillDownASP")) = 0 then
RowDrillDown="No"
Else
RowDrillDown=Request("RowDrillDownASP")
End if
' **************************************************************
' 按照所选取的坐标轴的细目展开，指定其所要执行的MDX语句
' **************************************************************
sMDX=null
' X或Y轴都没有按细目展开时所执行的MDX语法
If ((ColumnDrillDown = "No") and (RowDrillDown = "No")) then
sMDX = "SELECT {[" + OnColumns + "].CHILDREN, [All " + OnColumns
+ "]} ON COLUMNS, "
sMDX = sMDX + "{[" + OnRows + "].CHILDREN, [All " + OnRows + "]}
ON ROWS " sMDX = sMDX + "FROM " + Session("CubeName")
sMDX = sMDX + " WHERE (MEASURES.[" + OnMeasures + "],"
' X及Y轴都细目展开时所执行的MDX语法
elseif ((ColumnDrillDown = "Yes") and (RowDrillDown = "Yes")) then
OnColumns=request("ColumnASP")
OnRows=request("RowASP")
OnColumnsExt=request("ColumnExtASP")
OnRowsExt=request("RowExtASP")
OnMeasures=request("MeasureASP")
sMDX = "SELECT {[" + OnColumns + "].[" + OnColumnsExt +
"].CHILDREN, [All " + OnColumns +"]} ON COLUMNS, "
sMDX = sMDX + "{[" + OnRows + "].[" + OnRowsExt + "].CHILDREN,
[All " + OnRows +"]} ON ROWS "
sMDX = sMDX + "FROM " + Session("CubeName")
sMDX = sMDX + " WHERE (MEASURES.[" + OnMeasures + "],"
' X轴细目展开，Y轴都没有细目展开时所执行的MDX语法
elseif ((ColumnDrillDown = "Yes") and (RowDrillDown = "No")) then
OnColumns=request("ColumnASP")
OnRows=request("RowASP")
OnColumnsExt=request("ColumnExtASP")
OnRowsExt=request("RowExtASP")
OnMeasures=request("MeasureASP")
sMDX = "SELECT {[" + OnColumns + "].[" + OnColumnsExt +
"].CHILDREN, [All " + OnColumns +"]} ON COLUMNS, "
sMDX = sMDX + "{[" + OnRows + "].CHILDREN, [All " + OnRows + "]}
ON ROWS "
sMDX = sMDX + "FROM " + Session("CubeName")
sMDX = sMDX + " WHERE (MEASURES.[" + OnMeasures + "],"
' Y轴细目展开，X轴都没有细目展开时所执行的MDX语法
else
OnColumns=request("ColumnASP")
OnRows=request("RowASP")
OnColumnsExt=request("ColumnExtASP")
OnRowsExt=request("RowExtASP")
OnMeasures=request("MeasureASP")
sMDX = "SELECT {[" + OnColumns + "].CHILDREN, [All " + OnColumns
+ "]} ON COLUMNS, "
sMDX = sMDX + "{[" + OnRows + "].[" + OnRowsExt + "].CHILDREN,
[All " + OnRows + "]} ON ROWS "
sMDX = sMDX + "FROM " + Session("CubeName")
sMDX = sMDX + " WHERE (MEASURES.[" + OnMeasures + "],"
end if
' MDX语法的条件
if Request("性别") = "男" then
sMDX = sMDX + "[性别].[男])"
ELSEIF Request("性别") = "女" then
sMDX = sMDX + "[性别].[女])"
ELSEIF Request("性别") = "NA" then
sMDX = sMDX + "[性别].[NA])"
ELSE
sMDX = sMDX + "[性别].[All 性别])"
END IF
（4）连接到多维数据集数据源并计算出每一轴线总共有多少Position。
' 连接到Cube数据来源，执行MDX语法查询
conMD.Open Session("MDConnectionString")
CellSet.Open sMDX, conMD
set Cat.ActiveConnection = conMD
' X轴的Position数目
columns= CellSet.Axes(0).Positions.Count
' Y轴的Position数目
Rows= CellSet.Axes(1).Positions.Count
（5）多维数据集的层次数目。
If ColumnDrillDown <> "Yes" and RowDrillDown <> "Yes" then
for i = 0 to Cat.CubeDefs.Item(0).Dimensions.Count -1
if Cat.CubeDefs.Item(0).Dimensions.Item(i).Name = OnColumns then
ColumnLevelsDrillDown=Cat.CubeDefs.Item(0).Dimensions.Item(i).
Hierarchies.Item(0).Levels.Count
end if
if Cat.CubeDefs.Item(0).Dimensions.Item(i).Name = OnRows then
RowLevelsDrillDown=Cat.CubeDefs.Item(0).Dimensions.Item(i).
Hierarchies.Item(0).Levels.Count
end if
next
else
ColumnLevelsDrillDown=request("ColumnLevelsDrillDownASP")
RowLevelsDrillDown=request("RowLevelsDrillDownASP")
end if
（6）指定cellset中各项目的值。
Column=0
Row=0
Do While Column < Columns
Do While Row < Rows
if isnull(cellset.Item(Column,row)) then
CellsetItem(Column,Row)=0
elseif OnMeasures <> "百分比" then
CellsetItem(Column,Row)=formatnumber(CellSet.Item(Column,row),0)
else
CellsetItem(Column,Row)=CellSet.Item(Column,row)
end if
Row=Row+1
LOOP
Column=Column+1
Row=0
LOOP
（7）以下程序绘出表格，由于程序太长，我们以重点解释。
' 将X及Y轴各member的名称列出
CellSet.Axes(0).Positions(column).Members.Item(0).Caption
CellSet.Axes(1).Positions(row).Members.Item(0).Caption
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -