List ranges

You can use only simple range options in an arbitrary range (excluding the AutoFilter option that is used in a list range only, as you may recall). This isn't an Active XL Report restriction - it is an Excel restriction. You need to understand that all the Excel capabilities in data grouping, analyzing, and filtering are based on list processing methods. Sorting, totaling, grouping, etc. are done not by Active XL Report but by Excel itself, so the existing restrictions on the report design are Excel restrictions. Remember the main thing - if you would like to total, filter, sort, group etc. you have to create and use list ranges (or just lists in Excel terminology - see Appendix A).

Sorting a list range

Where? form: frmListRange1; template: ListRange1.xls

Just use the Sort option in the option cell of the columns you need to sort. We have deleted all options in the OrdersRange range of the previous template. In the option cell of the PaymentMethod we have put the Sort option. There was no previous sorting in this range. The primary key of the Orders table provided the default sorting. Now the report is sorted on the PaymentMethod column. You can sort the column in descending order by adding the Desc option to the Sort option and separating them by semicolon.

Sorting in Active XL Report has some restrictions. First, a list range (a list) can be sorted only on three columns maximum. Second, Active XL Report can sort a list range only in left-to-right order. If you set Sort option for more then three columns then only the three leftmost columns will be used for sorting.

Totals

Where? form: frmListRange2; template: ListRange2.xls

In the previous chapter, we showed you how to get a sum of column values in a list range. The formula generated by Active XL Report for the column was =SUBTOTAL(9;..... Active XL Report supports all the functions of SUBTOTAL. Here are the options:

• Sum - the sum of the values in a column;
• Count - the number of items in a column;
• CountNums - the number of records or rows in a column that contains numeric data;
• Avg - the average of the values in a column;
• Max - the largest value in a column;
• Min - the smallest value in a column;
• Product - the result of multiplying all the values in a column;
• StDev - an estimate of the standard deviation of a population, where the column is the sample;
• StDevP - the standard deviation of a population, where the column is the entire population;
• Var - an estimate of the variance of a population, where the column is the sample
• VarP - the variance of a population, where the column is the entire population.

See Excel Help if you need assistance. You can get only one summary option for a column. If you provide several options (separated by a semicolon, of course), then Active XL Report will use only the last one.

We warned you that in the case of summary option the option row isn't destroyed. Notice the Total string in the I13 cell of the template. Its contents confirm our statement. This is the one and only exception. In all other cases the option row is destroyed after report building.

Subtotals

Where? form: frmListRange3; template: ListRange3.xls

Summary options can be used in combination with the Group option. Provide this one for columns where you want to get subtotals. Open the template of the previous example and write down Sum in Items Total and Amount Paid column option cells. In the option cell of the Payment Method column write down the Group option. Save, close, and run the report. You can see the result - subtotals are inserted in the Payment Method column.

You can provide up to 16 columns for subtotals. Add the Group option to the Sale Date column. In the report you see that 1) subtotals are inserted on Sale Date and Payment Method; 2) the OrdersRange range contains both original data and subtotals; 3) grouping is made in left-to-right order - subtotals are calculated first on the leftmost column with Group option, then the next grouped column is on its right, and so on.

In contrast to the total of a column, you can have several subtotals. If you add the Avg option in the Amount Paid column and run the report, you will see the sum and the average of this column.

Subtotaling implies previous sorting of data (Sale Date and Payment Method columns in our case). By default, Active XL Report does the work itself. But if you have sorted the dataset beforehand, you can use the GroupNoSort range option. It is just a tip to lessen the time it takes to generate a report.

Hiding detail data in an outline

Where? form: frmListRange4; template: ListRange4.xls

Add the COLLAPSE parameter to the Group option in the previous example to hide detail data in a subtotaled report. It can be useful if your customer prefers to see grand totals immediately and then decipher them by using the outline symbols.

Active XL Report uses only the rightmost option column containing the Group option with this parameter.

Time to look back - list range options

• A list is the basis of Excel power, thus giving us the ability to sort, filter, group etc.
• A Range-list can be sorted via the Sort option. A maximum of three columns can be sorted. Default sorting order is ascending. To sort in descending order, use the Desc option. In the case of several columns, fields for sorting are taken in left-to-right order.
• For a column in a list range, you can get a total that will be placed directly below the column. You can call any function of the SUBTOTAL function using the appropriate options.
• In a list range, you can get subtotals on several columns.
• The maximum number of grouped columns is 16. Fields for grouping are taken in left-to-right order.
• If there are subtotals in a report, you can indicate the level of subtotals' outline to be hidden via the Collapse parameter of the Group option.