Feature differences between OLAP and non-OLAP source data
If you work with PivotTable reports and PivotChart reports from both OLAP source data and other types of source data, you will notice some feature differences.
Data retrieval: An OLAP server returns new data to Excel every time that you change the layout of the report. With other types of external source data, you query for all the source data at once, or you can set options to query only when you display different report filter field items. You also have several other options for refreshing the report.
In reports based on OLAP source data, the report filter field settings are unavailable, background query is unavailable, and the optimize memory setting is not available.
Note: The optimize memory setting is also not available for OLEDB data sources and for PivotTable reports based on a cell range.
Field types: OLAP source data, dimension fields can be used only as row (series), column (category), or page fields. Measure fields can be used only as value fields. For other types of source data, all fields can be used in any part of a report.
Access to detail data: For OLAP source data, the server determines what levels of detail are available and calculates summary values, so the detail records that make up summary values may not be available. The server may, however, provide property fields that you can display. Other types of source data don't have property fields, but you can display the underlying detail for data field values and for items, and you can show items with no data.
OLAP report filter fields may not have an All item, and the Show Report Filter Pages command is unavailable.
Initial sort order: For OLAP source data, items first appear in the order in which the OLAP server returns them. You can then sort or manually rearrange the items. For other types of source data, the items in a new report first appear sorted in ascending order by item name.
Calculations: OLAP servers provide summarized values directly for a report, so you cannot change the summary functions for value fields. For other types of source data, you can change the summary function for a value field and use multiple summary functions for the same value field. You cannot create calculated fields or calculated items in reports with OLAP source data.
Subtotals: In reports with OLAP source data, you cannot change the summary function for subtotals. With other types of source data, you can change subtotal summary functions and show or hide subtotals for all row and column fields.
For OLAP source data, you can include or exclude hidden items when you calculate subtotals and grand totals. For other types of source data, you can include hidden report filter field items in subtotals, but hidden items in other fields are excluded by default.
