Microsoft KB Archive/199002

= INF: Example Active Server Page to Access OLAP Services =

Article ID: 199002

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server OLAP Services

-



This article was previously published under Q199002



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 

<%

' 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 ("") ' start the first row, emit upper-left, blank cell Response.Write ("")

' 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 ("" + 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 ("" + name + "</TD>")

' Data cells for x = 0 to cs.Axes(0).Positions.Count-1 Response.Write ("") 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  tag to make multilevel column headings look ' better %>

'End Example Web Page </li></ol>

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.

Keywords: kbinfo KB199002

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.