INF: Example Active Server Page to Access OLAP Services (199002)



The information in this article applies to:

  • Microsoft SQL Server OLAP Services 7.0

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:
  1. Install Microsoft Internet Information Server (IIS) 3.0 or later, or Microsoft Peer Web Services 3.0 or later.
  2. Install Microsoft Active Server Pages 1.0b on the same Web server.
  3. Install OLAP Client on the Web server computer.
  4. 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.

Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbinfo KB199002