ERPJEWELS.COM

If you work with PivotTable and PivotChart reports from both OLAP source data (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.) and other types of source data, you will notice some feature differences. According to Microsoft help, following are the differences of OLAP source data with other external source data.

Data retrieval   An OLAP server returns new data to Microsoft Excel every time 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 page field items (page field: A field that's assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.). You also have several other options for refreshing (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) the report.

In reports based on OLAP source data, the page field (page field: A field that's assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.) settings are unavailable, background query is unavailable, and the optimize memory setting is not available.

Field types   For OLAP source data, dimension (dimension: An OLAP structure that organizes data into levels, such as Country/Region/City for a Geography dimension. In a PivotTable or PivotChart report, each dimension becomes a set of fields where you can expand and collapse detail.) fields have icons in the field list and can be used only as row (row field: A field that's assigned a row orientation in a PivotTable report. Items associated with a row field are displayed as row labels.) (series), column  (column field: A field that's assigned a column orientation in a PivotTable report. Items associated with a column field are displayed as column labels.)(category), or page fields. Fields with icons can be used only as data fields. For other types of source data, all fields have icons and can be used in any part of a report.

Renamed fields and items   For OLAP source data, renamed fields and items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) that you hide revert to their original names when you redisplay them. For other types of source data, fields and items retain their new names in these situations.

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 usually aren't available, and you can't show items with no data. The server may, however, provide property fields (property fields: independent attributes associated with items, or members, in an OLAP cube. For example, if city items have size and population properties stored in the server cube, a PivotTable report can display the size and population of each city.) 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 page fields may not have an All item, and the Show 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 (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.) for data fields. For other types of source data, you can change the summary function for a data field and use multiple summary functions for the same data field.

Both types of source data support custom calculations (custom calculation: A method of summarizing values in the data area of a PivotTable report by using the values in other cells in the data area. Use the Show data as list on the PivotTable Field dialog for a data field to create custom calculations. ).

You cannot create calculated fields (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.) or calculated items (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.) in reports with OLAP source data.

Subtotals   In reports with OLAP source data, you cannot change the summary function for subtotals, and you cannot display subtotals for inner row or column fields. 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 page field items in subtotals, but hidden items in other fields are excluded by default.

FastTrack to ERPJEWELS :

Latest Trend | Hot Links | Jewels of ERPJewels | Jewels of Baan | Jewels of Excel