Microsoft KB Archive/279254

= How To Chart MDX Results with the Office 2000 Chart Component =

Article ID: 279254

Article Last Modified on 8/23/2005

-

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   Copy the following code into any text editor (for example, Microsoft Notepad): 



Use a Flattened Cellset   Use an Array Built From CellSet  </OBJECT>

</BODY>

<SCRIPT Language=&quot;VBScript&quot;>

Dim sMDX, sConn, c

sMDX = &quot;SELECT Measures.MEMBERS ON COLUMNS, &quot; & _ &quot;TOPCOUNT({[Store].[Store City].MEMBERS}, 3, &quot; & _      &quot;Measures.[Store Sales Net]) ON ROWS From [Sales]&quot;

sConn = &quot;Provider=msolap;Initial Catalog=FoodMart;&quot; & _ &quot;Data Source=YourSQLServer&quot;

Set c = CSpace.Constants 'for OWC Constants

Function btnOne_OnClick

'Get the Flattened recordset for the MDX query Dim oConn, oRS Set oConn = CreateObject(&quot;ADODB.Connection&quot;) Set oRS = CreateObject(&quot;ADODB.Recordset&quot;) 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 = &quot;Top 3 Cities&quot; oChart.HasLegend = True 'Add a new series to the chart for Sales With oChart.SeriesCollection.Add .Caption = &quot;Sales&quot; .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 = &quot;Net Sales&quot; .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(&quot;ADODB.Connection&quot;) Set oCSet = CreateObject(&quot;ADOMD.Cellset&quot;) 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 = &quot;Top 3 Cities&quot; oChart.HasLegend = True 'Add a new series to the chart for Sales With oChart.SeriesCollection.Add .Caption = &quot;Sales&quot; .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 = &quot;Net Sales&quot; .SetData c.chDimCategories, c.chDataLiteral, aCities .SetData c.chDimValues, c.chDataLiteral, aNetSales End With

CSpace.Refresh End Function

</SCRIPT>

</HTML> </li> <li>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.</li> <li>Save the file as ChartMDX.htm.</li> <li>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.</li></ol>

(c) Microsoft Corporation 2001, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.

<div class="references_section">