eXPivot - FAQ
Exontrol.COM Software - Frequently Asked Questions - ExPivot Component
1:
The control's release notes can be found on our web site, looking for the Release Notes column in the control's main page. Click here for direct link.
2:
Yes, the Exontrol ExPrint component ( exprint.dll ) provides Print and Print Preview capabilities for the exPivot component. Once that you can have the exPrint component in your Components list, insert a new instance of "ExPrint 1.0 Control Library" to your form and add the following code:
Private Sub Command1_Click()
    With Print1
        Set .PrintExt = Pivot1.Object
        .Preview
    End With
End Sub

The Exontrol Print Preview mainframe looks like follows:

The exPrint component is free of charge, if you are registered user of the exPivot component.

The following VB sample opens the Print Preview frame:

With Print1
    Set .PrintExt = Pivot1.Object
    .Preview
End With

The following C++ sample opens the Print Preview frame:

m_print.SetPrintExt( m_pivot.GetControlUnknown() );
m_print.Preview();

The following VB.NET sample opens the Print Preview frame:

With AxPrint1
    .PrintExt = AxPivot1.GetOcx()
    .Preview()
End With

The following C# sample opens the Print Preview frame:

axPrint1.PrintExt = axPivot1.GetOcx();
axPrint1.Preview();

The following VFP sample opens the Print Preview frame:

with thisform.Print1.Object
    .PrintExt = thisform.Pivot1.Object
    .Preview()
endwith
3:
The Exontrol ExPrint component ( exprint.dll ) provides Print and Print Preview capabilities for the Exontrol ExPivot component.

The requirements for the FitToPage option:

  • Exontrol.ExPrint version 5.2 ( or greater )
  • Exontrol.ExPivot version 7.0 ( or greater )

If these are not meet, the Options("FitToPage") property has NO effect.

The FitToPage option could be one of the following:

  • On, (Fit-To-Page) the control's content is printed to a single page ( version 7.0 )
  • p%, (Adjust-To) where p is a positive number that indicates the percent from normal size to adjust to. For instance, the "FitToPage = 50%" adjusts the control's content to 50% from normal size. ( version 10.0 )
  • w x, (Fit-To Wide) where w is a positive number that indicates that the control's content fits w pages wide by how many pages tall are required. For instance, "FitToPage = 3 x" fits the control's content to 3 pages wide by how many pages tall is are required. ( version 10.0 )
  • x t, (Fit-To Tall) where t is a positive number that specifies that the control's content fits t pages tall by how many pages wide are required. For instance, "FitToPage = x 2" fits the control's content to 2 pages tall by how many pages wide are required. ( version 10.0 )
  • w x t, (Fit-To) where w and t are positive numbers that specifies that the control's content fits w pages wide by t pages tall. For instance, "FitToPage = 3 x 2" fits the control's content to 3 pages wide by 2 pages tall. ( version 10.0 )

The following VB6 sample shows how to show the eXPivot/COM's content to one page when print or print preview the component:

Private Sub Command1_Click()
    With Print1
        .Options = "FitToPage = On"
        Set .PrintExt = Pivot1.Object
        .Preview
    End With
End Sub

The following VB/NET sample shows how to show the eXPivot/NET or /WPF's content to one page when print or print preview the component:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    With Exprint1
        .Options = "FitToPage = On"
        .PrintExt = Expivot1
        .Preview()
    End With
End Sub
4:
The control's Layout property indicates a value to restore the control's layout as previously saved. The Layout includes the column positions, sorting, size, filtering and so on.

For instance, the Layout term is defined as:

  • as an event, in the /NET framework, (Control.Layout Event)
  • as a property in MS Access, of the extended control. 

In order to use the control's Layout property and not confused with the system's Layout event or property you need to cast the object such as:

  • VB, VBA, MS Access, Pivot1.Object.Layout instead Pivot1.Layout
  • C# for /COM, (axPivot1.GetOcx() as EXPIVOTLib.IPivot).Layout, instead  axPivot1.Layout. The /COM version on /NET provides the CtlLayout, which is the control's Layout property, so it is correct to be used too as axPivot1.CtlLayout
  • VB/NET for /COM, AxPivot1.GetOcx().Layout instead AxPivot1.Layout. The /COM version on /NET provides the CtlLayout, which is the control's Layout property, so it is correct to be used too as AxPivot1.CtlLayout.
5:
The control's Import method imports data to the control. It may be confused with other methods, properties or events named the same.  

In order to use the control's Import method and not be confused other method of the same name you can do:

  • C# for /COM, The /COM version on /NET provides the CtlImport, which is the control's Import method, so it is correct to be used too as axPivot1.CtlImport. The same way, you can get the Import call if using such as   (axPivot1.GetOcx() as EXPIVOTLib.IPivot).Import, instead  axPivot1.Import.
  • VB/NET for /COM, The /COM version on /NET provides the CtlImport, which is the control's Import method, so it is correct to be used too as AxPivot1.CtlImport.The same way, you can get the Import call if using such as  AxPivot1.GetOcx().Import instead AxPivot1.Import.
6:
Yes. The control's Import method imports data to the control. The Source parameter of the Import method can be:
  • A String expression that indicates the path to a file to be loaded or the content itself. If the expression points to a file, the file's content is loaded.The Import method loads CSV files or CSV content. A comma-separated values (CSV) file stores tabular data (numbers and text) in plain-text form. Plain text means that the file is a sequence of characters, with no data that has to be interpreted instead, as binary numbers. A CSV file consists of any number of records, separated by line breaks of some kind; each record consists of fields, separated by some other character or string, most commonly a literal comma or tab. Usually, all records have an identical sequence of fields. The Source parameter indicates the path to the CSV file to be loaded, or the content itself. For instance, the Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"  imports the data.txt file.
  • A Safe array of one-dimensional or two-dimension type, which indicates the data to be loaded. The safe array must be with 1 or 2 dimensions, else an error occurs. If the Source parameter refers a one-dimensional array, the content of the array indicates the values of the rows. If the Source parameter refers a two-dimensional array, the first dimension indicates the columns, while the second indicates the rows. For instance, in VB6 you can load data from a safe array using the syntax Import Array(1, 1, 2, 2, 3, 3, 4, 4). Where Array is a method of VB6 that creates a safe array of different values. The same way you can have the declaration Dim data(1,99) as String, and call the Import data, will load a two-dimensional array. The sample loads 2 columns, and 100 rows from the data array.

Here's a few samples of using Import method with arrays, using the /COM version:

  • VB,VBA: .Import (Array(1, 2, 3, 4, 5))
  • VB/NET: .GetOcx().Import(New Integer() {1, 2, 3, 4, 5})
  • C#: .Import(new int[] { 1, 2, 3, 4, 5 }, null);

Here's a few samples of using Import method with arrays, using the /NET version:

  • VB/NET: .Import(New Integer() {1, 2, 3, 4, 5})
  • C#: .Import(new int[] { 1, 2, 3, 4, 5 });;
7:
By default, the control is keeping the layout (size, position, sorting order, ... ) of the generated columns when the user makes changes in the control's pivot bar. In other words, if you include a new value in the column's filter, the new generated columns will be appended at the end of the header list. In order to prevent this, you need to handle the LayoutEndChanging event when exPivotDataColumnSort and exPivotDataColumnFilterChange notifications occur, and call the control's Reset method as in the following sample:
Private Sub Pivot1_LayoutEndChanging(ByVal Operation As EXPIVOTLibCtl.LayoutChangingEnum)
    If (Operation = exPivotDataColumnSort) Or (Operation = exPivotDataColumnFilterChange) Then
        Pivot1.Reset "c*.position*"
    End If
End Sub

 The sample resets the position of the generated columns when exPivotDataColumnSort and exPivotDataColumnFilterChange notifications occur.

8:
This issue may occur when the control displays a hierarchy ( the control is grouping multiple columns, or PivotRows property contains multiple columns ) By default, the control's FilterInclude property is set on exItemsWithChilds + exMatchIncludeParent. Because the FilterInclude property includes the exMatchIncludeParent flag, it makes the control to include the parent with childs, so more items appear to be included not only the matching items. 

You can do one of the following:

  • Change the FilterInclude property to exItemsWithoutChilds, and so just the items that match the filtering will be included in the control's list ( including the parent items ).
  • Set the FilterInclude property to exMatchingItemsOnly flag, and so no parent/child items are displayed, and so the control displays just the items that match the criteria. This option hides the parent or child items that does not match the criteria.
9:
If using a different Font, make sure that you are updating the DefaultItemHeight property as well, which indicates the default height for the items to be shown in the control's list. The DefaultItemHeight property is not depending on the Font property.
10:

The number of records the control can handle depends on the architecture of the application (32-bit or x64) and installed memory (RAM). In order to calculate a sum, a max, or a count, the control requires to load the entire data into the memory. There is no virtual way. Why? Let's say you need to get the sum of the entire column? You bet you need to know the value for each field within the column. Otherwise, the sum is impossible to compute.

A 32-bit system can address a maximum of 4 GB (4,294,967,296 bytes) of RAM. A 64-bit register can theoretically reference 18,446,744,073,709,551,616 bytes, or 17,179,869,184 GB (16 exabytes) of memory. This is several million times more than an average workstation would need to access. What's important is that a 64-bit computer (which means it has a 64-bit processor) can access more than 4 GB of RAM. If a computer has 8 GB of RAM, it better has a 64-bit processor. Otherwise, at least 4 GB of the memory will be inaccessible by the CPU.

Back to the control. As a table comes with records, you need to know that also the number of column is important, as there is a difference in occupied memory between one column to more columns. For instance, the memory required to load 1,000 records with 1 column is not the same as 4 or more columns.

We have created a basic x-script sample to load different number of records from 250,000 to more than 4,000,000 records, with 14 columns, using 32 and 64 bit versions of the control, on a x64 Windows machine with 8 GB of RAM. In order to run following sample you need to have installed: MS Access, eXPivot and eXHelper. If you have a different ADO connection (SQL Server, MySQL, Oracle, Internet Publishing, ...) you need to change the first ("Orders") and second ("Provider=...") parameters of the Open command. Click here to see how you can do on your machine.

The x-script code is (copy and paste the following code to eXHelper for eXPivot control):

BeginUpdate
Dim rs
rs = CreateObject("ADOR.Recordset")
{
  Open("Orders","Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:/Working/sample(250K).accdb", 3, 3 )
}
DataSource = rs
PivotRows = "1"
PivotColumns = "sum(7)[content=numeric],count(7)"
PivotTotals = "sum[bold]"
EndUpdate

The sample loads the Orders table, group by CustomerID and displays the sum and count of Freight column. You can download the sample(250K).accdb, sample(500K).accdb, sample(1M).accdb, sample(2M).accdb and sample(4M).accdb here.

All accdb samples runs fine on eXHelper(32-bit) and eXHelper(64-bit) on Windows OS 8GB. During executing you will notice that the control requires a lot of memory to load the data, but once the data is loaded the memory get stabilized at a reasonable amount. The time to load/run the sample varies from 4 seconds (14 x 250,000) to 1 minute (14 x 4,000,000) and it depends on the performance of the computer you are running (Processor Intel(R) Core(TM) i7-6700 CPU @ 3.40GHz, 3401 Mhz, 4 Core(s), 8 Logical Processor(s), 8.00 GB). Please be aware that the size on disk of your database is not equivalent with the memory require to load the entire data-source.

Here's the result:

The memory usage varies from to 115 MB (14 x 250,000) to 1.2 GB (14 x 4,000,000). The size on disk varies from 43 MB (14 x 250,000) to 640 MB (14 x 4,000,000).

While trying to run the sample you can get any of the following exceptions:

0x80004005: Path not is not valid

change the path from E:/Working/ to your valid path

0x800A0E7A: Provider cannot be found

install MS Access 32 or 64 based on what type of eXHelper you run

0x80020006: Unknown name

no ADO is installed on your machine, so you need to install it

The x-script code can be converted to VB, C++, VFP, Delphi, ... using the eXHelper tool. If you convert the code to other programming languages, please consider that each environment requires its own memory to run, so you mat encounter any of this:

  • Run-time error: '-2145417848 (80010108) Method 'DataSource'' of object IPivot failed
  • OLE exception error: Exception code e06d7363. OLE object matt be corrupt

due insufficient memory, and we would recommend the following tips:

  • compile the build the application to EXE to run, rather than running the sample within its environment
  • do not run the application in debug mode
  • use x64 version
  • reduce the number of columns
  • do not change DisplayPivotRows, DisplayPivotFields, DisplayPivotData properties (these properties do not affect the number of items / rows to be loaded from the DATA you provide, it just adjusts the number of rows/field to be displayed on the control's (generated) list)

and use the Task manager (or any other tool) to monitor the memory usage for the application.

Finally, please pay attention that the evaluation version of the control limits (from time to time) the number of records the control loads.

How-To Questions
General Questions