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):
<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.
(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:
Keywords: kbhowto kbofficewebchart KB279254