INF: Example Active Server Page to Access OLAP Services
ID: Q199002
|
The information in this article applies to:
-
Microsoft SQL Server OLAP Services version 7.0
SUMMARY
This article is intended to give OLAP developers instructions on how to set up an Active Server Web page that will access and display data from an OLAP Services cube.
MORE INFORMATION
To set up Active Server Pages (ASP), perform the following steps:
- Install Microsoft Internet Information Server (IIS) 3.0 or later, or Microsoft Peer Web Services 3.0 or later.
- Install Microsoft Active Server Pages 1.0b on the same Web server.
- Install OLAP Client on the Web server computer.
- Create a text file with the name ADOMD_Ex.asp on that server in the Web root directory (or subdirectory thereof). All Active Server Pages must end with the extension .asp. The text of this file should be the following:
NOTE: If your SQL Server OLAP Services server is different than your Web server, you must change the word "localhost" in the line cn.Open "provider=msolap;data source=localhost" to the name of your OLAP server.
'Start Example Web Page
<Language=VBScript>
<%
' Build the MDX statement
szMDX = "with member [Measures].[Store Profit Rate] "
szMDX = szMDX + "as '([Measures].[Store Sales]-"
szMDX = szMDX + "[Measures].[Store Cost])/[Measures].[Store Cost]', "
szMDX = szMDX + "format = '#.00%' "
szMDX = szMDX + "select {[Measures].[Store Cost],"
szMDX = szMDX + "[Measures].[Store Sales],"
szMDX = szMDX + "[Measures].[Store Profit Rate]} on columns, "
szMDX = szMDX + "Order([Product].[Product Department].members, "
szMDX = szMDX + "[Measures].[Store Profit Rate], BDESC) on rows "
szMDX = szMDX + "from Sales where ([Time].[1997])"
' Connect to the OLAP server
set cn = Server.CreateObject ("ADODB.Connection")
cn.Open "provider=msolap;data source=localhost"
cn.DefaultDatabase = "Foodmart"
' Create a cellset
set cs = Server.CreateObject ("ADOMD.Cellset")
cs.ActiveConnection = cn
cs.Open szMDX
' Emit an HTML table to show the results
Response.Write ("<TABLE BORDER=0>")
' start the first row, emit upper-left, blank cell
Response.Write ("<TR><TD></TD>")
' Display the columns axis
for each p in cs.Axes(0).Positions
name = "<CENTER><B>"
for each m in p.Members
name = name + m.Caption + "<BR>"
next
name = name + "</B></CENTER>"
Response.Write ("<TD>" + name + </TD>")
next
Response.Write ("</TR>")
' Display each row, row label first, then data cells
y = 0
for each py in cs.Axes(1).Positions
' Do the row label
name = ""
for each m in py.Members
name = name + m.Caption + "<BR>"
next
Response.Write ("<TD>" + name + "</TD>")
' Data cells
for x = 0 to cs.Axes(0).Positions.Count-1
Response.Write ("<TD ALIGN=RIGHT>")
Response.Write (cs(x,y).FormattedValue)
Response.Write ("</TD>")
next
Response.Write ("</TR>")
y = y + 1
next
' Obvious enhancements
' Allow the user to enter their own statement in a form,
' post the statement to the same ASP
' Cache the connection in a session variable if using IIS 3.0.
' There is no advantage to this in IIS 4.0 and later.
' Use the colspan in the <TD> tag to make multilevel column headings look
' better
%>
'End Example Web Page
Web browsers that have access to the Web server can now access the OLAP data by going to the address of the new .asp page. If the .asp page is in the Web server root directory, that address would be http://<server_name>/ADOMD_Ex.asp.
Additional query words:
Keywords : SSOSadomd
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo
|