Edit

Share via

Axis Example (VBScript)

  • Article

This Active Server Page displays OLAP data from an MDX Query string and writes the resulting cellset to an HTML table structure.

<%@ 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 Multidimensional Catalog and Cellset  
'************************************************************************  
Set cat = Server.CreateObject("ADOMD.Catalog")  
Set cst = Server.CreateObject("ADOMD.CellSet")  
  
'************************************************************************  
'*** Use default settings of a known OLAP Server  
'*** for Server Name for Connection Set Server Name Session Object  
'*** to default value  
'************************************************************************  
'*** Must set OLAPServerName to OLAP Server that is  
'*** present on network  
'************************************************************************  
   OLAPServerName = "Please set to present OLAP Server"  
   cat.ActiveConnection = "Data Source=" & OLAPServerName & _  
      ";Initial Catalog=FoodMart;Provider=msolap;"  
  
'************************************************************************  
'*** Use default MDX Query string of a known query that works  
'*** with default server Set MDXQuery Session Object to default value  
'************************************************************************  
   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>