pnnPTableLight (Pivot Table Light)

  1. Overview.
  2. Control pnnPTableLight.
  3. Client script pnntable.
  4. Installation and using of the pnnPTableLight.
  5. Licence and Trial version.

1. Overview

The component pnnPTableLight designed for display a DHTML pivot table in a user browser. It consist of two parts: first, the server-side COM control (pnnPTableLight.dll); second, the client-side script (pnntable.js).

The pnnPTableLight.dll is a server-side control prepares HTML code for drawing a pivot table.

The script pnntable.js is a client-side JavaScript displays a pivot table in a browser.

Interface of the pnnPTableLight enables drag-n-drop and drill up/down operations over the table.

2. Control pnnPTableLight.

The pnnPTableLight.dll is a ATL COM Control. It prepares HTML client code for drawing a pivot table.

Summary.

Type of the server In-process server
Library where object is implemented pnnPTableLight.dll
Version independent Program ID pnnPTableLight.TableLight
Version dependent Program ID pnnPTableLight.TableLight.1
Threading model Appartment
Interface ITableLight
Type of interface Dual
Aggregation Object can be aggregated
Free thread marshaller Not supported

Methods.

Name of method

Description
DrawTable Retrieves HTML code for drawing a table.

DrawTable(BSTR sXML, BSTR * HTMLCode) 

Retrieves HTML code for drawing a pivot table.

Parameters:
sXML [in] - String containing initial data in XML format.
HTMLCode [out,retval] - String containing result HTML code.

Structure of input XML:

<pnnPTableLight>

  <Filter>
    <Set>
      <Item/> [..n]
    </Set> [..n]
  </Filter> [..n]
  
  <Axes>
    <Axis>
      <Set>
        <Item/> [..n]
      </Set> [..n]
    </Axis> [..n]
  </Axes>
  
  <MDXQuery/>

  <DataArray>
    <Item/> [..n]
  </DataArray>
  
</pnnPTableLight>

Description of XML tags:

Tag Attributes Description
pnnPTableLight   Root tag.
NumberHAxes Number of axes drawing horizontally. Default value is 1.
MDXQuery   MDX query. It is text of
Text Text of a 'TEXTAREA' Element of the pivot table.
Axes   Set of axis of the pivot table.
Axis   Axis of the pivot table.
Filter   Filter table of the pivot table.
Set   Set of items.
DataArray   Cell's values of the pivot table.
Item   Item of the pivot table.
Name Hierarchical name of the item. E.g. [Customers].[USA].[OR]. (Only for 'Filter' and 'Axis' tags).
UniqueName Hierarchical unique name of the item. E.g. [Customers].[USA].[190]. Default value is attribute 'Name' of the item. (Only for 'Filter' and 'Axis' tags).
Flags List of flags for drawing the item. E.g. 'dd' - 'disable drill', hide checkbox for the item. (Only for 'Filter' and 'Axis' tags).
Value Text in the cell. (Only for 'DataArray' tag).
Color Color of text in the cell. It is a numeric value defined in the Client script pnnPTableLight. (Only for 'DataArray' tag).

If you want to create and fill a Pivot Table you have to made the follow:

  1. Specify axes of a pivot pable for drawing table headings. Eath axis consist of a set hierarchical coordinates for data cells. E.g. : <Axis> <Set><Item Name='[Customers].[USA].[OR]'/></Set> ...</Axis>
  2. Specify a data array for filling data cells of a pivot table. The data array contains values for all cells of a pivot table. Cell values for eath rows of a pivot table is placed one after another. 
Example: The follow code create HTML code for drawing a table with two axes:
	XML = "<pnnPTableLight>"
	XML = XML & "<Axes>"
	XML = XML & "<Axis>"
	XML = XML 	& "<Set><Item Name='[Customers].[USA].[OR]'/></Set>"
	XML = XML 	& "<Set><Item Name='[Customers].[Canada]'/></Set>"
	XML = XML & "</Axis>"
	XML = XML & "<Axis>"
	XML = XML 	& "<Set><Item Name='[Measure].[Sales]'/></Set>"
	XML = XML 	& "<Set><Item Name='[Measure].[Profit]'/></Set>"
	XML = XML & "</Axis>"
	XML = XML & "</Axes>"
	XML = XML & "<DataArray>"
	XML = XML 	& "<Item Value='1' Color='1'/>"
	XML = XML 	& "<Item Value='2.11'/>"
	XML = XML 	& "<Item Value='3'/>"
	XML = XML 	& "<Item Value='4.0'/>"
	XML = XML & "</DataArray>"
	XML = XML & "</pnnPTableLight>"

	Code = pnnPTableLight.DrawTable(XML)

Result table:

USA Canada
OR
Sales 1 2.11
Profit 3 4.0

3. Client script pnnPTableLight.

The script pnntable.js is a client-side JavaScript displays a pivot table in a browser.

You can define your own styles of the pnnPTableLight by changing the existing cascading style sheet (CSS/pnnPTableLight.css).

Cascading style sheet:

Class name

Description
clsTDData Style of a data cell.
clsTDDataCurr Style of a current data cell.
clsTable Style of the pivot table.
clsTableFilter Style of the filter table.
clsTDCheck Style of a checked header cell.
clsTDUnCheck Style of a unchecked header cell.
clsTextArea Style of TEXTAREA Element.
clsDragElem Style of a dragged element.
clsDragElemRemove Style of a dragged element(Removing the item).
clsDragElemRotate Style of a dragged element(Rotate axes).
clsDragElemFilter Style of a dragged element(Filter on/off).
clsDragImg Style of a dragged image.
clsTDFilterTitle Style of a title cell of the filter table.
clsTDFilter Style of a data cell of the filter table.

Images: 

File name

Description
images/checkbox.gif Checked checkbox.
images/uncheckbox.gif Unchecked checkbox.
images/filter.gif Filter on.(drag-n-drop).
images/filteroff.gif Filter off.(drag-n-drop).
images/remove.gif Remove item.(drag-n-drop).
images/rotate.gif Rotate axes.(drag-n-drop).

For handling events (drag-n-drop and drill operations) you have to declare two event handler functions: TableDrill and TableDragAndDrop.

function TableDrill(checked,Element) { ...}

 Where: checked - Boolean parameter determines direction

of the action. Values: 0
-
'Drill
  up' operation,
  1 -
'Drill
down'
operation. Element.UNPos - Unique name of the item. Element.NumbInPos - No. position of the
item in a set. Element.Level - Hierarchical level of the element in the item.

For example, a initial XML is:
...
<Set>
  <Item Name='[Customers].[USA].[OR]'/>
  <Item Name='[Customers].[Canada]'/>
</Set>
...
If click on the element 'OR' then Element.NumbInPos = 1 and Element.Level = 3.
If click on the element 'Canada' then Element.NumbInPos = 2 and Element.Level = 2.

function TableDragAndDrop(SrcElem,SrcType,SrcNumbInPos,SrcLevel,DestElem,DestType) {...}

Where:
SrcElem - Unique name of the source item. 
SrcType - Type of the source item.
SrcNumbInPos - No. position of the source item in a set. 
SrcLevel - Hierarchical level of the element in the source item. 
DestElem - Unique name of the destination item. 
DestType - Type of the destination item. 

The possible SrcType and DestType values are as follows: 
'axis' - it is a item of the pivot table. 
'filter' - it is a item of the filter table.
If a destination is not defined then DestType is ''.

4. Installation and using of the pnnPTableLight.

This section describes the steps needed to install of the pnnPTableLight and to embed it to your site.

Preparation:

Embedding:

For embedding the pnnPTableLight in your site you have to add the following code to your ASP page:

...

	<%  dim pnnPTableLight, HTMLCode, XML  
	' Create the pnnPTableLight object 
	set pnnPTableLight = CreateObject("pnnPTableLight.TableLight.1") 
	
	' Build the	XML table query  
	XML	= "<pnnPTableLight>" & ... & "</pnnPTableLight>"

	'Get the result HTML client code
	HTMLCode = pnnPTableLight.DrawTable(XML)
%>
<%=HTMLCode%>
...

Using:

The pnnPTableLight draws a DHTML pivot table and a filter table in a browser. Interface of the pnnPTableLight enables drag-n-drop and drill up/down operations over the table and the filter table on client side. For handling user's events (drag-n-drop and drill operations) you have to declare two event handler functions in your client script: TableDrill and TableDragAndDrop. 

The following steps describe how to handle the drag-n-drop and drill up/down events.

  1. Declare event handler functions in your client script:

    <SCRIPT LANGUAGE = "JavaScript">
function TableDrill(checked,Element){...}
function TableDragAndDrop(SrcElem,SrcType,SrcNumbInPos,SrcLevel,DestElem,DestType){...}
</SCRIPT>
  2. Add a form object to the HTML code. The form object contains hidden fields for storage the event data. The event data describes each instance of an event as it occurs. 
    				
<form id='form1' method='POST' action='pnntable.asp'>
	<p><input type='hidden' name='Operation' ID="Hidden1"></p>
	<p><input type='hidden' name='UN' ID="Hidden2"></p>
	<p><input type='hidden' name='NumbInPos' ID="Hidden3"></p>
	<p><input type='hidden' name='Level' ID="Hidden4"></p>
</form>
  3. Add code to your event handler functions that will send the event data to the server. The following example illustrates how to sent the event data to the server by POST method. 
    function TableDrill(checked,Element)
{
	// Determine type of the drill operation
	if (checked) window.form1.Operation.value = "Drill Down"
	else window.form1.Operation.value = "Drill Up";
	
	// Fill in form's elements
	window.form1.UN.value = Element.UNPos;
	window.form1.NumbInPos.value = Element.NumbInPos;
	window.form1.Level.value = Element.Level;
	
	// Send the data to the server using POST method
	window.form1.submit();
}
function TableDragAndDrop(SrcElem,SrcType,SrcNumbInPos,SrcLevel,DestElem,DestType)
{
	// Fill in form's elements
	window.form1.Operation.value = "Drag&Drop";
	window.form1.UN.value = "Source = '" + SrcElem;
	window.form1.UN.value += "' (Type = '" + SrcType;
	window.form1.UN.value += "') Destination = '" + DestElem;
	window.form1.UN.value += "' (Type = '" + DestType + "')";
	window.form1.NumbInPos.value = SrcNumbInPos;
	window.form1.Level.value = SrcLevel;

	// Send the data to the server using POST method
	window.form1.submit();
  4. Add code to your server script that will process the event data and will rebuild the XML table query if necessary. The following example illustrates how to process the event data on a server side. 
    <%
if Len(Request.Form("Operation")) > 0 then
	' If it is drag-n-drop or drill up/down operation 
	' then get the event data from fields of the form object
	Operation = Request.Form("Operation")
	UN=Request.Form("UN")
	NumbInPos=Request.Form("NumbInPos")
	Level=Request.Form("Level")%>
	
	' Process the event data
	...
	
	' Rebuild XML table query if necessary
	XML	= "<pnnPTableLight>" & ... & "</pnnPTableLight>"
end if

'Get the result HTML client code
HTMLCode = pnnPTableLight.DrawTable(XML)

%>

The sample pnnPTableLight_demo demonstrates the use and capabilities of the pnnPTableLight programming interfaces.

5. Licence and Trial version

The pnnPTableLight control looks for a license file named license.crt. This file must be in the same directory as the pnnPTableLight.dll file that contains the component.

The trial version of the component has following limits:

If this limits was exceeded then the component will write string 'Trial' to corresponding data cells of the result pivot table.

The licensed version of the component has no this limits.