Using Barcode encoder for Microsoft Excel Control to generate, create Code 3 of 9 image in Office Excel applications.

TarCode.com/Code 3 of 9

That s just an excerpt, of course; there are many more records in the report. Thus we see that each sale is broken out by its date with the proviso that should two or more sales have been conducted on the same date, their data would be aggregated or added together because again, entries in the Row Label area appear uniquely. 4. Now click anywhere in the Order Date data. Then click PivotTable Tools Options tab Group Selection in the Group button group. You ll call up this dialog box (Figure 8 54):

Using Barcode drawer for Office Excel Control to generate, create Barcode image in Microsoft Excel applications.

TarCode.com/Barcode

Using Barcode encoder for Office Excel Control to generate, create Data Matrix image in Excel applications.

TarCode.com/Data Matrix

Figure 8 54. Here s where you select time-based grouping interval 5. Note the various grouping options, which obviously apply to date data. We ll leave the default Months selection in place and click OK. You ll see (Figure 8 55):

Using Barcode drawer for Excel Control to generate, create Barcode image in Microsoft Excel applications.

TarCode.com/Barcode

Using Barcode creation for Office Excel Control to generate, create Leitcode image in Excel applications.

Using Barcode maker for Online Control to generate, create Code 128C image in Online applications.

TarCode.com/Code 128B

Figure 8 55. Aggregating by months discloses important patterns in sales activity I d say that s pretty cool and useful. The pivot report has combined, or aggregated, the dates into a larger category, in this case months, thus enabling us to break out the sales data by that unit. You could also select both Years and Months in the Grouping dialog box by way of additional example, whereupon the data would be broken out by year, and then by months below each year. And if we want to return the data to their original state, just click in the Row Labels area and repeat the above command sequence, substituting Ungroup Group. The grouping option can also be productively applied to standard numerical data in addition to dates, provided those data are placed in the Row or Column areas. For example-I could place the Order Amount data in the Row Label area something we ve yet to do, because these kinds of data are usually sent to the Values area to be added, averaged, etc. But if I do go ahead and assign Order Amount to the Row Labels area, I ll see, at least in part (Figure 8 56):

Using Barcode scanner for Software Control to read, scan read, scan image in Software applications.

Figure 8 56. Listed numbers: Each transaction sum, arrayed in the row label area True to Row Label form, the data here are merely listed uniquely (and that means if two sales came to say, $50 each, that number would be listed only once). Now, if I click the Group Selection command, I ll see (Figure 8 57):

Using Barcode reader for Software Control to read, scan read, scan image in Software applications.

Using Barcode generation for Reporting Service Control to generate, create ANSI/AIM Code 128 image in Reporting Service applications.

TarCode.com/Code 128

Figure 8 57. Grouping this time by the sales order amounts The Grouping dialog box here records the highest and lowest values among all the sales a spanand a suggested grouping interval, which we ll accept. (Note: By typing a larger value, say 100, in Starting at:, you can restrict the grouping to begin at the value you ve designated. The sub-100 values will also be grouped, but in a <100 category .) By clicking OK, we get (Figure 8 58):

Using Barcode decoder for Java Control to read, scan read, scan image in Java applications.

TarCode.com/Data Matrix

Figure 8 58. Big bucks: The transaction data, grouped in intervals of $1000 See what happens The pivot report aggregated, or grouped, the sales order data by units of $1000 (Note: These aggregated intervals cannot be reformatted). If we then drag the same Order Amounts field into the Values area where it usually goes but change its mathematical operation to Count, we wind up with this (Figure 8 59):

Using Barcode maker for Reporting Service Control to generate, create Data Matrix image in Reporting Service applications.

TarCode.com/ECC200

CHAPTER 8 PIVOTTABLES AND PIVOT CHARTS

Figure 8 59. Numbers of sales, grouped by transaction size in dollars True you may need to think about this one. What we ve done here is break out the number of sales by the size of each sale, in sales intervals of $1,000. Thus, for example, there were 7 sales carried out that were between $6012.50 and $7012.50, and 211 between 1012.50 and $2012.50. OK, grantedthis was a tricky one: take the rest the day off (but before you do, note that grouped data may continue to appear grouped on other new pivot reports in the workbook, and so you may need to ungroup these sooner or later in any case).