How To Chart MDX Results with the Office 2000 Chart Component (279254)



The information in this article applies to:

  • Microsoft Office Chart Component 9.0

This article was previously published under Q279254

SUMMARY

This article illustrates how you can chart results from a query to an OLAP data source with the Microsoft Office 2000 Chart component.

MORE INFORMATION

The Office 2000 Chart component cannot directly bind to a multidimensional expressions (MDX) cellset that is obtained from an OLAP data source. However, the Office 2000 Chart component can bind to a flat ActiveX Data Objects (ADO) recordset or literal data. To chart MDX results, you can:
  • Create a cellset for the MDX query, transfer the results to an array (or a delimited text string), and then use the literal data for the chart's data source. - or -

  • Create a flattened recordset from the MDX query and use that ADO recordset as the data source for the chart.
The code presented in this article describes both of these techniques.

NOTE: The Office XP Chart component has added functionality for binding to a data source, including OLAP data sources.

For additional informationabout details on binding to a data source with the Office XP Chart component, click the article number below to view the article in the Microsoft Knowledge Base:

288907 INFO: Binding the Office XP Chart Component to a Data Source

Sample
  1. Copy the following code into any text editor (for example, Microsoft Notepad):
    <HTML>
    
    <BODY>
    
    <BUTTON ID=btnOne>Use a Flattened Cellset </BUTTON>
    <P><P>
    <BUTTON ID=btnTwo>Use an Array Built From CellSet</BUTTON>
    <P><P>
    <OBJECT classid=clsid:0002E500-0000-0000-C000-000000000046 id=CSpace
    style="HEIGHT:300; WIDTH:400"></OBJECT>
    
    </BODY>
    
    <SCRIPT Language="VBScript">
    
    Dim sMDX, sConn, c
    
    sMDX = "SELECT Measures.MEMBERS ON COLUMNS, " & _
           "TOPCOUNT({[Store].[Store City].MEMBERS}, 3, " & _
           "Measures.[Store Sales Net]) ON ROWS From [Sales]"
    
    sConn = "Provider=msolap;Initial Catalog=FoodMart;" & _
            "Data Source=YourSQLServer"
    
    Set c = CSpace.Constants 'for OWC Constants
    
    Function btnOne_OnClick()
    
        'Get the Flattened recordset for the MDX query
        Dim oConn, oRS
        Set oConn = CreateObject("ADODB.Connection")
        Set oRS = CreateObject("ADODB.Recordset")
        oConn.Open sConn
        oRS.Open sMDX, oConn, 3 'adOpenStatic=3
    
        'Set the recordset as the datasource for the chart
        CSpace.Clear
        Set CSpace.DataSource = oRS
    
        'Create a new chart in the chartspace
        Dim oChart
        Set oChart = CSpace.Charts.Add
        oChart.HasTitle = True
        oChart.Title.Caption = "Top 3 Cities"
        oChart.HasLegend = True
        
        'Add a new series to the chart for Sales
        With oChart.SeriesCollection.Add
            .Caption = "Sales"
            .SetData c.chDimCategories, 0, oRS.Fields(2).Name 'City
            .SetData c.chDimValues, 0, oRS.Fields(5).Name     'Sales
        End With
        
        'Add a new series to the chart for Net Sales
        With oChart.SeriesCollection.Add
            .Caption = "Net Sales"
            .SetData c.chDimCategories, 0, oRS.Fields(2).Name 'City
            .SetData c.chDimValues, 0, oRS.Fields(7).Name     'Net Sales
        End With
    
        CSpace.Refresh
    
        'Close the recordset and connection
        oRS.Close
        oConn.Close
    
    End Function
    
    Function btnTwo_OnClick()
    
        'Get a cellset for the MDX query
        Dim oConn, oCSet
        Set oConn = CreateObject("ADODB.Connection")
        Set oCSet = CreateObject("ADOMD.Cellset")
        oConn.Open sConn
        oCSet.Open sMDX, oConn
        
        'Create an array from the Cellset -- one array for the categories (City),
        'one for Sales and one for NetSales
        Dim nCount
        nCount = oCSet.Axes(1).Positions.Count
        ReDim aCities(nCount)
        ReDim aSales(nCount)
        ReDim aNetSales(nCount)
        For r = 0 To oCSet.Axes(1).Positions.Count - 1
            aCities(r) = oCSet.Axes(1).Positions(r).Members(0).Caption
            aSales(r) = oCSet(2, r).FormattedValue
            aNetSales(r) = oCSet(4, r).FormattedValue
        Next
                
        'Close the cellset and connection
        oCSet.Close
        oConn.Close
        
        'Create a new chart in the chartspace
        CSpace.Clear
        Dim oChart
        Set oChart = CSpace.Charts.Add
        oChart.HasTitle = True
        oChart.Title.Caption = "Top 3 Cities"
        oChart.HasLegend = True
        
        'Add a new series to the chart for Sales
        With oChart.SeriesCollection.Add
            .Caption = "Sales"
            .SetData c.chDimCategories, c.chDataLiteral, aCities
            .SetData c.chDimValues, c.chDataLiteral, aSales
        End With
        
        'Add a new series to the chart for Net Sales
        With oChart.SeriesCollection.Add
            .Caption = "Net Sales"
            .SetData c.chDimCategories, c.chDataLiteral, aCities
            .SetData c.chDimValues, c.chDataLiteral, aNetSales
        End With
    
        CSpace.Refresh
        
    End Function
    
    </SCRIPT>
    
    </HTML>
    					
  2. Replace YourSQLServer in the connection string constant sConn with the name of your SQL Server. Also, note that the connection string references the Foodmart sample database that is included with Microsoft SQL Server 7.0. If you are using Microsoft SQL Server 2000, change this to Foodmart 2000.
  3. Save the file as ChartMDX.htm.
  4. Start Microsoft Internet Explorer and browse to ChartMDX.htm. Click either button to display the chart; both produce the same chart that contains two series with three data points per series.
(c) Microsoft Corporation 2001, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.

REFERENCES

For additional information about the Microsoft Office Web components, see the following Microsoft support Web site at:

Modification Type:MinorLast Reviewed:8/23/2005
Keywords:kbhowto kbOfficeWebChart KB279254