Report Data Pivot Tables represent a summary of a Data Table in a Data Set. Pivot Tables summarize data in another Data Table, and are made by applying operations such as sorting, averaging, summing or counting.
To add, edit or insert a Report Data Pivot Table click on the Report Designer tree-view and select a Report Section or Report Group and then click the menu Edit | New, Insert or Edit Pivot Table.
The Build Option property controls the output of the Report by including the content in HTML and / or PDF builds.
Example of a Pivot Data Table
|
ACME SOFTWARE
|
TOMMY SYSTEMS
|
DAC SOLUTIONS
|
|||||||
|---|---|---|---|---|---|---|---|---|---|
|
OCCUPATION
|
2010
|
2011
|
2012
|
2010
|
2011
|
2012
|
2010
|
2011
|
2012
|
| Administration | £633,226.75 | £333,174.71 | £289,011.14 | £266,929.36 | |||||
| Business Analyst | £7,079,479.00 | ||||||||
| Chartered Accountant | £116,591.03 | £178,602.21 | £200,684.00 | ||||||
| HR Analyst | £121,111.50 | £121,764.71 | £145,479.53 | £561,009.00 | |||||
| HR Manager | £194,755.00 | £233,806.68 | £666,349.43 | £311,092.93 | |||||
| Project Manager | £266,929.36 | ||||||||
| Sales Representative | £337,059.00 | £355,256.50 | £255,888.46 | £277,970.25 | |||||
| Software Engineer | £1,875,584.75 | £243,529.42 | £804,683.92 | £189,643.10 | £522,817.82 | £300,052.03 | |||
| Team Leader | £211,724.89 | £37,356.85 | £366,297.39 | ||||||
| £9,819,714.14 | £753,048.69 | £950,163.45 | £266,929.36 | £999,524.14 | £1,723,025.95 | £1,083,826.82 | £844,951.63 | £1,056,676.54 | |
Data Pivot Function
The Data Pivot Function determines how data is summarized in the Data Pivot Table. Functions for summarising data include Sum, Average, Count, Max, Min, First, Last and Exists.
Choose the relevant function for the Data Pivot Table according to how the data needs to be summarised or presented.
Data Filters can be applied to Report Data Pivot Tables for filtering and sorting.
To enable Data Filtering click the Data Filter (...) property button to open the filter properties page.
Data Filters can be used to select and sort data from Data Tables returning specific results and / or rearranging into some meaningful order.
For example, if a Select Filter is applied for Fee Name = 'Cool Doc Fee' then only the data with this criteria will be output in the Data Table.
Report Queries as an either an XQuery or Python expression to query the Report Data can also be used for Data Selection, Data Sorting and Data Limits.
Data Row Limit
Data Rows in the Data Table can be limited by specifying a Row Limit in the Data Filter. Open the property page for the Data Filter and enter a limit for the Data Rows.
Data Filter Designer
The Data Filter Designer is a powerful tool to help with the design and application of Data Filters and Data Sorting on Data Columns.
Select the Data Columns to filter and / or sort from the drop-down lists.
Click the 'Execute' button to view the filtered data and then OK to apply to the Report Data Pivot Table.
Data Column Properties
The Data Column style and format can be configured by applying settings in the properties page. For example a column can be specified as having a currency format with rounding and decimal places.
Data Column Formatting
There are many options for formatting Field Data. Select the best formatting option to display the Field Data according to your requirements. Data can be formatted for time, date, number, currency and strings.
Regular Expressions and Inbuilt Functions can also be applied to Data Formats to manipulate and extract Data.
Data Formats can also be declared globally in the Report and used throughout the Report to apply common Data Formatting options. If a global Data Format is available it will be shown in the drop-down for Field Formatting under Format Name.
Data Row and Alternate Row Styling
Data Rows and alternate Data Rows in the Data Pivot Table can styled with fonts, fore color, back color, borders and various other styling properties to customise the appearance of the Data Pivot Table. Expand the column properties and configure the styling on the property page.
Report Data Tables can be defined with Report Data Contexts which are used to drive and build Report output content and also allows the consolidation and integration of data from multiple different sources and types such as API's, websites and data files to be read and used in the Report.
Data Contexts can be applied to specific Report elements allowing data in XML documents to be contextually enumerated. The default Data Context is the Report Data Source (XDM) root node and if no Data Context is specified then the current Report Data Source will be used.
To apply a Data Context to a Report element expand the Data Context property or click the (...) property button and specify the Content Type as either a Literal (Constant) or a File.
Context Types can be either XQuery, Python, XML, XLS, XLSX, CSV, JSon or HTML.
The Report Data Pivot Table can use the Report Data Context or a custom Data Context specified by the Data Context property.
In addition XSLT Reporting features Extensible Stylesheet Language Transformations which is a language for transforming XML documents into other XML documents or other formats such as HTML for web pages or plain text.
To specify an XSLT to transform the Report Table Data click the XSLT (...) property button.
Data Source Mapping can be used to map data and add additional custom Data Columns to a Data Table using formulas, expressions and queries.
Add Data Columns to the Report Chart Data Mapping and design formulas, expressions and queries accordingly to create the custom data.
Click the OK button to add the Data Column to the Data Source Mapping.
File URI's
Report File URI's are expressed as Uniform Resource Identifiers (URI) and can be either a Literal or a Query to specify the location of the File Resource. Literal values are constants that are determined by the URI File property.
Query based URI's are determined by the result of the URI Query which can be a series of XQuery or Python statements to specify the location of the File URI.
Report Rules are conditional XQuery or Python expressions that determine if Report content is enabled and applied to the build. Rule based queries return a Boolean that is either True or False and can query the Report Data Source and other Report Data Contexts. Rules are used for controlling the flow, visibility and activation of reporting content.
Report Rules if empty or null by default will always return True.
Report Namespaces are used for providing uniquely named elements and attributes and is a mechanism to avoid name conflicts by differentiating elements or attributes within an XML document that may have identical names, but different definitions. If the Report Data Source contains namespaces they can be applied to the XQuery Processor.
