Saturday, May 21, 2011

Create Reports Using PowerPivot Data Source

You can create new SSRS reports, Excel reports, or PPS Dashboard reports based on PowerPivot workbooks that are published in a SharePoint PowerPivot Gallery. PowerPivot data access on a server is supported through SharePoint 2010 with SQL Server 2008 R2.

SSRS Reports

You can use either Report Designer in BIDS or Report Builder to access the PowerPivot data.  The Reporting server could be in native mode or in SharePoint integrated mode.  Not like the Excel reports which include embedded data, SSRS reports always access PowerPivot data as an external data source.

In order to support PowerPivot connections from Report Designer, you must have the SQL Server 2008 R2 OLE DB Provider for Analysis Services installed.  Windows authentication is also required to integrate between the SSRS server and the PowerPivot server.  More information could be found at http://msdn.microsoft.com/en-us/library/ee210631.aspx.

Steps for SSRS Reports:

  • Create a new Report Server Project in Visual Studio 2008 or BIDS
  • Add new data source in Share Data Source

image

  • Using the Microsoft SQL Server Analysis Services Type with Connection String pointed to the SharePoint location for the PowerPivot spreadsheet, for example: Data Source="http://SharepointServerName/PowerPivot Gallery/PowerPivotDemo.xlsx"

image

  • Click on the Credentials tab to set the Authentication to be Windows and click on OK button.

image

  • Add a new report and select the Share data source just created, then click on Next.

image

  • Click on the Query Builder button

image

  • The Query Designer will be opened with a Sandbox cube

image

  • You now should be able to work on your MDX query to get your result as needed.

image

 

Excel Reports

You could also create new Excel reports to access the PowerPivot workbooks that you have permissions with.

Steps for Excel Reports:

  • Open Excel, go to Data tab and select From Data Connection Wizard on Other Sources menu

image

  • On the Data Connection Wizard window, select the Other/Advanced option and click on Next button

image

  • Select the Microsoft OLE DB provider for Analysis Services 10.0 and click on Next

imageimage

image

  • The Select Database and Table window will then be opened with Sandbox Cube been selected to be used. Click on the Next to save the data connection file and finish the wizard. Here’s what the connection string may look like:

Provider=MSOLAP.4;Persist Security Info=True;Initial Catalog=PowerPivotDemo Sandbox 6042a0d5-355c-47ed-b24c-d458bfecd529;Data Source=http://SharePointServerName/PowerPivot Gallery/PowerPivotDemo.xlsx;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Data Source Version=5/9/2011 5:27:11 PM

    image

  • You now should be able to use the Data from the PowerPivot data source to create your excel report

image

 

Performance Point Server Reports

Performance Point Server on SharePoint 2010 also supports PowerPivot data source.

Steps for PPS Reports:

  • Open PPS Designer and connect to the SharePoint site
  • Create a new Analysis Service data source

image

image

Since Excel Pivot Tables that go against PowerPivot data do not support the write back, offline cubes, and drill through,  there are some known issues when using the PowerPivot data source in PPS reports such as:

  • Show Details error

image_thumb9[1] image_thumb10[1]

  • Error running data source query. DrillThrough is not supported because the server is running in VertiPaq mode.

 image_thumb12[1]

 
Additional References:

PowerPivot Connection Type (SSRS)

Plan for PowerPivot Integration with Reporting Services

PowerPivot Features

ErrorCode Enumeration

2 comments:

  1. They are creating new excel report so that they can easily send their files and others thing through this.Another external data source is also included here.




    Cheap Tablet PCs

    ReplyDelete
  2. Hello everyone, have a nice article. I read this post and get lot of important news. I sharing this post some of my close friends and they accede to visit this site. Thanks
    Online Computer Store

    ReplyDelete