Advanced Report Features

From DataSelf Knowledge Base
Jump to navigation Jump to search

Back to Excel Reporting

Slicers (Excel 2010 and later)

Basic Slicer for one PivotTable

Video: http://office.microsoft.com/en-us/excel-help/video-use-slicers-to-filter-pivottable-data-VA101267315.aspx?CTT=1
(our video?)

Multi-level Hierarchy Slicers

A Multi-level hierarchy, such as the Date Document hierarchy, may have several slicers, for Year, Quarter, Period (month) and Date. In this case, also add the hierarchy to the Report Filters section, to make it easier to read exactly what is selected.

You can select one or more members of one of the levels, and that will control the filter, and will cause the slicers at the other levels to also change their selection.

One slicer to control multiple PivotTables

PivotTables that share slicers must be:

1. From the same data source (same cube)
2. Use the same data Connection
3. All linked to the selected slicers

The easiest way to do this is:

1. Create the first PivotTable with the desired slicers.
2. Copy/paste the PivotTabe as many times as needed.
3. Change the layouts (the rows, columns, measures, etc) of each PivotTable.
(Make a short video)

Add other calculations

Percentages of Total, Ranking, Running Totals: Show values as (Excel 2010 and later)

Video: http://office.microsoft.com/en-us/excel-help/video-use-the-show-values-as-feature-in-a-pivottable-report-VA101812176.aspx?CTT=1

Adding formulas to the side

Leave room, or add to the left, so it doesn’t get overrun
Turn off Generate GetPivotData:
Click the Options tab, click Options in the PivotTable group, and then click to clear the Generate GetPivotData check box.
More information: http://support.microsoft.com/kb/287736

Convert to formulas

Totals to include all items, even filtered ones

Click in the pivot table. Right-click > PivotTable Options > Totals & Filters tab.

See also: For Excel 2010 http://office.microsoft.com/en-us/excel-help/filter-items-in-a-pivottable-report-HA010342516.aspx#_Toc267658787 The section "Include or exclude filtered items in totals"

For Excel 2007: http://office.microsoft.com/en-us/excel-help/subtotal-and-total-fields-in-a-pivottable-report-HP010096317.aspx#BMcalculate_the_totals_with_or_without_

Sort by rows, columns and measures

Sort by measures

https://dataselfsupport.com/attachments/78/SortRowsByMeasureValue.pdf

Sort by row and column labels

Additional sorting of row and column labels

Video: http://office.microsoft.com/en-us/excel-help/video-sort-items-in-a-pivottable-report-VA101812190.aspx?CTT=1

Named Sets

Do you always need to select the same subgroup of items for your rows or columns? If this is a grouping that others in your organization would find useful, best practice would be to have the set added to the cube itself. But for ad hoc sets that you can easily maintain, you can create your own named sets. Video: http://office.microsoft.com/en-us/excel-help/video-use-named-sets-in-an-olap-pivottable-report-VA101812172.aspx?CTT=1

Other Sites with helpful tips

http://www.excel-erate.biz/excel/excel_index_alpha.htm

Totals to include all items, even filtered ones

Click in the pivot table. Right-click > PivotTable Options > Totals & Filters tab.

See also: For Excel 2010 http://office.microsoft.com/en-us/excel-help/filter-items-in-a-pivottable-report-HA010342516.aspx#Toc267658787
The section “Include or exclude filtered items in totals”

For Excel 2007: http://office.microsoft.com/en-us/excel-help/subtotal-and-total-fields-in-a-pivottable-report-HP010096317.aspx#BMcalculate_the_totals_with_or_without

Sort by rows, columns and measures

Sort by measures

https://dataselfsupport.com/attachments/78/SortRowsByMeasureValue.pdf

Sort by row and column labels

Additional sorting of row and column labels

Video: http://office.microsoft.com/en-us/excel-help/video-sort-items-in-a-pivottable-report-VA101812190.aspx?CTT=1

Subtotals

Using Properties to Improve Performance (make the cube refresh more quickly)

When you hover the mouse over a row member, a yellow pop-up window displays more information about that member. These are “properties”. You can also use the properties as part of the pivot table itself. Properties just display more information about the selected member, but you cannot filter or sort on properties.

Use Properties to display the Description field in a separate column after the ID. Use them to display other details about the dimension member in the row. For example, use them to display address information for a customer. Because properties do not affect which data is selected for the pivot table, adding them to the report does NOT slow the report refresh time (does not affect performance).

  1. Click the row field you want properties for
  2. Options tab > Tools > OLAP tools > Property Fields
  3. Select the desired properties, re-arrange the display order as needed, and OK
  4. Once in the pivot table, you can edit the property column name

For more details:
http://office.microsoft.com/en-us/excel-help/display-or-hide-screentips-and-properties-in-a-pivottable-or-pivotchart-report-HA010177752.aspx
and click down to the section: Display member properties as data in a PivotTable report

Named Sets

Do you always need to select the same subgroup of items for your rows or columns? If this is a grouping that others in your organization would find useful, best practice would be to have the set added to the cube itself. But for ad hoc sets that you can easily maintain, you can create your own named sets.
Video: http://office.microsoft.com/en-us/excel-help/video-use-named-sets-in-an-olap-pivottable-report-VA101812172.aspx?CTT=1

Other Sites with helpful tips

http://www.excel-erate.biz/excel/excel_index_alpha.htm