Creating a Power View Report in Excel 2013 Which Uses an External Data Connection

Overview:  Quick tip about how to insert a Power View worksheet in Excel 2013 that points to an external data connection (as opposed to an internal PowerPivot model).  Also includes a tidbit at the end about why the behavior of data connections makes Power View unable to be used for snapshot reporting.

What Doesn’t Work With an External Data Connection

You’ve probably noticed on Excel’s Insert menu a choice for Power View.

    

The Insert menu works beautifully when a PowerPivot model is embedded within the same Excel workbook that is open.  However, if an embedded PowerPivot model is not available, instead you get a message:  “Power View needs data to work with.”

    

So, let’s investigate a different technique in order to make an external connection to an Analysis Services Tabular model.

Connecting to an External Data Source

If your connection already exists, choose it in Existing Connections and skip to Step 4.

1.  On the Data menu, choose From Other Sources > From Analysis Services.

    

2.  Select the Tabular Model you want, then Next.

    

3.  Modify the File Name and Friendly Name if you’d like, then Finish.

    

4.  Change the radio button to Power View Report (instead of the default PivotTable Report).

    

You probably also want to visit the Properties pane.  By default, the checkbox to “Refresh data when opening the file” is not selected. 

    

If you leave the “Refresh data when opening the file” option unchecked (which is the default), when the file is reopened Power View will render a message “Please refresh to see the data for this Power View sheet.” Doing a Refresh fixes it, but it’s one extra click every single time you open the file.

    

Inherently what this means is that Power View cannot be used as a tool for snapshot reporting.  With an Excel PivotTable, unless you choose to refresh the data you see the last saved state.  Power View, however, does not behave the same way – it won’t render the last saved state.  It must be refreshed.  Therefore, this means to save a snapshot at a point in time the report would have to be exported.

To change the setting after it’s been set up, go to Connections on the Data menu, then choose Properties.