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 Web form, setting its properties, and setting the DataSourceID property of the PivotView control to the name of the data source control. You can also connect to any of these data sources in code.
Code-Free Methods
To connect to an ActiveAnalysis Local cube without code
- In Visual Studio, open the design view of the Web form containing your PivotView control.
- From the Visual Studio toolbox, drag the LocalCubeDataSource control and drop it onto the form below the PivotView.
- The LocalCubeDataSource control, named LocalCubeDataSource1 by default, appears below the PivotView, 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 - Select the PivotView control in the Properties grid, and in the DataSourceID 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 Web form containing your PivotView control.
- From the Visual Studio toolbox, drag the MdxDataSource control and drop it onto your Web form below the PivotView.
- The MdxDataSource control, named MdxDataSource1 by default, appears below the PivotView, 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.
- Select the PivotView control in the Properties grid, and in the DataSourceID property, select MdxDataSource1.
To connect to a relational data (RD) source without code
- In Visual Studio, open the design view of the Web form containing your PivotView control.
- From the Visual Studio toolbox, drag the RdDataSource control and drop it onto your Web form below the PivotView.
- The RdDataSource control, named RdDataSource1 by default, appears below the PivotView, 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 - 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 Web form containing your PivotView control.
- From the Visual Studio toolbox, drag the XmlDataSource control and drop it onto your Web form below the PivotView.
- The XmlDataSource control, named XmlDataSource1 by default, appears below the PivotView, 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 schema file, or paste in the following path to the sample.
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 - Select the PivotView control in the Properties grid, and in the DataSourceID property, select XmlDataSource1.
Code Methods
To connect to an ActiveAnalysis Local cube using code
- In Visual Studio, open the design view of the Web form containing your PivotView control.
- To configure the data source, right click on Default.aspx and open the Default.aspx.cs or Default.aspx.vb file.
- At the top of the code view, add code like the following to reference namespaces used in the code below:
Visual Basic.NET Code Copy Code Imports GrapeCity.ActiveAnalysis
Imports GrapeCity.ActiveAnalysis.DataSourcesC# Code Copy Code using GrapeCity.ActiveAnalysis;
using GrapeCity.ActiveAnalysis.DataSources; - In the Page_Load event, enter the following code to set up your data source and connect it to PivotView control.
Visual Basic.NET Code Copy Code Dim myDS As New LocalCubeDataSource()
myDS.LocalCubeFile = "C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindLocalCube.ddacube"
myDS.Connect()
pivotView1.DataSource = myDSC# Code Copy Code var myDS = new LocalCubeDataSource();
myDS.LocalCubeFile = @"C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindLocalCube.ddacube";
myDS.Connect();
PivotView1.DataSource = myDS
To connect to a SQL Server Analysis Services (MDX) data cube using code
- In Visual Studio, open the design view of the Web form containing your PivotView control.
- To configure the data source, right click on Default.aspx and open the Default.aspx.cs or Default.aspx.vb file.
- At the top of the code view, add code like the following to reference namespaces used in the code below:
Visual Basic.NET Code Copy Code Imports GrapeCity.ActiveAnalysis
Imports GrapeCity.ActiveAnalysis.DataSourcesC# Code Copy Code using GrapeCity.ActiveAnalysis;
using GrapeCity.ActiveAnalysis.DataSources; - In the Page_Load event, enter the following code to set up your data source and connect it to PivotView control.
Visual Basic.NET Code Copy Code Dim myDS = New MdxDataSource()
myDS.ConnectionString = "Data Source=localhost;Catalog=Adventure Works DW;"
myDS.Cube = "Mined Customers" 'optional
myDS.Connect()
pivotView1.DataSource = myDSC# Code Copy Code var myDS = new MdxDataSource();
myDS.ConnectionString = "Data Source=localhost;Catalog=Adventure Works DW;";
myDS.Cube = "Mined Customers"; //optional
myDS.Connect();
PivotView1.DataSource = myDS;
To connect to a relational data (RD) source using code
- In Visual Studio, open the design view of the Web form containing your PivotView control.
- To configure the data source, right click on Default.aspx and open the Default.aspx.cs or Default.aspx.vb file.
- At the top of the code view, add code like the following to reference namespaces used in the code below:
Visual Basic.NET Code Copy Code Imports GrapeCity.ActiveAnalysis
Imports GrapeCity.ActiveAnalysis.DataSourcesC# Code Copy Code using GrapeCity.ActiveAnalysis;
using GrapeCity.ActiveAnalysis.DataSources; - In the Page_Load event, enter the following code to set up your data source and connect it to PivotView control.
Visual Basic.NET Code Copy Code Dim myDS = New RdDataSource()
myDS.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\NWind\NWind.mdb;"
myDS.ConnectionType = GrapeCity.ActiveAnalysis.DataSources.ConnectionType.OleDb
myDS.CustomSchemaFile = "C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindMDB-SalesProductsSuppliers.schema"
myDS.QueryString = "SELECT c.CategoryName, p.ProductName, o.OrderID, o.OrderDate, d.UnitPrice, d.Quantity, s.Country AS SupplierCountry, s.City AS SupplierCity, " & ControlChars.CrLf & "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 " & ControlChars.CrLf & "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, " & ControlChars.CrLf & "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) " & ControlChars.CrLf & "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 " & ControlChars.CrLf & "(o.OrderID = d.OrderID)) ON m.CustomerID = o.CustomerID) ON e.EmployeeID = o.EmployeeID) ON p.ProductID = d.ProductID) ON s.SupplierID = p.SupplierID"
myDS.Connect()
pivotView1.DataSource = myDSC# Code Copy Code var myDS = new RdDataSource();
myDS.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CC:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\NWind\NWind.mdb;";
myDS.ConnectionType = GrapeCity.ActiveAnalysis.DataSources.ConnectionType.OleDb;
myDS.CustomSchemaFile = @"C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\NWind\NwindMDB-SalesProductsSuppliers.schema";
myDS.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";
myDS.Connect();
PivotView1.DataSource = myDS;
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 Web form containing your PivotView control.
- To configure the data source, open the Default.aspx.cs or Default.aspx.vb file by double clicking the PivotView Control.
- At the top of the code view, add code like the following to reference namespaces used in the code below:
Visual Basic.NET Code Copy Code Imports GrapeCity.ActiveAnalysis
Imports GrapeCity.ActiveAnalysis.DataSourcesC# Code Copy Code using GrapeCity.ActiveAnalysis;
using GrapeCity.ActiveAnalysis.DataSources; - In the Page_Load event, enter the the following code to set up your data source and connectit to PivotView control.
Visual Basic.NET Code Copy Code Dim myDS = New GrapeCity.ActiveAnalysis.DataSources.XmlDataSource()
myDS.DataFile = "C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\Factbook.xml"
myDS.CustomSchemaFile = "C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\LifeExpectancyByGdpAndLiteracy.schema"
myDS.QueryString = "//country"
myDS.TransformationFile = "C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\LifeExpectancyByGdpAndLiteracy.xsl" 'optional
myDS.Connect()
pivotView1.DataSource = myDSC# Code Copy Code var myDS = new GrapeCity.ActiveAnalysis.DataSources.XmlDataSource();
myDS.DataFile = @"C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\Factbook.xml";
myDS.CustomSchemaFile = @"C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\LifeExpectancyByGdpAndLiteracy.schema";
myDS.QueryString = @"//country";
myDS.TransformationFile = @"C:\Users\Public\Documents\GrapeCity\ActiveAnalysis\DataSources\Factbook\LifeExpectancyByGdpAndLiteracy.xsl"; //optional
myDS.Connect();
PivotView1.DataSource = myDS;