SQL Server 2012 Reporting Services introduced Power View, which was initially available only in SharePoint. This limited the use of Power View only to those individuals and organizations that had SharePoint deployed in some environment. To make Power View more widely available it is now an add-in for Excel 2013. Now anyone with Excel 2013 can create reports and dashboards using Power View. This leads me to the story.

Recently I was working with a customer and they were leveraging Excel to create Power View reports using an SSAS Tabular model as the source data. While this a good choice the problem was that they could not figure out how to connect multiple Power View reports in the same work book to a single tabular model. Now if you have ever worked with data in Excel you should be familiar with the Import Data window, which can be seen below.

When you initially connect to a tabular model using Excel 2013 the Import Data window gives you a few choices, one being Power View. After selecting Power View a new tab is added, but more importantly to the right of the screen you will see the Power View Field list, as seen below:

This is pretty simple and intuitive. Most Excel users can quickly figure this out. However, what if you want to add another sheet connected to the same tabular model? How is that accomplished? Well first click Insert in the ribbon, move the cursor to the right and you will see the Power View Reports button. If you click it a new Power View sheet is added, but you may notice that the Power View Field list resembles the following:

What the….. Where is the data? How do I connect to the existing tabular model connection? Well the first thing that I see most people do is click Data in the ribbon, but most of the items or greyed out. What next? The first step is to go to a regular sheet in the workbook. You know, one with rows and columns. Then click on Data in the ribbon. You should see most of the items enabled. Locate and click the Existing Connections button. The Existing Connections window will open, which can be seen below:

At the very top you will see a list of connections that are in the workbook. Select the connection of choice and click the button labeled Open. When you do the Import Data window will appear. Simply choose Power View from the list of available choices and a new Power View sheet will be added to the workbook. Most importantly, you will have a field list that is fully populated with the information from tabular model.

Now you can create several Power View reports based on a single connection. As always email me at pleblanc@sqllunch.com if you have any questions or concerns regarding this post.

Talk to you soon,

Patrick LeBlanc