A typical report might provide a list of models with revenue by year. This report could be given to product line managers to show them which models are selling well. In this example, you want to show the models in descending order by revenue with years going across the columns. A sample report is shown in Figure 12.6.
Figure 12.6. A typical request is to take transactional data and produce a summary by model for product line managers. You can use a pivot table to get 90% of this report, and then a little formatting to finish it.
The key to producing this data quickly is to use a pivot table. Although pivot tables are incredible for summarizing data, they are quirky and their presentation is downright ugly. The final result is rarely formatted in a manner that is acceptable to line managers. There is not a good way to insert page breaks between each product in the pivot table.
To create this report, start with a pivot table that has Line of Business and Market as row fields, In Balance Date grouped by year as a column field, and Sum of Revenue as the data field. Figure 12.7 shows the default pivot table created with these settings.
Figure 12.7. Use the power of the pivot table to get the summarized data, but then use your own common sense in formatting the report.
Here are just a few of the annoyances that most pivot tables present in their default state:
Even with all these problems in default pivot tables, they are still the way to go. Each complaint can be overcome, either by using special settings within the pivot table or by entering a few lines of code after the pivot table is created and then copied to a regular dataset.
Eliminate Blank Cells in the Data Area
People started complaining about the blank cells immediately when pivot tables were first introduced. Anyone using Excel 97 or later can easily replace blank cells with zeroes. In the user interface, the setting can be found in the PivotTable Options dialog box. Choose the For Empty Cells, Show option and type 0 in the box.
The equivalent operation in VBA is to set the NullString property for the pivot table to "0".
Although the proper code is to set this value to a text zero, Excel actually puts a real zero in the empty cells.
Control the Sort Order with AutoSort
The Excel user interface offers an AutoSort option that enables you to show markets in descending order based on revenue. The equivalent code in VBA to sort the customer field by descending revenue uses the AutoSort method:
PT.PivotFields("Line of Business").AutoSort Order:=xlDescending, _ Field:="Sum of Revenue"
Default Number Format
To change the number format in the user interface, double-click the Sum of Revenue title, click the Number button, and set an appropriate number format.
When you have large numbers, it helps to have the thousands separator displayed. To set this up in VBA code, use the following:
PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0"
Some companies often have customers who typically buy thousands or millions of dollars of goods. You can display numbers in thousands by using a single comma after the number format. Of course, you will want to include a "K" abbreviation to indicate that the numbers are in thousands:
PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0,K"
Of course, local custom dictates the thousands abbreviation. If you are working for a relatively young computer company where everyone uses "K" for the thousands separator, you're in luck because Microsoft makes it easy to use the K abbreviation. However, if you work at a 100+ year-old soap company where you use "M" for thousands and "MM" for millions, you have a few more hurdles to jump. You are required to prefix the M character with a backslash to have it work:
PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0,\M"
Alternatively, you can surround the M character with a double quote. To put a double quote inside a quoted string in VBA, you must put two sequential quotes. To set up a format in tenths of millions that uses the #,##0.0,,"MM" format, you would use this line of code:
PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0.0,,""M"""
In case it is hard to read, the format is quote, pound, comma, pound, pound, zero, period, zero, comma, comma, quote, quote, M, quote, quote, quote. The three quotes at the end are correct. Use two quotes to simulate typing one quote in the custom number format box and a final quote to close the string in VBA.
Suppress Subtotals for Multiple Row Fields
As soon as you have more than one row field, Excel automatically adds subtotals for all but the innermost row field. However, you may want to suppress subtotals for any number of reasons. Although this may be a relatively simple task to accomplish manually, the VBA code to suppress subtotals is surprisingly complex.
You must set the Subtotals property equal to an array of 12 False values. Read the VBA help for all the gory details, but it goes something like this: The first False turns off automatic subtotals, the second False turns off the Sum subtotal, the third False turns off the Count subtotal, and so on. It is interesting that you have to turn off all 12 possible subtotals, even though Excel displays only one subtotal. This line of code suppresses the Product subtotal:
PT.PivotFields("Line of Business").Subtotals = Array(False, False, False, False, _ False, False, False, False, False, False, False, False)
A different technique is to turn on the first subtotal. This will automatically turn off the other 11 subtotals. You can then turn off the first subtotal to make sure that all subtotals are suppressed:
PT.PivotFields("Line of Business").Subtotals(1) = True PT.PivotFields("Line of Business").Subtotals(1) = False
Suppress Grand Total for Rows
Because you are going to be using VBA code to add automatic subtotals, you can get rid of the Grand Total row. If you turn off Grand Total for Rows, you delete the column called Grand Total. Thus, to get rid of the Grand Total row, you must uncheck Grand Total for Columns. This is handled in the code with the following line:
PT.ColumnGrand = False