Pivot Table Component Configuration via XML

Flexmonster Pivot Table and Charts Component has a lot of configuration options. It can be configured via configuration XML file. By default it is called config.xml.

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

Data Source Configuration
1. CSV
2. Mondrian
3. Microsoft Analysis Services
Predefined Report Configuration (Default Data Slice)
1. Required
  1. Rows
  2. Columns
  3. Values
2. Optional
  1. Report Filter
  2. Aggregations for Values
  3. Filter Data
UI Configuration
1. CSS
2. UI Elements Enabling/Disabling
3. Values Formatting

Data Source Configuration

Flexmonster Pivot Table supports different data sources, such as: CSV, Mondrian, and Microsoft Analysis Services.

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

CSV

To connect Pivot Table component to your CSV data source, you should accomplish the following steps:

Set the property type of <dataSource> tag to CSV in XML configuration file:
<config>
...
<dataSource type="CSV">
</dataSource>
...
</config>
Set the path to CSV data source in configuration file. It can be CSV file or path to the service which returns CSV:
<config>
...
  <dataSource type="CSV">
    <filename>data/data.csv</filename>
  </dataSource>
...
</config>

Mondrian

To connect Pivot Table component to Mondrian, you should accomplish the following steps:

Set the property type of <dataSource> tag to Mondrian:
<config>
...
<dataSource type="Mondrian">
</dataSource>
...
</config>
Define the service info:
<config>
...
  <dataSource type="Mondrian">
    <dataSourceInfo>Provider=Mondrian;DataSource=MondrianFoodMart;</dataSourceInfo>
  </dataSource>
...
</config>
Define the data source catalog name:
<config>
...
  <dataSource type="Mondrian">
    <dataSourceInfo>Provider=Mondrian;DataSource=MondrianFoodMart;</dataSourceInfo>
    <catalog>FoodMart</catalog>
  </dataSource>
...
</config>
Define the cube name from the given catalog:
<config>
...
  <dataSource type="Mondrian">
    <dataSourceInfo>Provider=Mondrian;DataSource=MondrianFoodMart;</dataSourceInfo>
    <catalog>FoodMart</catalog>
    <cube>Sales</cube>
  </dataSource>
...
</config>
Define the proxy URL:
<config>
...
  <dataSource type="Mondrian">
    <dataSourceInfo>Provider=Mondrian;DataSource=MondrianFoodMart;</dataSourceInfo>
    <catalog>FoodMart</catalog>
    <cube>Sales</cube>
    <proxyUrl>http://extranetold:8080/mondrian/xmla</proxyUrl>
  </dataSource>
...
</config>

Microsoft Analysis Services

To connect Pivot Table component to Microsoft Analysis Services, you should accomplish the following steps:

Set the property type of <dataSource> tag to MSOLAP:
<config>
...
<dataSource type="MSOLAP">
</dataSource>
...
</config>
Define the service info:
<config>
...
  <dataSource type="MSOLAP">
    <dataSourceInfo>Provider=MSOLAP; Data Source=extranet;</dataSourceInfo>
  </dataSource>
...
</config>
Define the data source catalog name:
<config>
...
  <dataSource type="MSOLAP">
    <dataSourceInfo>Provider=MSOLAP; Data Source=extranet;</dataSourceInfo>
    <catalog>Adventure Works DW Standard Edition</catalog>
  </dataSource>
...
</config>
Define the cube name from the given catalog:
<config>
...
  <dataSource type="MSOLAP">
    <dataSourceInfo>Provider=MSOLAP; Data Source=extranet;</dataSourceInfo>
    <catalog>Adventure Works DW Standard Edition</catalog>
    <cube>Adventure Works</cube>
  </dataSource>
...
</config>
Define the proxy URL — the URL to msmdpump.dll:
<config>
...
  <dataSource type="MSOLAP">
    <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>
...
</config>

Predefined Report Configuration

You can create a predefined report via XML 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 CSV and OLAP data sources.

The <defaultSlice> block has 2 groups of tags:

<axes> - is responsible for columns, rows, pages and filter.
<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>

Optional

Report filter, aggregations for values and filter data are optional.

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>

Aggregations for Values

This option is available only for CSV data sources.

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:

Function	Summarizes
Sum	The sum of the values. This is the default function for numeric data.
Count	The number of data values
Average	The average of the values
Max	The largest value
Min	The smallest value
Product	The product of the values
Percent	Values in percent

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>

UI Configuration

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

<style> - CSS file
<params> - UI elements and tools enabling/disabling
<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 <params> tag.

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

configuratorActive parameter defines whether Fields List should be active (opened) after initialization or not. This property is off by default.
<param name="configuratorActive">on</param>
configuratorButton parameter defines whether Fields List button should be on Pivot UI or not. This property is off by default.
<param name="configuratorButton">off</param>
View type can be grid or charts. It is grid by default.
<param name="viewType">grid</param>
Chart type can be bar, line or pie. It is bar by default.
<param name="chartType">line</param>
showChartZeroValues parameter defines whether show zero values on charts or not. Zero values is shown by default.
<param name="showChartZeroValues">on</param>
showHeaders parameter defines whether show grid headers (1, 2, 3, ...) or not. Grid headers is shown by default.
<param name="showHeaders">on</param>
fitGridlines parameter defines whether the grid should fit to gridlines or not. This property is off by default.
<param name="fitGridlines">off</param>
showTotals parameter defines whether the totals should be shown on the grid or not. This property is on by default.
<param name="showTotals">on</param>
showGrandTotals parameter defines whether the grand totals should be shown on the grid or not. This property is on by default.
<param name="showGrandTotals">on</param>
Expand/Collapse All. Expand/collapse headers. This property is off by default.
<param name="expandAll">off</param>
saveReportURL
<param name="saveReportURL"><![CDATA[http://localhost/flex_demos/save.php]]></param>
localSettingsURL
<param name="localSettingsURL">local_settings.xml</param>
exportHTMLTemplateURL
<param name="exportHTMLTemplateURL">styles/misc/export.html</param>
emptyDataWarning parameter defines whether show warning when the data is empty or not. This property is off by default.
<param name="emptyDataWarning">off</param>
chartTitle parameter is to set chart title. This property is empty by default.
<param name="chartTitle">Revenue</param>

The example of <params> block of XML configuration file:

<params>
  <param name="configuratorActive">on</param>
  <param name="configuratorButton">off</param>
  <param name="viewType">grid</param>
  <param name="chartType">line</param>
  <param name="showChartZeroValues">on</param>
  <param name="showHeaders">on</param>
  <param name="fitGridlines">off</param>
  <param name="showTotals">on</param>
  <param name="showGrandTotals">on</param>
  <param name="expandAll">on</param>
  <param name="saveReportURL"><![CDATA[http://localhost/flex_demos/save.php]]></param>
  <param name="localSettingsURL">local_settings.xml</param>
  <param name="exportHTMLTemplateURL">styles/misc/export.html</param>
  <param name="emptyDataWarning">off</param>
  <param name="chartTitle"></param>
</params>

Values Formatting

To set values formatting the <format> tag is used. Each <format> tag can have name, for example: <format name="currency">. If the <format> tag has no name, it is default format. 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.
nullValue - how to show null values in grid.

<format name="currency">

<param name="thousandsSeparator"><![CDATA[ ]]></param>
<param name="decimalSeparator"><![CDATA[.]]></param>
<param name="decimalPlaces">3</param>
<param name="maxSymbols">20</param>
<param name="currencySymbol">$</param>
<param name="currencySymbolAlign">left</param>
<param name="nullValue"><![CDATA[no value]]></param>

</format>

How to apply different formats to different values

To apply different formats you should do the following changes in the configuration XML file:

Create additional <format> block. Each <format> should have a name:
<format name="number">

<param name="thousandsSeparator"><![CDATA[ ]]></param>
<param name="decimalSeparator"><![CDATA[.]]></param>
<param name="decimalPlaces">2</param>
<param name="maxSymbols">20</param>
<param name="currencySymbol"></param>
<param name="currencySymbolAlign">left</param>
<param name="nullValue"><![CDATA[ ]]></param>

</format>

<format name="currency">

<param name="thousandsSeparator"><![CDATA[ ]]></param>
<param name="decimalSeparator"><![CDATA[.]]></param>
<param name="decimalPlaces">3</param>
<param name="maxSymbols">20</param>
<param name="maxDecimalPlaces">2</param>
<param name="currencySymbol">$</param>
<param name="currencySymbolAlign">left</param>
<param name="nullValue"><![CDATA[no value]]></param>

</format>
Set the format name for the measure in <defaultSlice> <measures> block:
<measures>
<measure format="currency">[Measures].[Price]</measure>
<measure format="number">[Measures].[Quantity]</measure>

</measures>