A tutorial on how to create Power View reports based on SSAS tabular models
Part three 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
  3. Creating and formatting basic visualisations (tables) (this blog)
  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 and formatting basic visualisations (tables)

It's easy to do things in Power View, although you may (I do) get frustrated by its formatting limitations.

I'm a big fan of PowerPivot and SSAS Tabular, but Power View always seems to me as if it hasn't quite been finished!  See if you agree ...

Creating a table visualisation

The generic word for a table, matrix, chart or card in Power View is a visualisation.  You can create a simple table visualisation just by selecting columns:

Selecting columns

Select the columns from the list of fields in your model.  Power View will aggregate the measures chosen.

 

If you can't see the field list shown above, you may need to select this button:

Field List

Click on this icon on the Power View tab of the Excel ribbon to display your model's field list.

 

Power View will show which fields you've chosen at the bottom right:

The chosen fields

Here we've chosen 3 fields: 2 aggregator columns and a measure.

 

Power View creates a table automatically for you:

The created table

The table created for this combination of columns.

 

The rules are: if you have an existing visualisation (such as a table) selected, Power View will add any fields you tick to it; if you don't have a visualisation selected, Power View will create a new table when you tick a field.

Selecting, moving and deleting visualisations

To move any visualisation, position the mouse pointer on any edge of it and wait for this hand symbol to appear, then click and drag:

The selection hand

Here we've succeeded in getting the hand symbol visible, and could now click and drag to move the table.

 

To delete a table, first click in it to select it:

Selected table

This table is selected, believe it or not!

 

You can then just press the Delete key to delete the table.

A note on undoing things

Power View has excellent support for the undo feature, but be careful not to try to use the Excel quick access toolbar to undo:

Undo tool

The undo tool on the Power View ribbon works, but the smaller one in the quick access ribbon doesn't. As always, the easiest way to undo anything, however, is to press CTRL + Z.

 

Sorting tables and other visualisations

Click on any column heading to sort by that column:

Sorting a column

Click on a column heading to sort by that column; click a second time to reverse the sort order.

 

Formatting tables and other visualisations

You can't add or edit borders in Power View.

To change the format of (for example) a column, first select it:

Selecting a column

Here I'm going to display commas when the total quantity sold for a region is 1,000 or more.

 

You can now change the format of the thing you have selected

Design formatting tools

Click on the tool shown to display commas, then reduce the number of decimal places using the tool to its right.

 

You're probably now wondering where all of the other formatting tools are.  The answer is that they don't exist - that's it!

Re-ordering or deleting columns (or making any other changes)

For virtually all other changes to visualisations, the secret is to use the FIELDS at the bottom of the field list:

FIELDS section

Click and drag a field to change where it appears.

 

To delete a field from a table or other visualisation, choose this option:

Removing a field

Click on the drop arrow to the right of any field and choose the option shown to remove it.

 

Popping out and in

One of the oddest options in Power View allows you to make a single visualisation temporarily fill your entire report, so that you can edit it easily:

Pop out

Click on a visualisation to select it, then click on this icon at the top right to "pop it out".

When you've finished making your changes, you can tell the table to resume its normal size by "popping it back in":

Pop in

Click on this tool to pop a visualisation back to where it was!

 

And on that high note, we've finished looking at basic tables.  Time to take a brief digression and look at text boxes and images in Power View.

This blog has 0 threads Add post