Active XL Report Pro: What is it?

In order to meet the requirements of our most exacting customers, we created an extended version of Active XL Report. It extends Active XL Report with new advanced features. They are:

Data grouping without subtotals - now you can use the GROUP option without summary options (SUM, COUNT, etc.). The GROUP option is now extended with parameters allowing cell merging (much as MergeLabels for pivot tables), controlling the placement of the group row, and grouping with no group header. You can use Excel formulas as well as Active XL Report options in the group header. See the Group without subtotals branch in the demo project.

Advanced subtotals - all the features of the GROUP option are available in subtotaling. Moreover, you are able to space out the groups to separate printed sheets, suppress totals, and create a group header in the presence of a summary row. See the Advanced Subtotals branch in the demo project.

Direct support of all Excel chart types - XL Report now can place static pivot tables in a template. It allows using all the pivot table design features, and speeding up the report generation process. We apply much the same machinery when developing the CHART option. Now you place a chart straight in a template and Active XL Report refreshes it. See the Static Charts branch in the demo project.

What follow below is a full list of Pro options and their parameters:

CHART Refreshes a given chart with list range data.

SUM, AVG, AVERAGE, COUNT, COUNTNUMS, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, VARP Now you can get totals in arbitrary ranges through the use of the \TOTALOFROW parameter..

GROUP (with subtotals) Available parameters: ASC, DESC, COLLAPSE, MERGELABELS, MERGELABELS2, PLACETOCOLUMN, DISABLEOUTLINE.

GROUP (without subtotals) Available parameters: ASC, DESC, COLLAPSE, MERGELABELS, MERGELABELS2, PLACETOCOLUMN, WITHHEADER, DISABLEOUTLINE, DISABLESUBTOTALS, PAGEBREAKS.

SUMMARYABOVE, SUMMARYBELOW Range options. Allow placing the summary row below or above the group header (if any).

GROUPWITHHEADER Allows using all features available for usual cells straight in the group header.

DELETECOLUMN Deletes a column of a range

OUTLINE Allows creating the Outline view for nested ranges.

Grouping without subtotals

Excel doesn't have quick grouping features similar to those of the Subtotals method of the Range object. Active XL Report Pro solves this problem allowing using the standard Group option without any parameters. In this way, for instance:

This sample template will group the list range by the Company column producing all the subtotal attributes without creating subtotals themselves: 1) grouped data will be divided by a group header row containing the grouped value; 2) the outline view will be created (see the figure below).

Implementation of the Group option in Active XL Report Pro allows several new features available either through the use of the Group option parameters or in combination with other options from this package. First, you can place the group header row above grouped data. Just use the SummaryAbove range option. Second, you can avoid data repetition in a grouped column and merge the grouped column's cells. This feature is provided through the \MergeLabels parameter of the Group option (syntax - "Group\MergeLabels"). The result of joint use of the SummaryAbove option and \MergeLabels parameter is shown in the figure below.

So, the /Merglabels parameter of the Group option:

• Inserts the group header row above (in the presence of the SummaryAbove option) or below the grouped values
• Clears the values in the grouped cells'
• Merges the cleared cells
• Form the outline view

There is another variant of merging cells. It is implemented in the /MergeLabels2 parameter of the Group option. This parameter doesn't insert the group header row and doesn't clear the grouped cells. It merges group cells preserving the group value. The sample report follows below. Note that absence of the outline view grouping symbols - Excel is not capable of creating the outline view if there is no header row.

Another feature of the Group option allows deleting the group column preserving group header row. You place the group value to any other column of the range (the \PlaceToColumn=n parameter of the Group option) and delete the group column (the DeleteColumn column option). The sample template in the figure below demonstrates the use of this feature.

This template contains the SummaryAbove range option in the A6 cell. The resulting report is shown below.

You can see that the Company column is deleted because its option cell contained the DeleteColumn option. The group value is placed to the OrderNo column thus preserving the header row from deleting. This is accomplished via the \PlaceToColumn=2 parameter of the Group option. 2 - means the second column of the source range.

You can create several groupings for the same range. In such a case groupings will be created in the left-to-right order. The \DisableOutline parameter prevents the outline view for a given group. The figure below shows the report whose template contained two groupings - on the Company and Payment Method columns. The second Group option had the \DisableOutline parameter.

In addition to the samples, described above, you will find several more samples in the demo project demonstrating data grouping without subtotals.

Advanced subtotals

There are certain differences between groupings with and without subtotals. First - a subtotaled group doesn't have a group header row but they have a summary row instead. Second, the above listed parameters work in a slightly different way. We describe the same features anew plus we describe additional range and column options that affect subtotals.

Just as in the case of grouping without subtotals, the SummaryAbove range option places the summary row above grouped values. The figure above shows the sample report that uses this option. Take note of grand totals that are also placed above the range data.

You can suppress the grand totals creation using the DisableGrandTotals range option - see the sample report below.

In the case of several different subtotals for the same column, say sum and average, Excel creates several different grand totals (naturally) but displays them in a somewhat peculiar way. This effect lasts out both in programming subtotals via VBA macros and in creating them manually. So, if your understanding of 'peculiarity' is close to ours, you will avoid using the SummaryAbove option in this case. In case of several groupings in the same range, the SummaryAbove option can lead to even more peculiar display of data. That's why we don't recommend using this option in such a case. We tried hard to solve as many potential problems as we could but we'd recommend avoiding using the SummaryAbove option together with subtotals, especially if you plan to work with Excel97.

Another feature is creating page breaks on the fly. If you need to place every group at separate pages, use the /PageBreaks parameter to the Group option.

The /MergeLabels parameter merge the grouped cells but, contrary to grouping without subtotals, keeps the group value in the resulting cell. Sample:

The /MergeLabels2 parameter consider the summary row as the part of a group and works in the following way:

You can create the header row for a grouping with subtotals. Just use the /WithHeader parameter. If any of the Group options in a range has this parameter, the SummaryAbove option as well as /MergeLabels and /MergeLabels2 parameters will be ignored. XL Report formats the header and summary rows according to formatting of the option row. The figure below shows the sample report demonstrating the result of applying of the /WithHeader parameter to the Group option in the Company column.

Static charts

One of the main features of Active Xl Report Pro is the possibility to use Excel charts referencing Active XL Report ranges. This feature is available through the use of the Chart option.

For instance, you had created the OrdersRange shown at the figure at your left. Now you invoke Chart Wizard (Insert|Chart:), choose the Bar type, leave the Data Range box empty, click the Series tab and create a new series named Amount paid, that takes its values from the Amount paid column and labels from the Company column.

Do you see that both ranges include the option row? To create another bar (Items total) you add another series, name it and make it take its data from the C4:C5 range. Finish the wizard and place the chart any where on the current worksheet.

In order to let the Chart option to work correctly you should give the name to the chart. Press the Ctrl key and click the chart (Ctrl+Click). Then you can change the default chart name in the Name box at the left of the Formula bar. We preferred to name it SimpleChart. Nice enough, isn't it? <g>

To refresh the chart with real data you have to apply the Chart option to the range: 'Chart\Name=Sheet1!SimpleChart'. Note that we used the full name of the chart, that is, including the sheet name. If you place a chart on a separate sheet, you can put only the name of the sheet in the \Name parameter. And here is the chart with real data: