A tutorial on using pivot tables in Excel based on SSAS tabular models
Part three of an eight-part series of blogs

Learn how to create, format and explore pivot tables within Excel based upon SSAS tabular models. This tutorial also shows how to create slicers, and combine them to create dashboards.

  1. Excel pivot tables in Analysis Services (Tabular Model)
  2. Setting up pivot tables in Excel
  3. Refreshing a pivot table (this blog)
  4. Formatting numbers in a pivot table
  5. Formatting a pivot table and choosing display options
  6. Drill-down and Quick Explore
  7. Slicers
  8. Things you can't do in tabular model pivot tables

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 24 November 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Refreshing a pivot table

There are two reasons you might want to refresh a pivot table: the underlying data has changed, or the underlying model has.  These two types of refresh are dealt with under separate headings below.

Refreshing data

You can refresh a pivot table by right-clicking on it:

Refresh menu

Right-click and choose this option to bring a pivot table's data up to date.

 

You can do the same thing using the ribbon:

Refresh tab on ribbon

Choose Refresh All only if you want to update not just this pivot table, but any other external data linked to from within this workbook.

 

Refreshing the pivot table structure

When you make a change to a tabular model aggregator column or its table, this will automatically show up in Excel when you refresh:

Renaming a column

Here we've renamed the Region field to RegionName.

 

 

After making a change to your tabular model, however, you have to refresh a pivot table to bring it up to date:

Refreshing pivot table

Right-click to bring your pivot table up to date.

Excel may not cope with the change you've made that well:

Region removed

Rather than changing the region field's name, Excel has removed it from the pivot table.

 

Users of PowerPivot will be disappointed to note that Excel doesn't automatically detect changes made to the underlying data model, and doesn't auto-refresh.

The next two parts of this blog looks at how to control how your pivot table looks, by formatting it and choosing display options.  We'll start with how to change number formatting.

This blog has 0 threads Add post