When you use a Microsoft Excel PivotTable to view a SAS^® OLAP cube, you cannot deselect the Include Hidden Items in Totals option in order to apply the VisualTotals method to the results. The SAS OLAP Data Provider does not support this option. If you attempt to deselect this option, you might see the following error:

There are two workarounds for this issue:

• When using a Microsoft Excel PivotTable, you can use the Report Filter section to filter the data in the PivotTable. When you use Report Filter section, you can select a single value at a time to display the expected totals.
• Instead of a PivotTable, you can use the SAS^® OLAP Analyzer available with the SAS^® Add-In for Microsoft Office. SAS clients, such as the SAS add-in, generate a query that correctly applies the VisualTotals function to the results. Right-click the table and select Totals in order to specify either Parent Totals or Visual Totals.

Note: Additional examples of clients developed in SAS^® include SAS^® Web Report Studio and the OLAP Viewer available in SAS^® Enterprise Guide^®.

Operating System and Release Information