Options in a list range

You can use only simple range options in an arbitrary range (excluding an AutoFilter that is used in a list range only, as you may recall). This isn't an 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 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). We refer to a lot of Excel documentation here; that's because we plan to talk about things that are probably well known. Now we'll begin.

Sorting in a list range

Where? QDemo.dpr form: frmLists1; unit: fLists1; template: tLists1.xls

Just use the Sort option in the option cell of the columns that are needed. We opened the previous form, opened the template, and deleted all options in the OrdersRange range. In the option cell of the PaymentMethod we 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 XL Report has some restrictions. First, a list range (a list) can be sorted only on three columns maximum. This is the restriction of the Sort method of the Range object. Second, 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 in a column

Where? QDemo.dpr form: frmLists2; unit: fLists2; template: tLists2.xls

In the previous chapter, we showed you how to get a sum of column values in a list range. The formula generated by XL Report for the column was =SUBTOTAL(9;:, where 9 stands for Sum (See Excel Help if you need assistance). XL Report supports all the summary functions of SUBTOTAL. Here are the respective 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.

You can get only one summary option for a column. If you provide several options (separated by a semicolon, of course), then 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 that the option row will be deleted. This is the one and only exception.

Subtotals in a list range

Where? QDemo.dpr form: frmLists3; unit: fLists3; template: tLists3.xls

Summary options can be used in combination with the Group option. Provide this one for columns where you want to get subtotals. Here is an example. 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. This is another feature of XL Report to remember.

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, XL Report does the work itself. But if you have sorted the dataset, you can use the GroupNoSort range option. It is just a tip to lessen the time it takes to generate a report. We remind you to put range options in the leftmost bottom cell of a range.

Hiding detail data in an outline

Where? QDemo.dpr form: frmLists4; unit: fLists4; template: tLists4.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.

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 (only one total value per column). You can make use of any function of the SUBTOTAL function using the appropriate options.
• In a list range, you can get subtotals on several columns: subtotaled columns must have any of the summary options and grouped columns must have the Group option.
• 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 a subtotals' outline to be hidden via the Collapse parameter of the Group option.