Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile
• My SAS

• Print  |
• Feedback  |
• Share

  • E-mail
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

In Microsoft Excel, you can uncheck the option Include Hidden Items in totals to indicate that totals should use the VisualTotals method. However, the current SAS OLAP Data Provider does not support this option.

One side effect of this behavior is that Microsoft Excel generates a query that might include empty cells even when the option to remove empty rows or columns is enabled. This is because the query being generated by Microsoft Excel includes data from other levels of detail that might not be NULL. An example of this is when a value at a parent level is non-NULL, but one or more children contains a NULL result. This can be confirmed by capturing the query generated by Microsoft Excel in your SAS OLAP Server log (see Setting additional debug options for the SAS® OLAP Server 9.2), and then submitting that query in a different MDX client such as SAS^® Enterprise Guide^® or the SQL pass-through facility.

Clients that are developed in SAS do not encounter this issue because they generate a query that can correctly apply the VisualTotals function to the results. Examples of clients developed in SAS include the OLAP Viewer available in the SAS^® Add-in for Microsoft Office 4.3, SAS Enterprise Guide, and SAS Web Report Studio.

As a workaround, 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.

Operating System and Release Information

A fix for this issue for Base SAS 9.21_M3 is available at: