Getting started with Power View Reports with SharePoint, Excel and SQL Server

By:   |   Comments (8)   |   Related: > Microsoft Excel Integration


Problem

Microsoft Business Intelligence stack provides several tools for analyzing data from different perspectives, in different format and different ways. Power View is a new addition to this arsenal which provides intuitive ad hoc reporting capabilities for business users to provide an interactive data exploration, visualization, and presentation experience. But how does it work and how to get started with it?

Solution

Power View is a new tool for intuitive ad hoc reporting which provides an interactive data exploration, visualization, and presentation experience to the business users. Business users can quickly and easily create and interact with different views of data from different angles. Power View reports can be based on data models on PowerPivot workbooks or tabular models or multi dimensional models (this requires installation of SQL Server 2012 Service Pack 1 Cumulative Update 4) deployed to SQL Server 2012 Analysis Services (SSAS) instances.

Power view was first introduced in SQL Server 2012 as Reporting Services Add-in for SharePoint 2010 and 2013 (enterprise edition) as thin browser-based Silverlight application which worked on data models based on PowerPivot workbooks or tabular models deployed to SQL Server 2012 Analysis Services (SSAS) instances. Though now, with the release of SQL Server 2012 Service Pack 1 Cumulative Update 4 (adds native support of Data Analysis Expressions (DAX) queries in Analysis Services Multidimensional Models), you can create Power View reports based on multi dimensional models (cube) as well. You can find more detail here.

You can also create Power View reports in Excel 2013 (available in Office Professional Plus and Office 365 Professional Plus editions) if you don't have or don't want to use SharePoint. I am going to demonstrate creating Power View reports in Excel 2013 in my next tip. You can learn about this here.

To learn about the system requirement click here and here.

Setting up SharePoint farm

You first need to active the "PowerPivot Integration Feature" by going to SharePoint Central Administration -> Manage farm features and clicking on Active as shown below:

Setting up SharePoint farm

Creating a site based on Business Intelligence Center template

If you have a site already created you can skip this step or else please create a site using "Business Intelligence Center" template as shown below. If you are creating a site in SharePoint 2013, you will have an option to chose the user experience of SharePoint 2010 or SharePoint 2013.

Creating a site based on Business Intelligence Center template

Please note, I am using SharePoint 2013 for demo and hence user experience might be slightly different if you are using SharePoint 2010.

Getting started with Power View Reports

Now open your site in the Internet Explorer and it should look like this. If you have site in SharePoint 2010, it will have slightly different look.

Getting started with Power View Reports

Click on the Site Contents link available on the left side and then click on SETTINGS link available on the right side.

click on SETTINGS link available on the right side

On the site settings page, under Site Collection Administration group click on the Site Collection features as shown below:

under Site Collection Administration group click on the Site Collection features

Ensure you have "Power View Integration Feature" and "PowerPivot Feature integration for Site Collections" features activated, if not please activate it.

Ensure you have "Power View Integration Feature" and "PowerPivot Feature integration for Site Collections" features activated

Now we need to add a PowerPivot Gallery document library. Click on "add an app" link as shown below:

add a PowerPivot Gallery document library

Enter Power in the search box and search for it. You will notice PowerPivot Gallery library template as shown below as part of the search result. Click on the PowerPivot Gallery link to add the PowerPivot Gallery library.

You will notice PowerPivot Gallery library template as shown below

On the next screen you need to specify the name for the PowerPivot Gallery that you want to create as shown below. In my case I have named it Power View Gallery and click on Create button to create it.

specify the name for the PowerPivot Gallery

Now if you click on the Site Contents link again you will notice newly created library (I named it "Power View Gallery") as shown below. Click on it to open the newly created library.

Click on it to open the newly created library

Once you have library open, you can go to FILES as shown below and you will notice New Document option is disabled. In order to enable it, follow the next step: 

go to FILES as shown below

Now click on the LIBRARY on the ribbon and then click on the "Library Settings" icon\link on the right side as highlighted below:

 click on the "Library Settings" icon\link on the right side

On the library settings page, click on the Advance Settings link and then on the Advance Settings page check "Allow management of content types" to Yes as shown below and save it.

click on the Advance Settings link and then on the Advance Settings page check "Allow management of content types"

Now if you go back to library and look under FILES ribbon as shown below you will notice "New Document" link is enabled but it allows to create PowerPivot Gallery document only.

under FILES ribbon as shown below you will notice "New Document" link is enabled

You need to add content types in order to create Power View reports. Go to Library settings again and click on "Add from existing site content types" as shown below:

You need to add content types in order to create Power View reports

On the Add Content Types page, select "BI Semantic Model Connection" and "Report Data Source" and click on Add to add them. Click in Ok the save the settings.

select "BI Semantic Model Connection" and "Report Data Source" and click on Add to add them

Now if you go again to the library you will notice that it allows you to create a "PowerPivot Gallery Document", "BI Semantic Model Connection" and "Report Data Source" under New Document menu as shown below:

create a "PowerPivot Gallery Document", "BI Semantic Model Connection" and "Report Data Source"

If you want to create Power View reports based on tabular model you need to create BI Semantic Model connection and if you want to create Power View reports based on multi dimensional models (cubes) you need to create Report Data Source as shown below. Please remember you need to create shared Report Data Source (RSDS file) for connecting to multi-dimensional cube. The connection string for the Report Data Source should look like this Data Source=;Initial Catalog=;Cube='.

create BI Semantic Model connection

Once connection created, you can open the gallery (we named it Power View as above) that we created and you will notice the connection as shown below. You can click on this connection to create reports based on this connection. 

click on this connection to create reports based on this connection

This is how the Power View report designer will look. You can find the field list on the based on the connection it is based on and you can design report the way you want from now on.

This is how the Power View report designer will look

In this article, I talked about how you can configure to get started with creating Power View reports and my next tip I am going to demonstrate how you can create different types of reports in Power View based on the connection we created. 

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, November 19, 2015 - 10:33:48 AM - veera Back To Top (39103)

i was trying to connect normal sQL SERVER GETTING BELOW ERROR

 

<detail><ErrorCode xmlns="http://www.microsoft.com/sql/reportingservices">rsInvalidDataSourceType</ErrorCode><HttpStatus xmlns="http://www.microsoft.com/sql/reportingservices">400</HttpStatus><Message xmlns="http://www.microsoft.com/sql/reportingservices">The current action cannot be completed. The data extension type for data source 'http://vmnwt1305/sites/vi/PowerPivot/PowerPivot Gallery/SqlTestNew.rsds' is not valid for this operation.</Message><HelpLink xmlns="http://www.microsoft.com/sql/reportingservices">http://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsInvalidDataSourceType&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.5058.0</HelpLink><ProductName xmlns="http://www.microsoft.com/sql/reportingservices">Microsoft SQL Server Reporting Services</ProductName><ProductVersion xmlns="http://www.microsoft.com/sql/reportingservices">11.0.5058.0</ProductVersion><ProductLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">127</ProductLocaleId><OperatingSystem xmlns="http://www.microsoft.com/sql/reportingservices">OsIndependent</OperatingSystem><CountryLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId><MoreInformation xmlns="http://www.microsoft.com/sql/reportingservices"><Source>ReportingServicesLibrary</Source><Message msrs:ErrorCode="rsInvalidDataSourceType" msrs:HelpLink="http://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsInvalidDataSourceType&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.5058.0" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">The current action cannot be completed. The data extension type for data source 'http://vmnwt1305/sites/vi/PowerPivot/PowerPivot Gallery/SqlTestNew.rsds' is not valid for this operation.</Message></MoreInformation><Warnings xmlns="http://www.microsoft.com/sql/reportingservices" /></detail>


Monday, December 8, 2014 - 12:40:09 PM - Mehdi Back To Top (35546)

Hi Arshad,

thanks for sharig, everything works great expect the last step. After creating the connection, when I click on it to normally create a PowerView Report it only reopen the connection edit page.

How to create a report then ?

Thanks.

Mehdi


Thursday, October 9, 2014 - 2:03:35 PM - Shahid Mashedy Back To Top (34914)

Keep up the good work. wish you more sucess.


Thursday, September 25, 2014 - 8:25:08 AM - Stefano Back To Top (34711)

I Arshad,

 

maybe you can help me. I'm trying to activate power view in our SharePoint environment, to od that I activated everything (services, powerpivot, ecc.).

Now I'm trying to build some report starting from multidimensional models (our SharePoint SqlServer has been updated to SP2 two days ago).

I followed precisely your suggestions, the difference is that I use Stored credential as Windows credentials and "set the execution context on this account", when I test the connection the system answers as follow:

 

"XML for Analysis parser: The 'DOMAIN\myUserName' value of the 'EffectiveUserName' XML for Analysis property is not valid."

 

I don't know what else to do, I hope you have some more suggestions for.

 

Thank's in advance.

 

Stefano


Thursday, August 21, 2014 - 11:51:34 AM - Mike Holmes Back To Top (34224)

I have installed it all and got the Data Source setup, but when I click on this to get into the designer it simply goes back into the data source setup, I am unable to get into the designer


Wednesday, July 9, 2014 - 10:57:51 AM - Bisi Back To Top (32607)

When are you sharing the next tip: "how you can create different types of reports in Power View"

Thanks,


Wednesday, February 19, 2014 - 12:25:30 PM - KALLEL Ahmed Back To Top (29505)

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting

 

 

Hi,

 

i have do all thing in document but when i want make 'Test Connection' , i have this message :

 

La connexion a expiré ou a été perdue

 

Can you help me ?

 

Thanks


Wednesday, September 25, 2013 - 9:41:00 AM - Nutan Patel Back To Top (26933)

Hi Arshad,


I have created report data source and it show me message that connection created successfully but i am going to create power view report it give me this error:
"A communication error occurred within the Reporting Services endpoint on this SharePoint site. If the error persists, contact the SharePoint site administrator for help."

Can you please help me?

 

Thanks

NP















get free sql tips
agree to terms