A tutorial on how to create Power View reports based on SSAS tabular models
Part two of a seven-part series of blogs

Power View is an add-in included within Excel 2013 and later versions. This tutorial shows how to create Power View reports based on Analysis Services tabular models.

  1. Overview - what is Power View?
  2. Creating your first Power View report (this blog)
  3. Creating and formatting basic visualisations (tables)
  4. Text boxes and images in Power View
  5. Filtering reports using tiles, slicers and filters
  6. Matrices and cards in Power View
  7. Charts in Power View

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 08 December 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.

Creating your first Power View report

To create a report using Excel Power View, you'll need to:

  •  be using the right version of Excel;
  • enable the Power View add-in, and
  • create a connection to your tabular model and create a report. 

Read on to see how to do all of this!

Enabling the Power View add-in

The first thing to do if you want to use Power View is to check that you can!  To do this you'll need to choose two options from the Excel File menu:

The File menu

The File menu in Excel 2013 (the 2016 version looks similar).

 

First choose Account from the File menu as shown above to check which version of Excel you have installed:

Office 365 ProPlus

You'll need to have either the Professional Plus edition of Excel installed or (as here) Office 365 ProPlus.

 

Next, choose Options to check you've enabled Power View and follow the numbered steps shown below:

Add-in Power View

Follow the numbered steps below.

The steps shown are:

  1. If Power View is listed as an enabled add-in (as here), there's no need to do anything else.  Otherwise ...
  2. Choose the Add-ins tab.
  3. Go to the COM Add-ins in Excel (whatever these may be!).
  4. Click on the Go... button

You can now tick Power View to enable it:

Power View add-in

If you don't see Power View listed, you may not be using the right version of Excel. In Excel 2013 and upwards Power View is included in the standard Office suite.

 

Creating a connection to your tabular model

Once you've ensured you can use Power View, you can create a connection to your tabular model.  Start by saying that you want to get data from Analysis Services:

Data from Analysis Services

Choose this option from the Data tab of the Excel ribbon.

Choose the Analysis Server workspace or database that you want to connect to:

Analysis Server database

Don't forget that this is the name of your development workspace or deployed SSAS database, not a SQL Server relational database.

Choose the model database that you want to connect to:

Choosing model database

The easiest thing to do now is to choose Finish, as this will automatically create a connection for you.

Finally, choose to create a Power View report and select OK:

Power View report

Choose this option to create a Power View report using this connection.

Power View takes a while to load:

Power View loading

You may be looking at this dialog box for a while ...

Finally, you'll see your Power View report:

Power View report

You can tick fields on the right which you want to include in your report (much more on this in the remainder of this blog).

I think it's a fair comment that Power View isn't as robust as the rest of Excel.  I recommend saving your work regularly!

The need to refresh

If you change the underlying tabular model for a Power View report (or even if you close and re-open its workbook) you will probably need to refresh it:

Refreshing power view report

Choose this option on the Power View tab of the Excel ribbon to refresh your report.

 

PowerPivot users will find this irritating, as they'll be used to PowerPivot updating automatically following model changes.

 

Now that you've created a Power View report, let's look at the basics: creating and formatting basic tables. 

 

This blog has 0 threads Add post