You can create Active Server Pages (ASPs) to enable users to query cubes and view returned datasets with Web browsers.
The following sections describe two sample programs:
Both sample programs use the sample FoodMart database (foodmart.mdb), which is provided with Microsoft SQL Server™ OLAP Services.
The sample program in this section includes the core operations required to display the results of a cube query through a Web browser. This program is intended to show only the basics and to provide a foundation upon which you can create more dynamic programs.
The sample program produces the following page as viewed in Microsoft Internet Explorer.
The following code produces the preceding page:
<%@ Language=VBScript %>
<%
'**********************************************************************
'**********************************************************************
'*** Active Server Page displays OLAP data from default
'*** MDX Query string and writes resulting cell set to HTML table
'*** structure.
'**********************************************************************
'**********************************************************************
Response.Buffer=True
Response.Expires=0
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY bgcolor=Ivory>
<FONT FACE=Verdana>
<%
Dim cat,cst,i,j,strSource,csw,intDC0,intDC1,intPC0, intPC1
'**********************************************************************
'*** Set Connection Objects for Multi dimensional Catalog and Cell Set.
'**********************************************************************
Set cat = Server.CreateObject("ADOMD.Catalog")
Set cst = Server.CreateObject("ADOMD.CellSet")
'**********************************************************************
'*** Use default settings of a known OLAP server
'*** for Server Name.
'**********************************************************************
cat.ActiveConnection = "Data Source=MACHUE1;Initial Catalog=FoodMart;Provider=msolap;"
'**********************************************************************
'*** Use default MDX Query string of a known query
'*** that works with default server.
'**********************************************************************
strSource = strSource & "SELECT "
strSource = strSource & "{[Measures].members} ON COLUMNS,"
strSource = strSource & "NON EMPTY [Store].[Store City].members ON ROWS"
strSource = strSource & " FROM Sales"
'**********************************************************************
'*** Set Cell Set Source property to strSource to be passed on
'*** cell set open method.
'**********************************************************************
cst.Source = strSource
'**********************************************************************
'*** Set Cell Sets Active connection to use the current Catalogs
'*** Active connection.
'**********************************************************************
Set cst.ActiveConnection = cat.ActiveConnection
'**********************************************************************
'*** Using Open method, Open cell set.
'**********************************************************************
cst.Open
'**********************************************************************
'*** Set Dimension Counts minus 1 for Both Axes to intDC0, intDC1.
'*** Set Position Counts minus 1 for Both Axes to intPC0, intPC1.
'**********************************************************************
intDC0 = cst.Axes(0).DimensionCount-1
intDC1 = cst.Axes(1).DimensionCount-1
intPC0 = cst.Axes(0).Positions.Count - 1
intPC1 = cst.Axes(1).Positions.Count - 1
'**********************************************************************
'*** Create HTML Table structure to hold MDX Query return Record set.
'**********************************************************************
Response.Write "<Table width=100% border=1>"
'**********************************************************************
'*** Loop to create Column header.
'**********************************************************************
For h=0 to intDC0
Response.Write "<TR>"
'**********************************************************************
'*** Loop to create spaces in front of Column headers
'*** to align with Row header.
'**********************************************************************
For c=0 to intDC1
Response.Write "<TD></TD>"
Next
'**********************************************************************
'*** Iterate through Axes(0) Positions writing member captions
'*** to table header.
'**********************************************************************
For i = 0 To intPC0
Response.Write "<TH>"
Response.Write "<FONT size=-2>"
Response.Write cst.Axes(0).Positions(i).Members(h).Caption
Response.Write "</FONT>"
Response.Write "</TH>"
Next
Response.Write "</TR>"
Next
'**********************************************************************
'*** Use Array values for row header formatting to provide
'*** spaces under beginning row header titles.
'**********************************************************************
For j = 0 To intPC1
Response.Write "<TR>"
For h=0 to intDC1
Response.Write "<TD><B>"
Response.Write "<FONT size=-2>"
Response.Write cst.Axes(1).Positions(j).Members(h).Caption
Response.Write "</FONT>"
Response.Write "</B></TD>"
Next
For k = 0 To intPC0
Response.Write "<TD align=right bgcolor="
Response.Write csw
Response.Write ">"
Response.Write "<FONT size=-2>"
Response.Write cst(k, j).FormattedValue
Response.Write "</FONT>"
Response.Write "</TD>"
Next
Response.Write "</TR>"
Next
Response.Write "</Table>"
%>
</FONT>
</BODY>
</HTML>
The sample program described in this section enables users to query cubes and view the returned datasets through a Web browser.
The sample program produces the following page as viewed in Internet Explorer:
You can find this sample program, named AspAdoComplex, in the ..\MSOLAP\Samples folder on the SQL Server CD-ROM.