Quickly Display Table Data Into Fully Customizable Visual Reports

Feature Overview

Quickly Display Table Data into Fully Customizable Visual Reports

TheExpressPivotGridSuiteallowsyour end users to slice and dice data in a manner similar to that found in MS Excel®. It has the power to convert hard to read and difficult to dissect dataset information into compact and summarized visual reports - making your applications far more usable and more effective at addressing your customer's real-time business analysis needs. And just as important, withtheExpressPivotGrid, userscaneasily change a report's layout visually in order to examine data from different/multiple perspectives. The benefits of this are two-fold - first, you never again have to foresee all possible report scenarios your end-users might require (saving you time and increasing your productivity) and second, your users will not have to deal with hard to use and overly complex reporting tools to answer their business questions.

Control the Level of Detail

By default, the row and column axes contain each unique value in the source column. WiththeExpressPivotGrid, youcanquickly simplify a report by merging values. For instance, you might have a report containing survey data in which the respondent's age represents an important characteristic. End-users may not want to browse through each and every unique age and instead want to see the ages of respondents in intervals such as 21-30, 31-40, etc. Instead of viewing sales by each unique date, they may want to view the sales for just the first few months. The axis can be customized in almost any fashion and the most common customizations need only a single property to be modified for its implementation!

Print andRendertheExpressPivotGridwithEase

TheExpressPivotGridis NOT just about on-screen data analysis. With it, you can export data to numerous formats and render its contents to paper just like a traditional WYSIWYG report.

• Printing support is availableviatheExpressPrintingSystem (mustbe purchased separately). This allows you to create cross-tab reports easier than ever before.
• Export to the XLS, XML, HTML and TXT formats.
• Multi-cell selection and data export via the clipboard.

Arrange Values Hierarchically - See Raw or Summarized Data in a Single Report

Businesses have unique requirements and no two entities will analyze information in the same exact way. Your end-users may want to analyze sales for each product or each product category. They may want to view yearly data or see more detailed data by viewing the data for each month in a year. Using traditional reporting/database tools, you would often be forced to create a myriad of different reports to deliver the appropriate solution to your customers - but that's no longer the casewiththeExpressPivotGrid. Since the visual control offers your end-users total runtime customization flexibility, not a single report layout needs to be modified to view data at different detail levels. You simply place fields at lower detail levels on the column or row area. Field values are automatically arranged into a tree structure with expandable parents. End-users are free to expand and collapse child groups to see more summarized or raw reports.

Automatic and Manually Specified Totals

To improve the developmentexperience, theExpressPivotGridensuresyourability to deliver superior capabilities with minimal coding. Among the many features available in the component library is its ability to display grand totals for each row and column automatically. On the rare occasion that the built-in summary computation engine does not address a specific business requirement, you can display any number of totals for any value group.

Sort Data and Display Top Rows - Easily Access the Most Important Information

TheExpressPivotGridautomaticallysortsrow and column values so you can easily locate required data. Comprehensive data analysis, however, often requires a two-way approach to data navigation in that you may need to find data by a specific value or find a value by its data. To allow for the latterapproach, theExpressPivotGridintroducesaunique feature - sorting values by their corresponding grand totals. This means the product you'll see first is not the one which starts with an A or Z, but rather the one that has the most or least sales. This of course is just the beginning to more effective and efficient data analysis. You can also specify the number of top rows that are displayed. So you can view a specific number of best-selling or worst-selling products for each year, month, customer, etc. You can concentrate on only the most important information and avoid being overload with a lot of surplus data.

Filter Data - Filter Out the Values Used to Calculate Summaries on the Fly

As you've probablygathered, theExpressPivotGridisunrivaledin its feature set and capabilities for the VCL. Our goal was to radically simplify runtime data analysis and provide end-users with superior customization options. You will find that with its built-in visual filtering option, your end-users can manipulate reports on the fly using an easy to understand and familiar visual metaphor.

• Hide unnecessary values in the axes.
  Yet another mechanism that allows end-users to focus only on the most important data.
• Filter the data against which calculations are based.
  To allow your end-users manage this capabilityvisually, theExpressPivotGridhasaFilter Panel Region. This pane was specifically designed to contain fields that aren't currently used in the report, and when required, can be dropped onto the report to automatically alter the base data used in the calculations.

Drill Down - Take a Look at the Data Behind a Summary

TheExpressPivotGridisdesignedto transform table data into a summarized report. At the same time, it allows you to do the reverse. With just a single line of code, you can take a look at the records that were used to calculate cell values. With a few more lines of code, you can display this data withinouradvancedExpressQuantumGridcontrol. This feature brings complete data browsing flexibility within a single report - from different detail levels in a summarized report to the original raw data used to create the report itself. End-users are free to analyze every aspect and level of their data!

Transform Table Data into a Pivot Report

For demonstration purposes, let's consider a car sales database with the following structure:

You can easily construct a query that will merge the information in all the tables into a single record set. If each record in the Orders table represents a single-car purchase, the total number of fields in the query's results will match the total number of cars sold. Summing by the Price field will generate sales totals.

Based on the data contained in these tables, let's assume your customer requests that you display on-screen and in printed form the total number of cars sold on any given day, by individual model - and how sales of these models have changed over time. To solve this business requirement and demonstrate the poweroftheExpressPivotGrid, we'llgoaheadandbindthePivotGridtotherecord set and create the fields which correspond to the underlying data source.

To construct the appropriate report layout, you simply need to drag fields to the correct region. As such, if you need to see a cross-report of sales by dates and models, simplydragtheOrderDateandModelheaders to the Column and Row areas. To display the sum of sales for each date and model intersection, drag the Price header to the data area. The image below shows the resulting report. Instead of a hard to read table of data with numerous records that have to be scrolled vertically and then analyzed (a laborious process to say the least), your customer instead has a fully summarized and compact report at his fingertips.

End-users can freely drag field headers in the same manner as you do at design time. Just a few drag and drop operations can entirely change the report's layout. This allows end-users to analyze the source data in the way they want to, without you having to redo a single thing. Below are a few examples that have been generated by simply dragging the field headers between areas.

• grand totals by model;
• daily sales by categories;
• daily sales by trademark and model (multiple fields in the column area).

Use Multiple Data Fields in a Report

The previous example showed an extremely simple report with just a single data field. This means that there is a single cell at each column/row intersection. ButtheExpressPivotGridletsyoucreate reports which have an unlimited number of data fields. In addition to that, you and end-users have additional options for customizing the control's layout.

Let's add one more field to the control to display the number of cars sold. The only field in the previous example (Price) used the default summary type for numeric columns - sum. For the newly added field, you need to change the summary type to count. There are 9 different built-in summary types availablewithintheExpressPivotGrid.

Note: the ExpressPivotGrid does not limit you with these predefined summary types. You can implement a custom aggregate function by writing aOnCalculateCustomSummary event handler for the desired field.

Now, when multiple fields are in the report, you can enable the Data header. Like any other header, you or end-users can drag it to different locations to fine-tune your report. This header lets you control which data to display first, whether to arrange data fields horizontally or vertically, etc. The content, size and availability of the Data header are controlled viathecontrol'sOptionsDataFieldproperty.

The following screenshots offer a glimpse into additional layouts that are just a drag and drop away from your users.

• Data cells located one under another;
• All price data then all count data (horizontally);
• All price data then all count data (vertically).

Adopt Any Data Using Manually Calculated Fields

Consider a more general case when the Orders table contains one morefield - UnitsOnOrder. In this instance, the sales volume for each record is PricemultipliedbyUnitsOnOrder.

To display the sales volumeintheExpressPivotGrid, youcaneasily add a manually calculated field which will hold PricemultipliedbyUnitsOnOrder. To accomplish this task, you need to carry out three simple steps. Add a new field, setitsDataBinding.ValueTypepropertytoCurrency and write a couple of code lines to populate the newly created unbound field with data.

[Delphi]

var
Index, UnitsOnOrder: Integer;
UnitPrice: Double;
// ...
with cxDBPivotGrid1.DataController do
begin
forIndex := 0 to cxDBPivotGrid1.DataController.RecordCount -1 do
begin
UnitsOnOrder := GetValue(Index, FieldUnitsOnOrder.Index);
UnitPrice := GetValue(Index, FieldPrice.Index);
SetValue(Index, FieldSales.Index, UnitsOnOrder * UnitPrice);
end;
end;

Now you can create a new report that will match the updated database structure. Simply drag the newly created Sales field to the data area and fine-tune your report by moving other headers to the desired locations. The following image shows a sample.

Show Trends Using the Built-in Summary Variation Feature

If the main aim of end-users is to analyze trends - see how values are changing with time or depending on other values - you will be glad to knowthattheExpressPivotGridoffersyoua built-in solution for this objective. You can trigger a single property to force cells to display differences rather than actual values. And there are two difference display modes available - absolute or percent.

Take a look at the next image for an example. Initially, there are two fields in the data area. They are bound to the same data field and thus display the same values. After you changeonefield'sSummaryVariationpropertytosvAbsolute, this field lets you see how sales were changing with time. Each cell displays the difference between its actual value and the valueinthepreceedingcell.

In the screenshot above, you see the variation displayed by dates. The obvious question is how can one change variation direction to display trends by trademarks. This is possible by means of the Data header discussed earlier. End-users can drag it to the column area to display left-to-right trends or to the row area to display top-to-bottom trends. You can control this programmaticallyusingtheOptionsDataField.Areapropertyevenif the Data header is invisible.

Group Data On Axes

VCL Controls
ASP.NET AJAX Controls
WinFormsControls
WPF Controls
SilverlightControls
VCL Controls
Reporting for WinForms and ASP.NET
Charting for WinForms and ASP.NET
.NET ApplicationFramework
Object-Relational Mapping for .NET
IDE Productivity Tools
Pivot Grid
Navigation Controls
Data-Aware Tree-Views
Flow Chart
Layout Manager
Master-Detail Data Viewer
Navbar
Org Chart
ExpressPack™
Pivot Grid
Printing System
Grid and Editors
ExpressQuantumPack™
Tree-Grid Hybrid
Scheduler
Skinning Library
Spell Checker
Spreadsheet
Object Inspector
Group Data On Axes
GroupDataOnAxes
From Plain Data to Hypercubes
DrillDown
ArrangeValuesHierarchically
Sort Data and Display Top Rows
Automatic and Manually Specified Totals
FeatureOverview
ScreenshotGallery
SpecificationsandPricing
What'sNewin v2

Data sources contain detailed information whereas summary reports often require only a general overview of the data stored within the database. WiththeExpressPivotGridyoucangroup values using predefined intervals or provide your own intervals as needed. Both approaches are extremely simple as demonstrated below.

• Use Built-in Mechanisms to Group Values
• Group Values Manually

Use Built-in Mechanisms to Group Values

ExpressPivotGridfieldsexposeaGroupIntervalpropertywhichallows you to merge field values into groups. By changing just a single property you can group date-time values by months, years, etc. Along with date-time group intervals, this property allows you to group values using numeric intervals or alphabetically. When grouping alphabetically, text values are joined into groups by comparing their starting characters.

The following image shows a complete list of self-explanatory options offeredbytheGroupIntervalproperty.

So by altering the value of a single property you can display different levels of detail and exactly match the needs of your end-users. The image below demonstrates the default appearance of the date-time axis and the effect that applying various group intervals has.

You can generate a more flexible report by displaying values hierarchically. You can create several fields boundtoaDateTimefieldandspecify different group intervals. Thus, end-users will be able to expand and collapse hierarchy nodes so that in a single report they can view the data by years, months or dates. Learn more about this feature here.

Group Values Manually

These built in grouping algorithms will address a wide range of business scenarios, but are by no means the only options available to you. You can easily group axis values using your own criteria. To implement custom group intervals, follow the simple steps below:

• Set thedesiredfield'sGroupIntervalpropertytogiCustom.
• Handlethefield'sOnGetGroupValueevent. For instance, if you need to have two groups in a numeric axis - with both positive and negative values, you can specify -1 if the field value in the current record is negative and 1 if not.
• Handlethefield'sOnGetGroupValueDisplayTexteventtosubstitute group values with user-friendly text. For instance, in the previous example, you can handle this event to replace the 1 and -1 values with the words "Positive" and "Negative" respectively.

Now let's see how manual group intervals can be implemented in a real example. The image below shows a sample report with the average salary and seniority displayed for employees. Employees are grouped by their age and department.

To compact the report further and make it more readable, lets group the values in the Age axis. This axis will show two intervals ("Under 30" and "Over 30") rather than display each unique age. To achieve this aim, all you need to do is to create an unbound field and write two short event handlers:

[Delphi]

procedure TForm1.FieldAgeGetGroupValue(Sender: TcxPivotGridField;
ARecordIndex: Integer; varAValue: Variant);
begin
AValue := cxDBPivotGrid1.DataController.GetValue(ARecordIndex, FieldAge.Index);
ifAValue < 30 thenAValue := 0
elseAValue := 1;
end;
procedure TForm1.FieldAgeGetGroupValueDisplayText(
Sender: TcxPivotGridField; constAGroupValue: Variant;
varAText: String);
begin
ifAGroupValue = 0 thenAText := 'Under 30'
elseAText := 'Over 30';
end;

And with just a few lines of code, you get a completely different view.

BecausetheExpressPivotGridoffersyoucomplete control over individual axis, you can deliver a wealth of business value to your customers with only a few lines of source code.

Arrange Values Hierarchically

VCL Controls
ASP.NET AJAX Controls
WinFormsControls
WPF Controls
SilverlightControls
VCL Controls
Reporting for WinForms and ASP.NET
Charting for WinForms and ASP.NET
.NET ApplicationFramework
Object-Relational Mapping for .NET
IDE Productivity Tools
Pivot Grid
Navigation Controls
Data-Aware Tree-Views
Flow Chart
Layout Manager
Master-Detail Data Viewer
Navbar
Org Chart
ExpressPack™
Pivot Grid
Printing System
Grid and Editors
ExpressQuantumPack™
Tree-Grid Hybrid
Scheduler
Skinning Library
Spell Checker
Spreadsheet
Object Inspector
Arrange Values Hierarchically
GroupDataOnAxes
From Plain Data to Hypercubes
DrillDown
ArrangeValuesHierarchically
Sort Data and Display Top Rows
Automatic and Manually Specified Totals
FeatureOverview
ScreenshotGallery
SpecificationsandPricing
What'sNewin v2

The hierarchical display of values brings multiple detail levels to a single report. The more fields that take part in a hierarchy, the more flexible the report is. Multi-level hierarchies allow end-users to view data from different perspectives thus giving them total control over the data's layout. At the same time, you only have to create a single report and don't need to change a single thing to show more or less detail within the report.