An improved method of PivotTable report in Excel2010 table

The following improvements and new features make it easier and quicker for users to use PivotTable and online analytical Processing (OLAP) PivotTables in Microsoft Excel2010.

Computing performance

In Excel2010, multithreading helps to speed up the calculation in the PivotTable report, thereby improving the overall performance of the PivotTable report. This means that you can get results faster when you work with large amounts of data, such as sorting and filtering data in a PivotTable report.

Pivot Table Labels

In Excel2010, you can fill the tabs down in the PivotTable report, making it easier to use the PivotTable report. You can also repeat labels in a PivotTable report, displaying the item headings for nested fields in all rows and columns. You can repeat labels for individual fields, but you can also turn on or off the repeating label options for all fields in the PivotTable report.

For example, if each column in the PivotTable report contains a value field, and the totals and subtotals for all the fields on the row are closed, you can use the duplicate label feature.

Named sets

In Excel2010, you can create named sets of multiple hierarchies. Named sets can be used to extract specific sets of information. If you are unfamiliar with the Multidimensional Expressions (MDX) language, you can use a dialog box to define a simple named set. If you are familiar with MDX, you can use the MDX Editor to create more advanced named sets. Both PivotTable and OLAP formulas support named sets. Named sets allow you to generate an OLAP PivotTable report that displays different metrics for different business areas.

Project Search

EXCEL2010 provides an item search in a PivotTable report that enables users to work with fields and columns that contain a large number of items. With Project search, users can find related items in thousands of or even millions of rows in a PivotTable report. You can use an item search to find a pivot field or an OLAP cube field item heading in a single column, when AutoFilter is turned on or off.

Sort

EXCEL2010 provides a multithreaded sort that allows you to quickly sort large amounts of data in PivotTable and Excel tables. You can turn on or off multithreaded sorting.

Screening

The filtering function has been improved to analyze a large amount of data. In Excel2010, filtering is significantly faster for multiple items, and when you apply filters to OLAP PivotTable and non-OLAP PivotTable reports, invisible data, such as hidden items in totals, is also included in the filter range.

In Excel2010, you can also choose to use slicers to filter the data. Click the button provided by the slicer to filter the PivotTable data. In addition to providing quick filtering, slicers can indicate the current filter state, making it easy for users to understand exactly what is displayed in a filtered PivotTable report.

Value Display method feature

In Excel2010, the value display feature is easier to find and use, and its functionality in OLAP and non-OLAP PivotTable reports is improved.

Undo support for large PivotTable reports

Some undo operations, such as a refresh operation, add multiple items to the undo stack, which can significantly degrade performance in large PivotTable reports. To enhance performance in large PivotTable reports, the Undo command now implemented can support a larger undo stack. In addition, if you apply AutoFit and style while updating or refreshing a PivotTable report, performance decreases. To resolve this problem in Excel2010, press ESC to cancel AutoFit and style. When you refresh and update data in a large PivotTable report, closing these options can enhance performance.

New and improved features in an OLAP PivotTable report: Calculated fields

In Excel2010, you can add some calculations to an OLAP PivotTable report by creating a calculated field. If you are unfamiliar with the Multidimensional Expressions (MDX) language, you can use dialog boxes to define simple calculated fields. If you are familiar with MDX, you can use the MDX Editor to create more advanced calculated fields. By using a calculated field that you can create yourself, you can analyze the data in depth without having to rely on people in it or other people with programming knowledge.

New and improved features in OLAP PivotTable reports: Writeback support

Write-back is an important feature for processing data in an Analysis Services multidimensional data set. From high-end planning and budgeting to simple multi-user data collection, it is often used in a variety of scenarios to write back.

In Excel2010, you can change the value in an OLAP PivotTable value range and write it back to the Analysis Services cube on the OLAP server. You can use writeback in analog mode, and you can either roll back the changes or save the changes when you no longer need them. The write-back feature can be used for any OLAP provider that supports the UPDATE CUBE statement.