In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #3 of N:
a. How to remove Grand Totals & Sub Totals?
1. Select the Pivot Table.
2. From the toolbar, go to Pivot Table Tools > Design > Sub Totals > Do not Show Sub Totals.
you can also choose to remove it just for rows or columns. it depends on your requirement on how to layout data.
Similarly, you can remove Grand Totals:
There’s also an alternate method for removing subtotals. Put your mouse over the hierarchy level for which you do not need subtotals. Right click > remove subtotals “field name”. But you’ll have to do that for all hierarchy levels if you need to remove all subtotals.
b. How to configure Automatic Data Refresh?
A common question asked by power user is how to make sure that the excel file is pulling the latest data from the cube? Good news for them is that Excel files that are configured to connect to a data source like SSAS cube can be configured to automatically refresh. Here’s how:
1. From the toolbar, Go to Data > Connections
In this post, we saw how to remove grand totals & subtotals and, how to configure the automatic data refresh.