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

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.