Pivot Table Component Configuration via XML

Flexmonster Pivot Table configuration usually is set via configuration XML file (alternative way - via FlashVars). By default it is called config.xml.

Configuration XML file is responsible for the following Pivot table aspects:

1. Data Source Configuration
   1. CSV
   2. OLAP
2. Predefined Report Configuration (Default Data Slice)
   1. Required
      1. Rows
      2. Columns
      3. Values
   2. Optional
      1. Filter Data
      2. Report Filter
3. UI Configuration
   1. CSS
   2. UI Elements Enabling/Disabling
   3. Values Formatting

Flexmonster Pivot Table supports different data sources, such as: CSV files, OLAP, XML from custom web services.

The tag <dataSource> of configuration XML file is responsible for data source configuration.

CSV

To configure CSV data source for Pivot Table you should:

1. Set the property type of <dataSource> tag to CSV:

   <dataSource type="CSV">

2. Set the path to CSV file:

   <dataSource type="CSV">

   <filename>csvdata.csv/data.csv</filename>

   </dataSource>

OLAP

To configure OLAP data source for Pivot Table you should:

1. Set the property type of <dataSource> tag to OLAP:

   <dataSource type="OLAP">

2. Define Microsoft Analysis Services info:

   <dataSource type="OLAP">
   

   <dataSourceInfo>Provider=MSOLAP; Data Source=extranet;</dataSourceInfo>

   </dataSource>

3. Define the catalog name from the dataSource:

   <dataSource type="OLAP">

   <dataSourceInfo>Provider=MSOLAP; Data Source=extranet;</dataSourceInfo>
   <catalog>Adventure Works DW Standard Edition</catalog>

   </dataSource>

4. Define the cube name from the given catalog:

   <dataSource type="OLAP">

   <dataSourceInfo>Provider=MSOLAP; Data Source=extranet;</dataSourceInfo>
   <catalog>Adventure Works DW Standard Edition</catalog>
   <cube>Adventure Works</cube>

   </dataSource>

5. Define the proxyURL — the URL to msmdpump.dll:

   <dataSource type="OLAP">

   <dataSourceInfo>Provider=MSOLAP; Data Source=extranet;</dataSourceInfo>
   <catalog>Adventure Works DW Standard Edition</catalog>
   <cube>Adventure Works</cube>
   <proxyURL>http://10.14.2.102/olap/msmdpump.dll</proxyURL>

   </dataSource>

You can create a predefined report via configuration; define columns, rows, values, and filters to get exact data you need immediately after the Pivot Table is opened. The tag <defaultSlice> of configuration XML file is responsible for this. The structure of <defaultSlice> is the same for both CSV and OLAP data sources.

The <defaultSlice> block has 2 groups of tags:

• <axes> - is responsible for columns, rows, and filters.
• <measures>. - is responsible for values.

<defaultSlice>

<axes>

<axis name="rows">
...
</axis>

<axis name="columns">
...
</axis>

<axis name="pages">
...
</axis>

</axes>

<measures>
...
</measures>

</defaultSlice>

To create the predefined report you have define which fields from your data source you would like to see in rows, which in columns, create a filter for them if needed, and define which values should be shown.

Required

Rows, columns and values are required.

Rows

To define the field for rows, you should add the <hierarchy> block with its description to <axis name="rows">. You can add to rows as much fields as you like. For example, if you want to see in predefined report 2 fields: Country and Color, the <axis name="rows"> block would be the following:

<axis name="rows">

<hierarchy>

<dimensionName>[Country]</dimensionName>
<hierarchyName>[Country].[Country]</hierarchyName>

</hierarchy>

<hierarchy>

<dimensionName>[Color]</dimensionName>
<hierarchyName>[Color].[Color]</hierarchyName>

</hierarchy>

</axis>

Columns

To define the field for columns, you should add the <hierarchy> block with its description to <axis name="columns">. You can add to columns as much fields as you like.

<axis name="columns">

<hierarchy>

<dimensionName>[Category]</dimensionName>
<hierarchyName>[Category].[Category]</hierarchyName>

</hierarchy>

</axis>

Values

Define which values you would like to be shown on the report. You can add several values.

<measures>

<measure>[Measures].[Price]</measure>

<measure>[Measures].[Quantity]</measure>

</measures>

Also, you have to specify where measures should be shown, on rows or on columns. For example, you the following code shows how to add measures to columns:

<axis name="columns">

<hierarchy>

<dimensionName>[Category]</dimensionName>
<hierarchyName>[Category].[Category]</hierarchyName>

</hierarchy>

<hierarchy>

<dimensionName>[Measures]</dimensionName>
<hierarchyName>[Measures]</hierarchyName>

</hierarchy>

</axis>

For CSV data source only.

You can define an aggregation for each value you add by setting aggregation property of <measure> tag. For example,

<measure aggregation="Count">[Measures].[Quantity]</measure>

The following aggregation functions are available for CSV data source:

Optional

Filters are optional.

Filter Data

You can filter data both on rows and on columns in the Pivot Table report. Filters are additive ('AND' filters), and can be applied to any field from rows or columns. Each additional filter reduces the subset of data.

Each <hierarchy> tag can have additional <filter> block which includes all the members of the field which would be included into report.

For example, if you would like to add filter to Color field, you should add <filter> tag, as it is shown below, and add to it all the members which you would like to be shown:

<axis name="rows">

<hierarchy>

<dimensionName>[Color]</dimensionName>
<hierarchyName>[Color].[Color]</hierarchyName>

<filter>

<member>[Color].[Color].[red]</member>
<member>[Color].[Color].[green]</member>

</filter>

</hierarchy>

</axis>

Report Filter

You can configure the report filter. It gives an ability to filter the entire report based on the selected field in the report filter.

To define the field for report filter, you should add the <hierarchy> block with its description to <axis name="pages">. You can add to report filter as much fields as you like. For example, if you want to have predefined report filter on Destination field, the <axis name="pages"> block would be the following:

<axis name="pages">

<hierarchy>

<dimensionName>[Destination]</dimensionName>
<hierarchyName>[Destination].[Destination]</hierarchyName>

</hierarchy>

</axis>

UI configuration block of XML configuration consists of the following 3 parts:

1. <style> - CSS file
2. <controls> - UI elements enabling/disabling
3. <format> - values formatting

CSS

Skins and visual appearance can be controlled with easy-to-edit CSS and images. To define the path to CSS file, <style> tag of XML configuration file is used:

<style source="original.css"/>

To get more information on Pivot Table CSS please read CSS for Pivot Table Skins and Visual Appearance.

UI Elements Enabling/Disabling

You can easily configure UI, define which UI elements are needed and which ones should be turned off using <controls> tag.

You can enable or disable via XML configuration file the following set of visual elements:

• "Fields List" configurator. - Is used to rearrange fields and configure any report on UI with drag-and-drop. Also it can be opened or closed.

  <param name="configurator" opened="true">on</param>

• Pivot Charts. - Pivot Charts can be opened or closed. It is closed by default.

  <param name="charts" opened="false">on</param>

• Print Tool. - Tool for printing a table report or a charts report.

  <param name="print">off</param>

• Full Screen Tool. It is used to enable full screen mode.

  <param name="fullscreen">off</param>

  Note: To use Full-screen mode, flash player should be allowed to use it. More detales in Flash player documentation.

• Zoom Tool. It is used to scale the grid and a Pivot Table report on it.

  <param name="zoom">off</param>

• Styles Selector Tool. ComboBox with list of styles for Pivot.

  <param name="styles">off</param>

• Expand/Collapse All. Expand/collapse headers.

  <param name="expandAll">off</param>

• Excel Like Header Property.

  <param name="excelLikeHeader">off</param>

• Auto Collapse Tool. Auto collapse headers after start or update.

  <param name="autoCollapse">off</param>

For example, Pivot Table component will have "Fields List" configurator opened, ability to switch to Pivot Charts, ability to print Pivot Table and Pivot Charts, enabled full screen tool, disabled zoom tool, no styles combobox, Expand/Collapse All button, no Excel like headers, and headers auto collapsed if XML configuration file will have the following <controls> block:

<controls>

<param name="configurator" opened="true">on</param>
<param name="charts" opened="false">on</param>
<param name="print">on</param>
<param name="fullscreen">on</param>
<param name="zoom">off</param>
<param name="styles">off</param>
<param name="expandAll">on</param>
<param name="excelLikeHeader">off</param>
<param name="autoCollapse">on</param>

</controls>

Values Formatting

To set values formatting the <format type="number"> tag is used. The following parameters of values formatting can be set:

• thousandsSeparator - thousands separator.
• decimalSeparator - decimal separator.
• decimalPlaces - by default it is -1, which means "no formatting"
• maxSymbols - max number of symbols in cell. By default it is 10.
• currencySymbol - if you want format numbers as currency.
• currencySymbolAlign - left or right / by default it is left.

<format type="number">

<param name="thousandsSeparator"><![CDATA[ ]]></param>
<param name="decimalSeparator"><![CDATA[.]]></param>
<param name="decimalPlaces">-1</param>
<param name="maxSymbols">12</param>
<param name="currencySymbol"></param>
<param name="currencySymbolAlign">left</param>

</format>