You can connect to SQL Server Analysis Services 2005 and 2008 cubes, Microsoft SQL 2000, 2005, and 2008, Excel, Access, and XML data sources. For all but unbound data sources, you can do this without any code by dropping the data source control onto the form, setting its properties, and setting the DataSource property of the PivotView control to the data source control. You can also connect to any of these data sources in code.
Note: If you want to use the Microsoft Jet OLEDB 4.0 data provider on a 64 bit machine, in Visual Studio, open the project properties, and on the Compile page, click the Advanced Compile Options button. In the Advanced Compiler Settings dialog that appears, change the Target CPU property to x86. (The default value is AnyCPU.) |
Code-Free Methods
To connect to an ActiveAnalysis Local cube without code
- In Visual Studio, open the design view of the form containing your PivotView control.
- From the Visual Studio toolbox, drag the LocalCubeDataSource control and drop it onto your form.
- The LocalCubeDataSource control, named LocalCubeDataSource1 by default, appears in a tray below the form, and is selected in the Properties grid.
- In the LocalCubeFile property, enter the path to your ActiveAnalysis local cube.
Path to local cube file. Copy Code C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindLocalCube.ddacube - Change the AutoConnect property to True to have the data source connect automatically when you run the application.
- Select the PivotView control in the Properties grid, and in the DataSource property, select LocalCubeDataSource1.
To connect to a SQL Server Analysis Services (MDX) data cube without code
- In Visual Studio, open the design view of the form containing your PivotView control.
- From the Visual Studio toolbox, drag the MdxDataSource control and drop it onto your form.
- The MdxDataSource control, named MdxDataSource1 by default, appears in a tray below the form, and is selected in the Properties grid.
- In the ConnectionString property, enter a connection string like the following for your SQL Server Analysis Services data cube.
MDX connection string. Paste in the ConnectionString property. Copy Code Data Source=localhost; Catalog=Adventure Works DW 2008; - In the Cube property, you can optionally enter the name of a perspective that you have within your data cube, such as Mined Customers in the Adventure Works sample data cube.
- Change the AutoConnect property to True to have the data source connect automatically when you run the application.
- Select the PivotView control in the Properties grid, and in the DataSource property, select MdxDataSource1.
To connect to a relational data (RD) source without code
- In Visual Studio, open the design view of the form containing your PivotView control.
- From the Visual Studio toolbox, drag the RdDataSource control and drop it onto your form.
- The RdDataSource control, named RdDataSource1 by default, appears in a tray below the form, and is selected in the Properties grid.
- In the ConnectionString property, enter a connection string like the following for the sample Northwind Access database.
Relational data connection string. Paste in the ConnectionString property. Copy Code Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb; - In the ConnectionType property, select OleDb to specify an Access database.
- Optionally enter the path to a schema file in the CustomSchemaFile property. You can use the following included sample schema file.
Sample schema path. Paste in the CustomSchemaFile property. Copy Code C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindMDB-SalesProductsSuppliers.schema - In the QueryString property, enter a SQL query for the data that you want to pull from the data source.
SQL query. Paste in the QueryString property. Copy Code SELECT c.CategoryName, p.ProductName, o.OrderID, o.OrderDate, d.UnitPrice, d.Quantity, s.Country AS SupplierCountry, s.City AS SupplierCity, s.CompanyName AS SupplierCompany, m.Country AS CustomerCountry, m.City AS CustomerCity, m.CompanyName AS CustomerCompany, d.Discount, m.Region AS CustomerRegion, s.Region AS SupplierRegion, e.LastName, e.EmployeeID AS EmployeeID, e.Title AS EmployeeTitle, e.Region AS EmployeeRegion, e.Country AS EmployeeCountry, e.City AS EmployeeCity, o.Freight, o.ShipName, o.ShipVia, o.ShipCity, o.ShipRegion, o.ShipCountry FROM Suppliers AS s INNER JOIN ((Categories AS c INNER JOIN Products AS p ON c.CategoryID = p.CategoryID) INNER JOIN (Employees AS e INNER JOIN (Customers AS m INNER JOIN (Orders AS o INNER JOIN [Order Details] AS d ON (o.OrderID = d.OrderID) AND (o.OrderID = d.OrderID) AND (o.OrderID = d.OrderID)) ON m.CustomerID = o.CustomerID) ON e.EmployeeID = o.EmployeeID) ON p.ProductID = d.ProductID) ON s.SupplierID = p.SupplierID - Change the AutoConnect property to True to have the data source connect automatically when you run the application. (If you try to change it prior to setting all of the required connection parameters, it automatically changes to True once all of the required parameters have values supplied.)
- Select the PivotView control in the Properties grid, and in the DataSource property, select RdDataSource1.
To connect to an unbound data source without code
To connect to an XML data source without code
- In Visual Studio, open the design view of the form containing your PivotView control.
- From the Visual Studio toolbox, drag the XmlDataSource control and drop it onto your form.
- The XmlDataSource control, named XmlDataSource1 by default, appears in a tray below the form, and is selected in the Properties grid.
- In the DataFile property, click the ellipsis button to navigate to your XML data file, or paste in the following path to the sample.
XML file path. Paste in the DataFile property . Copy Code C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\Factbook.xml - In the CustomSchemaFile property, click the ellipsis button to navigate to your XML data file.
Schema file path. Paste in the CustomSchemaFile property. Copy Code C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\LifeExpectancyByGdpAndLiteracy.schema - In the QueryString property, enter a valid XPath expression, such as //country.
- In the TransformationFile property, you can optionally enter the path to a Transformation (*.xslt) file.
Transformation file path. Paste in the TransformationFile property. Copy Code C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\LifeExpectancyByGdpAndLiteracy.xsl - Change the AutoConnect property to True to have the data source connect automatically when you run the application. (If you try to change it prior to setting all of the required connection parameters, it automatically changes to True once all of the required parameters have values supplied.)
- Select the PivotView control in the Properties grid, and in the DataSource property, select XmlDataSource1.
Code Methods
To connect to an ActiveAnalysis Local cube using code
- In Visual Studio, open the design view of the form containing your PivotView control.
- From the Visual Studio toolbox, drag the LocalCubeDataSource control and drop it onto your form.
- The LocalCubeDataSource control, named LocalCubeDataSource1 by default, appears in a tray below the form, and is selected in the Properties grid.
- Double-click the PivotView control on the form to open the code view and create the PivotView Load event.
- In the Load event, enter code like the following to set up your data source and connect it to the PivotView control.
Visual Basic.NET code. Paste INSIDE the PivotView Load event. Copy Code Me.LocalCubeDataSource1.LocalCubeFile = "C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindLocalCube.ddacube" Me.LocalCubeDataSource1.Connect() Me.PivotView1.DataSource = LocalCubeDataSource1
C# code. Paste INSIDE the PivotView Load event. Copy Code this.localCubeDataSource1.LocalCubeFile = @"C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindLocalCube.ddacube"; this.localCubeDataSource1.Connect(); this.pivotView1.DataSource = localCubeDataSource1;
To connect to a SQL Server Analysis Services (MDX) data cube using code
- In Visual Studio, open the design view of the form containing your PivotView control.
- From the Visual Studio toolbox, drag the MdxDataSource control and drop it onto your form.
- The MdxDataSource control, named MdxDataSource1 by default, appears in a tray below the form, and is selected in the Properties grid.
- Double-click the PivotView control on the form to open the code view and create the PivotView Load event.
- In the Load event, enter code like the following to set up your data source and connect it to the PivotView control.
Visual Basic.NET code. Paste INSIDE the PivotView Load event. Copy Code Me.MdxDataSource1.ConnectionString = "Data Source=localhost;Catalog=Adventure Works DW 2008;" Me.MdxDataSource1.Cube = "Mined Customers" 'optional Me.MdxDataSource1.Connect() Me.PivotView1.DataSource = MdxDataSource1
C# code. Paste INSIDE the PivotView Load event. Copy Code this.mdxDataSource1.ConnectionString = "Data Source=localhost;Catalog=Adventure Works DW 2008;"; this.mdxDataSource1.Cube = "Mined Customers"; //optional this.mdxDataSource1.Connect(); this.pivotView1.DataSource = mdxDataSource1;
To connect to a relational data (RD) source using code
- In Visual Studio, open the design view of the form containing your PivotView control.
- From the Visual Studio toolbox, drag the RdDataSource control and drop it onto your form.
- The RdDataSource control, named RdDataSource1 by default, appears in a tray below the form, and is selected in the Properties grid.
- Double-click the PivotView control on the form to open the code view and create the PivotView Load event.
- In the Load event, enter code like the following to set up your data source and connect it to the PivotView control.
Visual Basic.NET code. Paste INSIDE the PivotView Load event. Copy Code Me.RdDataSource1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb;" Me.RdDataSource1.ConnectionType = GrapeCity.ActiveAnalysis.DataSources.ConnectionType.OleDb Me.RdDataSource1.CustomSchemaFile = "C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindMDB-SalesProductsSuppliers.schema" Me.RdDataSource1.QueryString = "SELECT c.CategoryName, p.ProductName, o.OrderID, o.OrderDate, d.UnitPrice, d.Quantity, s.Country AS SupplierCountry, s.City AS SupplierCity," & _ " s.CompanyName AS SupplierCompany, m.Country AS CustomerCountry, m.City AS CustomerCity, m.CompanyName AS CustomerCompany, d.Discount, m.Region AS CustomerRegion, s.Region AS " & _ "SupplierRegion, e.LastName, e.EmployeeID AS EmployeeID, e.Title AS EmployeeTitle, e.Region AS EmployeeRegion, e.Country AS EmployeeCountry, e.City AS EmployeeCity, o.Freight, " & _ "o.ShipName, o.ShipVia, o.ShipCity, o.ShipRegion, o.ShipCountry FROM Suppliers AS s INNER JOIN ((Categories AS c INNER JOIN Products AS p ON c.CategoryID = p.CategoryID) " & _ "INNER JOIN (Employees AS e INNER JOIN (Customers AS m INNER JOIN (Orders AS o INNER JOIN [Order Details] AS d ON (o.OrderID = d.OrderID) AND (o.OrderID = d.OrderID) AND " & _ "(o.OrderID = d.OrderID)) ON m.CustomerID = o.CustomerID) ON e.EmployeeID = o.EmployeeID) ON p.ProductID = d.ProductID) ON s.SupplierID = p.SupplierID" Me.RdDataSource1.Connect() Me.PivotView1.DataSource = RdDataSource1
C# code. Paste INSIDE the PivotView Load event. Copy Code this.rdDataSource1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb;"; this.rdDataSource1.ConnectionType = GrapeCity.ActiveAnalysis.DataSources.ConnectionType.OleDb; this.rdDataSource1.CustomSchemaFile= @"C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindMDB-SalesProductsSuppliers.schema"; this.rdDataSource1.QueryString = @"SELECT c.CategoryName, p.ProductName, o.OrderID, o.OrderDate, d.UnitPrice, d.Quantity, s.Country AS SupplierCountry, s.City AS SupplierCity, s.CompanyName AS SupplierCompany, m.Country AS CustomerCountry, m.City AS CustomerCity, m.CompanyName AS CustomerCompany, d.Discount, m.Region AS CustomerRegion, s.Region AS SupplierRegion, e.LastName, e.EmployeeID AS EmployeeID, e.Title AS EmployeeTitle, e.Region AS EmployeeRegion, e.Country AS EmployeeCountry, e.City AS EmployeeCity, o.Freight, o.ShipName, o.ShipVia, o.ShipCity, o.ShipRegion, o.ShipCountry FROM Suppliers AS s INNER JOIN ((Categories AS c INNER JOIN Products AS p ON c.CategoryID = p.CategoryID) INNER JOIN (Employees AS e INNER JOIN (Customers AS m INNER JOIN (Orders AS o INNER JOIN [Order Details] AS d ON (o.OrderID = d.OrderID) AND (o.OrderID = d.OrderID) AND (o.OrderID = d.OrderID)) ON m.CustomerID = o.CustomerID) ON e.EmployeeID = o.EmployeeID) ON p.ProductID = d.ProductID) ON s.SupplierID = p.SupplierID"; this.rdDataSource1.Connect(); this.pivotView1.DataSource = rdDataSource1;
To connect to an unbound data source using code
To connect to an XML data source using code
- In Visual Studio, open the design view of the form containing your PivotView control.
- From the Visual Studio toolbox, drag the XmlDataSource control and drop it onto your form.
- The XmlDataSource control, named XmlDataSource1 by default, appears in a tray below the form, and is selected in the Properties grid.
- Double-click on the PivotView control to open the code view and create the PivotView Load event.
- In the Load event, enter code like the following to set up your data source and connect it to the PivotView control.
Visual Basic.NET code. Paste INSIDE the PivotView Load event. Copy Code Me.XmlDataSource1.DataFile = "C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\Factbook.xml" Me.XmlDataSource1.CustomSchemaFile = "C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\LifeExpectancyByGdpAndLiteracy.schema" Me.XmlDataSource1.QueryString = "//country" Me.XmlDataSource1.TransformationFile = "C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\LifeExpectancyByGdpAndLiteracy.xsl" 'optional Me.XmlDataSource1.Connect() Me.PivotView1.DataSource = XmlDataSource1
C# code. Paste INSIDE the Page Load event. Copy Code this.xmlDataSource1.DataFile = @"C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\Factbook.xml"; this.xmlDataSource1.CustomSchemaFile = @"C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\LifeExpectancyByGdpAndLiteracy.schema"; this.xmlDataSource1.QueryString = @"//country"; this.xmlDataSource1.TransformationFile = @"C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\LifeExpectancyByGdpAndLiteracy.xsl"; //optional this.xmlDataSource1.Connect(); this.pivotView1.DataSource = xmlDataSource1;