With the upcoming release of SQL Server 2012, the new and cooler way to connect to a PowerPivot workbook within SharePoint is to use Power View which addresses the concept of Self Service Reporting (similar to how PowerPivot addresses Self Service BI). I even have a cool YouTube video showing how to connect Power View to Hadoop on Azure: https://dennyglee.com/2012/02/10/connecting-power-view-to-hadoop-on-azurean-awesomesauce-way-to-view-big-data-in-the-cloud/
.
Why Report Builder 3.0 when there is Power View?
Nevertheless, there are some situations where SQL Server 2008 R2 tool – Report Builder 3.0 – can still be quite helpful. For example, if you need to export out the values from your table or overlay the report on top of a map, you can do this in Report Builder 3.0 (but not in Power View – at least not yet).
.
Changes for SQL Server 2012
For SQL Server 2008 R2, to install Report Builder 3.0 with SharePoint 2010, there were a number of manual steps on how to do this. For more information, check out: PowerPivot for SharePoint: Single Server Installation. The good news is that the installation is easier for SQL Server 2012 because Report Builder is a stand-alone install package that does not require server.
But, it is a little more complicated to make a connection to a PowerPivot workbook from Report Builder 3.0 as the “create the report” option is for Power View (the middle icon, left is the Excel report, right is the Data Refresh).
.
So how do I connect Report Builder 3.0 to a SQL Server 2012 PowerPivot workbook?
Software Involved:
- SQL Server 2012
- SharePoint 2010 Enterprise SP1
- Report Builder 3.0 (SQL Server 2012 Reporting Services version)
- PowerPivot V2 (SQL Server 2012 Analysis Services version)
.
1) For starters, download and install the SQL Server 2012 Reporting Services version of Report Builder 3.0. http://www.microsoft.com/download/en/details.aspx?id=29072
.
2) Configure SharePoint 2010 to allow the management of content types (in some cases, this is already configured).
- Using your browser, go to the SharePoint library that you want to configure (e.g. PowerPivot Gallery)
- Go to the SharePoint 2010 Library ribbon and click on Library Settings
- Click on Advanced Settings which is under the General Settings frame
- Under Content Types, ensure that the Allow management of content types is set to Yes.
.
3) Include Report Data Source is included in the available Content Types.
- From the SharePoint 2010 Library ribbon, click on Library Settings in the same SharePoint library (e.g. PowerPivot Gallery)
- Under the Content Types frame, click on Add from existing site content types. The Select Content Types dialog appears, add the Report Data Source (scroll down to it in the “Available Site Content Types”) and click Add.
- When you are done, under the Content Types frame, you should see the Report Data Source now available.
.
4) Now that you have configured the availability of the Report Data Source content type, go back to the SharePoint 2010 Library ribbon, click on Documents, and then click on New Document. Now you can click on Report Data Source.
.
5) The new document page (for a data source) will appear as noted below. They data source parameters to configure are:
Data Source Type: Since you are connecting to a SQL Server 2012 PowerPivot workbook (that you have already uploaded to SharePoint library), the data source type is Microsoft SQL Server Analysis Services.
The Connection String is in the format of:
Data Source=http://server/PowerPivot%20Gallery/PowerPivot%20Workbook.xlsx.
Note the use of %20 to replace any spaces. In the example below, the connection string is:
Data Source=http://intranet.contoso.com/SelfService%20BI/PowerPivot%20Mobile%20Hive%20Sample.xlsx.
.
6) Now that you have created a Report Data Source, you can now connect Report Builder 3.0 to it.
- Open up Report Builder 3.0 (from the Start menu) and it will give you the Getting Started dialog
- Click on you’re the Wizard of your choice to start creating your report. With a new table or matrix, Report Builder will first ask for you to create a dataset or choose an existing dataset. Since it’s your first time, choose the default of create a dataset and click Next.
- Because you had already created a Report Data Source in Step 5, click on Browser, and go to the location of your report data source (e.g. http://server/PowerPivot%20Gallery). Click to open it.
- Now that you’ve chosen your data source
- Click Next and you’re back to familiar territory of Designing your Query in Report Builder 3.0. To know more about how to work with Report Builder, check out the primer Getting Started with Report Builder 3.0.
.
Discussion
From the Report Builder application, you can create maps, charts, and/or tables which can also be exported as noted in the screenshot below.
Now that I have built my Report Builder 3.0 report, I can upload it to SharePoint (like any other document). When you click to Save the report, by default it will connect to the SharePoint farm you were originally connected to.
In this case, I have saved the report in my PowerPivot Gallery; when I go back to it, I can access the Report Builder report I had just created. And just like in the stand-alone application, from SharePoint I can export the table values of my Report Builder report.
.
Final
I hope this How to guide has been helpful on how to connect Report Builder 3.0 to a SQL Server 2012 PowerPivot workbook. And thanks to Robert Bruckner for setting me straight on some of this stuff, eh?!
Enjoy!
.
Denny Lee 