Pivot.DataSource Property (ExPivot)

The DataSource property may be used to load data from a table or a record set using the ADO or DAO recordsets. Microsoft's ActiveX Data Objects (ADO) is a set of Component Object Model (COM) objects for accessing data sources. In computer software, a data access object (DAO) is an object that provides an abstract interface to some type of database or other persistence mechanism. By mapping application calls to the persistence layer, DAOs provide some specific data operations without exposing details of the database. The DataColumns property accesses the control's Columns collection, so you can rename or specify the column's type once the control's data is loaded. The PivotColumnsFloatBarVisible property specifies whether the Columns collection is displayed to a floating bar, so user can drag and drop columns to the control's pivot bar so it gets data summarized. The ClearData method clears the control's data. Use the DisplayPivotData property to specify the number of rows to be displayed on the control's list. The LoadHeadersOnly property loads the headers only, so no data is loaded.

The control can load data using one of the following methods:

Import method, loads data from a CSV file or from specified text.
DataSource property, assigns an ADO/DAO record set to be loaded.
LoadXML method, loads an XML file previously saved using the SaveXML method
The user can drag and drop any TXT or XML files to the control.

The AppendData method appends data to the control (prevents clearing data already loaded).

The following properties may be used to group and summarize the data, once it is loaded:

PivotRows property specifies the list of DATA columns that determines the first column in the control's list. In other words, the Group-By columns
PivotColumns property specifies the list of DATA columns that determines the rest of the columns to be displayed on the control's list
PivotTotals property specifies the list of total/sub-total functions to be displayed on the control's list.

Is it possible to load data from a data source?

VBA (MS Access, ...)

With Pivot1
	.DataSource = CurrentDb.OpenRecordset("Data")
End With

VB6

With Pivot1
	Set rs = CreateObject("ADOR.Recordset")
	With rs
		.Open "Data","Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\Exontrol\ExPivot\Sample\Access2007\sample.accdb",3,3
	End With
	.DataSource = rs
End With

VB.NET

Dim rs
With Expivot1
	rs = New ADODB.Recordset()
	With rs
		.Open("Data","Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\Exontrol\ExPivot\Sample\Access2007\sample.accdb",3,3)
	End With
	.DataSource = rs
End With

VB.NET for /COM

Dim rs
With AxPivot1
	rs = CreateObject("ADOR.Recordset")
	With rs
		.Open("Data","Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\Exontrol\ExPivot\Sample\Access2007\sample.accdb",3,3)
	End With
	.DataSource = rs
End With

C++

/*
	Copy and paste the following directives to your header file as
	it defines the namespace 'EXPIVOTLib' for the library: 'ExPivot 1.0 Control Library'

	#import <ExPivot.dll>
	using namespace EXPIVOTLib;
*/
EXPIVOTLib::IPivotPtr spPivot1 = GetDlgItem(IDC_PIVOT1)->GetControlUnknown();
/*
	Includes the definition for CreateObject function like follows:

	#include <comdef.h>
	IUnknownPtr CreateObject( BSTR Object )
	{
		IUnknownPtr spResult;
		spResult.CreateInstance( Object );
		return spResult;
	};

*/
/*
	Copy and paste the following directives to your header file as
	it defines the namespace 'ADODB' for the library: 'Microsoft ActiveX Data Objects 6.0 Library'

	#import <msado15.dll> rename("EOF","REOF")
*/
ADODB::_RecordsetPtr rs = ::CreateObject(L"ADOR.Recordset");
	rs->Open("Data",_bstr_t("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Program Files\\Exontrol\\ExPivot\\Sample\\Access2007\\sample.accdb",ADODB::adOpenStatic,ADODB::adLockOptimistic,0);
spPivot1->PutDataSource(((ADODB::_RecordsetPtr)(rs)));

C++ Builder

/*
	Select the Component\Import Component...\Import a Type Library,
	to import the following Type Library:

		Microsoft ActiveX Data Objects 6.0 Library

	TypeLib: C:\Program Files\Common Files\System\ado\msado15.dll

	to define the namespace: Adodb_tlb
*/
//#include "ADODB_TLB.h"
Adodb_tlb::_RecordsetPtr rs = Variant::CreateObject(L"ADOR.Recordset");
	rs->Open(TVariant("Data"),TVariant(String("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Program Files\\Exontrol\\ExPivot\\Sample\\Access2007\\sample.accdb"),Adodb_tlb::CursorTypeEnum::adOpenStatic,Adodb_tlb::LockTypeEnum::adLockOptimistic,0);
Pivot1->DataSource = (IDispatch*)rs;

// Add 'Microsoft ActiveX Data Objects 6.0 Library' reference to your project.
ADODB.Recordset rs = new ADODB.Recordset();
	rs.Open("Data","Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Program Files\\Exontrol\\ExPivot\\Sample\\Access2007\\sample.accdb",exontrol.ADODB.CursorTypeEnum.adOpenStatic,exontrol.ADODB.LockTypeEnum.adLockOptimistic,0);
expivot1.DataSource = (rs as ADODB.Recordset);

JavaScript

<OBJECT classid="clsid:5C9DF3D3-81B1-42C4-BED6-658F17748686" id="Pivot1"></OBJECT>

<SCRIPT LANGUAGE="JScript">
	var rs = new ActiveXObject("ADOR.Recordset");
		rs.Open("Data","Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Program Files\\Exontrol\\ExPivot\\Sample\\Access2007\\sample.accdb",3,3,null);
	Pivot1.DataSource = rs;
</SCRIPT>

C# for /COM

// Add 'Microsoft ActiveX Data Objects 6.0 Library' reference to your project.
ADODB.Recordset rs = new ADODB.Recordset();
	rs.Open("Data","Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Program Files\\Exontrol\\ExPivot\\Sample\\Access2007\\sample.accdb",ADODB.CursorTypeEnum.adOpenStatic,ADODB.LockTypeEnum.adLockOptimistic,0);
axPivot1.DataSource = (rs as ADODB.Recordset);

X++ (Dynamics Ax 2009)

public void init()
{
	anytype rs;
	str var_s;
	;

	super();

	// Add 'Microsoft ActiveX Data Objects 6.0 Library' reference to your project.
	rs = COM::createFromObject(new ADODB.Recordset()); rs = rs;
		var_s = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Program Files\\Exontrol\\ExPivot\\Sample\\Access2007\\sample.accdb";
		rs.Open("Data",COMVariant::createFromStr(var_s),3/*adOpenStatic*/,3/*adLockOptimistic*/);
	expivot1.DataSource(rs);
}

Delphi 8 (.NET only)

with AxPivot1 do
begin
	rs := (ComObj.CreateComObject(ComObj.ProgIDToClassID('ADOR.Recordset')) as ADODB.Recordset);
	with rs do
	begin
		Open('Data','Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\Exontrol\ExPivot\Sample\Access2007\sample.accdb',3,3,Nil);
	end;
	DataSource := (rs as ADODB.Recordset);
end

Delphi (standard)

with Pivot1 do
begin
	rs := (IUnknown(ComObj.CreateComObject(ComObj.ProgIDToClassID('ADOR.Recordset'))) as ADODB_TLB.Recordset);
	with rs do
	begin
		Open('Data','Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\Exontrol\ExPivot\Sample\Access2007\sample.accdb',3,3,Null);
	end;
	DataSource := (IUnknown(rs) as ADODB_TLB.Recordset);
end

VFP

with thisform.Pivot1
	rs = CreateObject("ADOR.Recordset")
	with rs
		var_s = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\Exontrol\ExPivot\Sample\Access2007\sample.accdb"
		.Open("Data",var_s,3,3)
	endwith
	.DataSource = rs
endwith

dBASE Plus

local oPivot,rs

oPivot = form.Activex1.nativeObject
rs = new OleAutoClient("ADOR.Recordset")
	rs.Open("Data","Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\Exontrol\ExPivot\Sample\Access2007\sample.accdb",3,3)
oPivot.DataSource = rs

XBasic (Alpha Five)

Dim oPivot as P
Dim rs as P

oPivot = topparent:CONTROL_ACTIVEX1.activex
rs = OLE.Create("ADOR.Recordset")
	rs.Open("Data","Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\Exontrol\ExPivot\Sample\Access2007\sample.accdb",3,3)
oPivot.DataSource = rs

Visual Objects

local rs as _Recordset

// Generate Source for 'Microsoft ActiveX Data Objects 6.0 Library' server from Tools\Automation Server...
rs := _Recordset{"ADOR.Recordset"}
	rs:Open("Data","Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\Exontrol\ExPivot\Sample\Access2007\sample.accdb",3,3,0)
oDCOCX_Exontrol1:DataSource := _Recordset{rs}

PowerBuilder

OleObject oPivot,rs

oPivot = ole_1.Object
rs = CREATE OLEObject
rs.ConnectToNewObject("ADOR.Recordset")
	rs.Open("Data","Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\Exontrol\ExPivot\Sample\Access2007\sample.accdb",3,3)
oPivot.DataSource = rs

		Type	Description
		Object	An Object that defines the control's data. Currently, the control accepts ADO.Recordset, ADODB.Recordset objects, DAO recordset.