[Customers].[All Customers].CHILDREN
[Education Level].[All Education Level].CHILDREN
[Gender].[All Gender].CHILDREN
[Marital Status].[All Marital Status].CHILDREN
[Measures].[MeasuresLevel].MEMBERS
[Measures].[Profit]
[Measures].[Sales Average]
[Measures].[Unit Sales]
[Measures].[Store Cost]
[Measures].[Store Sales]
[Measures].[Sales Count]
[Measures].[Store Sales Net]
[Product].[All Products].CHILDREN
[Promotion Media].[All Media].CHILDREN
[Promotions].[All Promotions].CHILDREN
[Store].[All Stores].CHILDREN
[Store Size in SQFT].[All].CHILDREN
[Store Type].[All].CHILDREN
[Time].[Year].MEMBERS
[Yearly Income].[All Yearly Income].CHILDREN
Figure 3
Dimensions and Measures OPTION List
Dim vbCrLf: vbCrLf = Chr(13) + Chr(10)
Dim conFoodMart, catFoodMart, cubFoodMart
Dim strMeasures, strDimensions
Set conFoodMart = Server.CreateObject("ADODB.Connection")
conFoodMart.Open "Data Source=MyServer;Provider=MSOLAP;"
conFoodMart.DefaultDatabase="FoodMart"
' Access the FoodMart cube catalog
Set catFoodMart = Server.CreateObject("ADOMD.Catalog")
Set catFoodMart.ActiveConnection = conFoodMart
' Obtain a reference to the Sales Cube
Set cubFoodMart = catFoodMart.CubeDefs("Sales")
Dim dimCube, meaCube, strValue
strMeasures = "": strDimensions = ""
For Each dimCube In cubFoodMart.Dimensions
If dimCube.Name = "Measures" Then
' For the measures dimension construct strMeasures also
For Each meaCube In dimCube.Hierarchies(0).Levels(0).Members
strValue = meaCube.UniqueName
strMeasures = strMeasures & "<OPTION value = '" & strValue & "'>"
& meaCube.Name & vbCrLf
Next
strValue = dimCube.Hierarchies(0).Levels(0).UniqueName & ".MEMBERS"
strDimensions = strDimensions & "<OPTION value = '" & strValue &
"'>Measures" & vbCrLf
Else
If IsNull(dimCube.Hierarchies(0).Properties("ALL_MEMBER").Value) Then
strValue = dimCube.Hierarchies(0).Levels(0).UniqueName & ".MEMBERS"
Else
strValue = dimCube.Hierarchies(0).Properties("ALL_MEMBER").Value &
".CHILDREN"
End If
strDimensions = strDimensions & "<OPTION value = '" & strValue & "'>" &
dimCube.Name & vbCrLf
End If
Next
Figure 4
HTML MDX Selection Body
<body>
<form method="POST" name="frmInput" action="result.asp" target="bottom" onsubmit="return formInputSubmit();">
<input type="hidden" id="txtMember" name="txtMember">
<input type="hidden" id="txtRow" name="txtRow">
<input type="hidden" id="txtCol" name="txtCol">
<input type="hidden" id="txtMeasure" name="txtMeasure">
<table border="0" cellPadding="2" cellSpacing="0">
<tr>
<td onclick="viewData();" onmouseover="cursorHand(this);" onmouseout="cursorNormal(this)">Pick the data you wish to analyze: Then click here.</td>
<td noWrap style="BORDER:wheat ridge thin">Dimension: <select id="optCol" name="optColumn">
<option Value>(Select Dimension)</option>
<% = strDimensions %></select>
</td></tr>
<tr>
<td noWrap style="BORDER:wheat ridge thin">Dimension: <select id="optRow" name="optRow">
<option Value>(Select Dimension)</option>
<% = strDimensions %></select>
</td>
<td noWrap style="BORDER-BOTTOM:wheat ridge thin; BORDER-RIGHT:wheat ridge thin">Measure: <select id="optMeasure" name="optMeasure">
<option Value>(Select Measure)</option>
<% = strMeasures %></select>
</td>
</tr>
</table>
<p>OR - view a pre-defined analysis: <select id="optQuery" name="optQuery" onchange="queryChange();">
<option>(Select a cube view)</option>
<option>Product Profit Growth for 1997</option>
<option>Product YTD Profit for 1997</option>
<option>Top 12 Best Unit Selling Customers</option>
</select>
</p>
</form>
</body>
Figure 5
JScript MDX Selection Processing
<script Language="JScript">
function formInputSubmit() {
var strMember, strRow, strCol, strMeasure;
var optCol = document.all.optCol;
var optRow = document.all.optRow;
var optQuery = document.all.optQuery
var optMeasure = document.all.optMeasure;
// Ensure all rows and columns have been selected or a predefined query is
// selected
if (optQuery.selectedIndex==0) {
if (optCol.selectedIndex==0) {
optCol.focus();
alert ("A column value must be selected");
return false;
}
if (optRow.selectedIndex==optCol.selectedIndex) {
optCol.focus();
alert ("Column and Row cannot have the same Dimension");
return false;
}
strMember = "";
strCol = optCol.value;
if (optRow.selectedIndex!=0) {
strRow = optRow.value;
} else {
strRow = "";
}
if (optMeasure.selectedIndex!=0) {
strMeasure = optMeasure.value;
} else {
strMeasure = "";
}
} else {
switch (optQuery.selectedIndex) {
case 1:
strMember = "Measures.[Profit Growth] AS '(Measures.[Profit]) - (Measures.[Profit], Time.PrevMember)', FORMAT_STRING = '#.00' ";
strCol = "[Time].[1997].CHILDREN";
strRow = "[Product].[Product Department].MEMBERS";
strMeasure = "Measures.[Profit Growth]";
break;
case 2:
strMember = "Measures.YTDProfit AS 'SUM(YTD(),Measures.[Profit])', FORMAT_STRING = '#.00' ";
strCol = "Generate([Time].[1997].CHILDREN, {[Time].CurrentMember, [Time].CurrentMember.Children})";
strRow = "[Product].[Product Department].MEMBERS";
strMeasure = "Measures.YTDProfit";
break;
case 3:
strMember = "";
strCol = "[Measures].MEMBERS";
strRow = "TOPCOUNT([Customers].[Name].MEMBERS, 12, [Measures].[Unit Sales])";
strMeasure = "";
break;
default:
strMeasure = "";
}
if ((strCol=="")||(strRow=="")) {
alert ("Unable to construct the query");
return false;
}
}
document.all.txtMember.value = strMember;
document.all.txtRow.value = strRow;
document.all.txtCol.value = strCol;
document.all.txtMeasure.value = strMeasure;
return true;
}
function viewData() {
var f = document.all.frmInput;
document.all.optQuery.selectedIndex=0;
if (f.onsubmit()) {
f.submit();
}
}
function queryChange() {
if (document.all.optQuery.selectedIndex!=0) {
var f = document.all.frmInput;
document.all.optRow.selectedIndex=0;
document.all.optCol.selectedIndex=0;
document.all.optMeasure.selectedIndex=0;
document.all.optQuery.blur();
if (f.onsubmit()) {
f.submit();
}
}
}
function cursorHand(item) {
item.style.cursor = "hand";
item.style.textDecorationUnderline = true;
}
function cursorNormal(item) {
item.style.cursor = "";
item.style.textDecorationUnderline = false;
}
</script>
Figure 6
MDX Statement Construction
Dim strMember, strRow, strCol, strMeasure, strMDX
strMember = Request.Form("txtMember")
strRow = Request.Form("txtRow")
strCol = Request.Form("txtCol")
strMeasure = Request.Form("txtMeasure")
strMDX = ""
If strRow <> "" Or strCol <> "" Then
If strMember <> "" Then
strMDX = "WITH MEMBER " & strMember & " "
End If
strMDX = strMDX + "SELECT "
If strCol <> "" And strRow = "" Then
strMDX = strMDX + "{" & strCol & "} ON COLUMNS "
ElseIf strCol = "" And strRow <> "" Then
strMDX = strMDX + "{" & strRow & "} ON ROWS "
Else
strMDX = strMDX + "{" & strCol & "} ON COLUMNS, "
strMDX = strMDX + "{" & strRow & "} ON ROWS "
End If
strMDX = strMDX + "FROM [Sales] "
If strMeasure <> "" Then
strMDX = strMDX + "WHERE (" & strMeasure & ")"
End If
End If
Figure 7
MDX Cellset HTML Table Construction
strValue = " "
strTable = "<TABLE border=1>" & vbCrLf
strTable = strTable & "<TR><TD>" & strValue & "</TD>"
' Construct the column axis names
Set colFoodMart = cellFoodMart.Axes(0)
For Each posFMCol In colFoodMart.Positions
strTable = strTable & "<TD>" & posFMCol.Members(0).Caption & "</TD>"
Next
strTable = strTable & "</TR>" & vbCrLf
' Construct the row axis names and data cells
Set rowFoodMart = cellFoodMart.Axes(1)
For Each posFMRow In rowFoodMart.Positions
strTable = strTable & "<TR><TD>" & posFMRow.Members(0).Caption & "</TD>"
For Each posFMCol In colFoodMart.Positions
strValue = cellFoodMart(posFMCol.Ordinal,
posFMRow.Ordinal).FormattedValue
If strValue = "" Then
strValue = "0.00"
End If
If strMember = "" Then
strValue = FormatNumber(strValue, 2)
End If
strTable = strTable & "<TD>" & strValue & "</TD>"
Next
strTable = strTable & "</TR>" & vbCrLf
Next
strTable = strTable & "</TABLE>" & vbCrLf
Figure 8
HTML Data Table Construction
Dim vbCrLf: vbCrLf = Chr(13) + Chr(10)
Function ReturnConnection()
' Builds and returns a connection into the FoodMart cube
On Error Resume Next
Dim conFoodMart
Set conFoodMart = Server.CreateObject("ADODB.Connection")
conFoodMart.Open "Data Source=MyServer;Provider=MSOLAP;"
conFoodMart.DefaultDatabase="FoodMart"
Set ReturnConnection = conFoodMart
End Function
Function CreateCubeTable(strMember, strRow, strCol, strMeasure, strMDX)
' Returns an HTML table of the CellSet values
On Error Resume Next
Dim conFoodMart, rowFoodMart, colFoodMart, posFMCol, posFMRow, cellFoodMart
Dim strTable, strDrillRow, strDrillCol
Dim strClass, strValue, strDrill
Dim iCount, fMember
' See if we have a calculated member
If strMember = "" Then
fMember = False
Else
fMember = True
End If
' first construct the MDX expression
strMDX = ""
If strRow <> "" Or strCol <> "" Then
If fMember = True Then
strMDX = "WITH MEMBER " & strMember & " "
End If
strMDX = strMDX + "SELECT "
If strCol <> "" And strRow = "" Then
strMDX = strMDX + "{" & strCol & "} ON COLUMNS "
ElseIf strCol = "" And strRow <> "" Then
strMDX = strMDX + "{" & strRow & "} ON ROWS "
Else
strMDX = strMDX + "{" & strCol & "} ON COLUMNS, "
strMDX = strMDX + "{" & strRow & "} ON ROWS "
End If
strMDX = strMDX + "FROM [Sales] "
If strMeasure <> "" Then
strMDX = strMDX + "WHERE (" & strMeasure & ")"
End If
End If
If strMDX = "" Then
strTable = "Select a view of the data"
Else
' open a connection to the foodmart database
Set conFoodMart = ReturnConnection()
' Open a cellset of information using the MDX statement
Set cellFoodMart = Server.CreateObject("ADOMD.Cellset")
cellFoodMart.Open strMDX, conFoodMart
' start the definition of the table displaying the data
strClass = "measure"
strValue = " "
strTable = "<TABLE class=olapdata>" & vbCrLf
If strRow = "" Then
strTable = strTable & "<TR>"
Else
strTable = strTable & "<TR><TD>" & strValue & "</TD>"
End If
' Construct the column axis names
Set colFoodMart = cellFoodMart.Axes(0)
For Each posFMCol In colFoodMart.Positions
strDrillCol = posFMCol.Members(0).UniqueName
If posFMCol.Members(0).ChildCount = 0 Then
strDrill = "nodrill"
Else
strDrill = ""
End If
strValue = posFMCol.Members(0).Caption
strTable = strTable & "<TD class=dimensioncol drillrow='' drillcol='"
& strDrillCol & "'" & strDrill & ">" & strValue & "</TD>"
Next
strTable = strTable & "</TR>" & vbCrLf
' Construct the row axis names and data cells
If strRow = "" Then
' do not have row axis so just output the number
For iCount = 1 To cellFoodMart.Axes(0).Positions.Count
strDrillCol = cellFoodMart.Axes(0).Positions(iCount -
1).Members(0).UniqueName
If cellFoodMart.Axes(0).Positions(iCount -
1).Members(0).ChildCount = 0 Then
strDrill = "nodrill"
Else
strDrill = ""
End If
strValue = cellFoodMart(iCount-1).FormattedValue
If strValue = "" Then
strValue = "0.00"
strDrill = "nodrill"
End If
If fMember = False Then
strValue = FormatNumber(strValue, 2)
End If
strTable = strTable & "<TD class=" & strClass & " drillcol='"
& strDrillCol & "' drillrow='' " & strDrill & ">" & strValue
& "</TD>"
Next
Else
' have both a column and a row to deal with
Set rowFoodMart = cellFoodMart.Axes(1)
For Each posFMRow In rowFoodMart.Positions
strDrillRow = posFMRow.Members(0).UniqueName
If posFMRow.Members(0).ChildCount = 0 Then
strDrill = "nodrill"
Else
strDrill = ""
End If
strValue = posFMRow.Members(0).Caption
strTable = strTable
& "<TR><TD class=dimensionrow drillcol='' drillrow='"
& strDrillRow &"'" & strDrill & ">" & strValue & "</TD>"
For Each posFMCol In colFoodMart.Positions
strDrillCol = posFMCol.Members(0).UniqueName
If posFMCol.Members(0).ChildCount = 0 Or
posFMRow.Members(0).ChildCount = 0 Then
strDrill = "nodrill"
Else
strDrill = ""
End If
strValue = cellFoodMart(posFMCol.Ordinal,
posFMRow.Ordinal).FormattedValue
If strValue = "" Then
strValue = "0.00"
strDrill = "nodrill"
End If
If fMember = False Then
strValue = FormatNumber(strValue, 2)
End If
strTable = strTable & "<TD class=" & strClass & " drillcol='"
& strDrillCol & "' drillrow='" & strDrillRow & "' "
& strDrill & ">" & strValue & "</TD>"
Next
If strClass = "measure" Then strClass = "measurealt"
Else strClass = "measure"
strTable = strTable & "</TR>" & vbCrLf
Next
End If
strTable = strTable & "</TABLE>" & vbCrLf
End If
CreateCubeTable = strTable
End Function
Figure 9
Application Stylesheet Definition
.measure
{
FONT-FAMILY: 'Times New Roman';
PADDING-LEFT: 5px;
TEXT-ALIGN: right
}
.measurealt
{
BACKGROUND-COLOR: palegoldenrod;
FONT-FAMILY: 'Times New Roman';
PADDING-LEFT: 5px;
TEXT-ALIGN: right
}
BODY
{
COLOR: navy;
FONT-FAMILY: Comic Sans MS
}
SELECT
{
COLOR: blue;
FONT-FAMILY: Comic Sans MS
}
.mdxcode
{
COLOR: black;
FONT-FAMILY: Courier New;
FONT-SIZE: smaller
}
.olapdata
{
FONT-SIZE: smaller;
TEXT-ALIGN: center
}
.dimensionrow
{
BACKGROUND-COLOR: wheat;
FONT-WEIGHT: bolder;
TEXT-ALIGN: left
}
.dimensioncol
{
BACKGROUND-COLOR: wheat;
FONT-WEIGHT: bolder;
TEXT-ALIGN: center
}
Figure 10
OLAP Data Presentation Page
<%@ Language=VBScript %>
<!--#include file="functions.asa"-->
<%
Dim strMember, strRow, strCol, strMeasure, strMDX
strMember = Request.Form("txtMember")
strRow = Request.Form("txtRow")
strCol = Request.Form("txtCol")
strMeasure = Request.Form("txtMeasure")
%>
<html>
<head>
<meta NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<link rel="stylesheet" href="default.css">
<script Language="JScript">
function formInputSubmit() {
return true;
}
function viewData() {
var item = event.srcElement;
var itemclass = item.className;
if (itemclass=="dimensionrow" || itemclass=="dimensioncol" || itemclass=="measure" || itemclass=="measurealt") {
if (item.getAttribute("nodrill")==null) {
var strDrillCol, strDrillRow;
strDrillCol = item.drillcol;
strDrillRow = item.drillrow;
if (strDrillCol!="")
document.all.txtCol.value = strDrillCol + ".CHILDREN";
if (strDrillRow!="")
document.all.txtRow.value = strDrillRow + ".CHILDREN";
var f = document.all.frmInput;
if (f.onsubmit()) {
f.submit();
}
event.cancelBubble = true;
}
}
}
function cursorHand() {
var item = event.srcElement;
var itemclass = item.className;
if (itemclass=="dimensionrow" || itemclass=="dimensioncol" || itemclass=="measure" || itemclass=="measurealt") {
if (item.getAttribute("nodrill")==null) {
item.style.cursor = "hand";
item.style.textDecorationUnderline = true;
item.style.color = 'red';
event.cancelBubble = true;
}
}
}
function cursorNormal() {
var item = event.srcElement;
var itemclass = item.className;
if (itemclass=="dimensionrow" || itemclass=="dimensioncol" || itemclass=="measure" || itemclass=="measurealt") {
var tester = item.getAttribute("nodrill");
if (item.getAttribute("nodrill")==null) {
item.style.cursor = "";
item.style.textDecorationUnderline = false;
item.style.color = 'navy';
event.cancelBubble = true;
}
}
}
</script>
</head>
<body>
<p onclick="viewData();" onmouseover="cursorHand();" onmouseout="cursorNormal()"><% = CreateCubeTable(strMember, strRow, strCol, strMeasure, strMDX) %></p>
<p class="mdxcode"><% = strMDX %></p>
<form method="POST" name="frmInput" action="result.asp" target="bottom" onsubmit="return formInputSubmit();">
<input type="hidden" id="txtMember" name="txtMember" value="<% = strMember %>">
<input type="hidden" id="txtRow" name="txtRow" value="<% = strRow %>">
<input type="hidden" id="txtCol" name="txtCol" value="<% = strCol %>">
<input type="hidden" id="txtMeasure" name="txtMeasure" value="<% = strMeasure %>">
</form>
</body>
</html>