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

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

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>

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.
Save the file as ChartMDX.htm.
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.

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

SUMMARY

MORE INFORMATION

REFERENCES