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

• 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"

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

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

• Click on the Query Builder button

• The Query Designer will be opened with a Sandbox cube

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

 

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

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

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

• Enter the data source in the Data Source textbox as : http://SharepointServerName/PowerPivot Gallery/PowerPivotDemo.xlsx and click OK

• 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

   

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

 

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

• Using the following connation string: Provider = molap; datasource = http://SharePointServerName/PowerPivot Gallery/PowerPivotDemo.xlsx
• In the Cube drop down box, select the sandbox cube

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

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

 

 

Additional References:

PowerPivot Connection Type (SSRS)

Plan for PowerPivot Integration with Reporting Services

PowerPivot Features

ErrorCode Enumeration

Enhance PowerPivot Cube using Visual Studio 2008 R2

PowerPivot is a self service BI tool that you may use it to prototype your work quickly. The SQL Server Analysis service that PowerPivot cube used is a VertiPaq storage mode which is different from the traditional  MOLAP, ROLAP or HOLAP cube. It is a read only temporary cube that PowerPivot Service creates and manages. You may persist or enhance it using Visual Studio 2008 (SQL Server 2008 R2). 

Here are the steps to enhance it using Visual Studio:

• Backup and Restore the PowerPivot Sandbox database with a different name on the VertiPaq Mode SSAS.
• Open the Analysis Service Database option to point to the restored database from Visual Studio to create a new project.

  

• Once the project is created, you could see all the dimensions and a cube called Sandbox in the project.

  

• The Data Sources contains a connection string that connects to the original data source used by the PowerPivot workbook.
• The Data Source Views contains all the tables from the PowerPivot workbook as Named Queries.
• Since it is a read only cube, if there is any modification or trying to process the cube, you will get the following error

• To overcome the read only issue, you need to detach and then attach it by unselecting the Read-only checkbox to change the ReadWrite mode to be ReadWrite.

 

• Now you should be able to add a new MDX or DAX measure and then deploy/process it.

• You could view your result in both the Visual Studio or SSMS.

• If you try to restore the PowerPivot Cube on a traditional SSAS Server, you will encounter the following error: Backup and restore errors: Restore failed. The backup file was created on a server with DeploymentMode=1 and the current server is running DeploymentMode=0. (Microsoft.AnalysisServices)

How to Pass Empty String in Entity Framework

While working with Data Warehouse project, most database columns were designed as required even thought there are no data to populate with. In that situation, an empty string or blank will be used. 

Here is an example: The Note column is required and could be populated with empty string if there is no additional information provided.

When using Entity Framework to insert or update this type of field by passing in a blank, we often encounter a “field is required” validation error as below:

To overcome this issue, we could add Data Annotations as:

[MetadataType(typeof(Report_META))]

   public partial class Report

   {

   }

   public partial class Report_META

   {

       [Required(AllowEmptyStrings = true)]

       [DisplayFormat(ConvertEmptyStringToNull = false)]

       public object Note { get; set; }

    }

When setting AllowEmptyStrings to true for a specific data field, the framework does not perform validation and transforms the empty string to null. This value is then passed to the database.

When setting ConvertEmptyStringToNull  to false, the framework will not automatically convert the empty string value to null when the data field is updated in the database.

After adding the Data Annotations, we could use the blank/empty string to populate or modify the Note field now.

Change SSRS Report Layout to keep the Column Headers Together

I created a SSRS report with 8 columns. When exporting the report in PDF format, the column headers were separated into two pages with default PORTRAIT layout.

 

To keep all the column headers together in one page, either I need to change the font size and squeeze them to fit into the 8.5 inches width or change the report layout to be Landscape.  Since it does not look good to squeeze them, I want to change the report to be Landscape layout.

To do it, here are the steps:

1. Open the Report Properties
2. Expend the PageSize
3. Set the Width to 11in and Height to 8.5in and save it.

 

Now when exporting the report to PDF file, all the Column Headers are together in one page.

Cannot find the Performance Point Server “Decomposition Tree” option on the menu

Recently I am working on a PPS solution for the SharePoint 2010.  PPS 2010 introduces a new feature called Decomposition Tree that allows users to interactively drill down to the details. The Decomposition Tree option enables all of the following in one simple browser window:

• Show how an individual value in a report can be broken down into its contributing members.
• See all the members in a group in descending order
• Choose which dimension you want to use for the next level of detail.

After I deploy my new dashboard to the SharePoint site,I cannot find the Decomposition Tree menu by right clicking on the Grid or the Chart.

 

Doing several researches, I find that there is no Microsoft Silverlight installed on my client machine.

Microsoft Silverlight enables this option. If it is not installed, or disabled, the menu command will be hidden.  In addition, if you are using an analytic chart or grid, you must design it using the Design tab, not the Query tab in order to use the Decomposition tree feature.

Create SSRS Report with SharePoint 2010 Integrated Mode

With SharePoint 2010, lots people would like to use SSRS Integrated mode. The advantage is that you can deploy data sources, reports, etc. to the SharePoint document library instead of the Report Manager web application. In addition, you could also use the new version of Report Builder that came with SQL Server 2008 R2 and deploy the reports to SharePoint.  Derek Sanderson posted a blog entry: How to Switch from SSRS Native Mode to SharePoint Integrated Mode with detailed graphic information to show step by step how to do it. Once you completed all the installation steps, you may want to create the SSRS reports on it. The following steps show you how to do it.

Create SSRS Report Document Library

In order to deploy or create SSRS artifacts, you need to add a document library with the appropriate content types.

• Create a new document library called SSRS Report

• Go to the Library tab and click on the Library Settings menu to configure the content type

• In the General settings section, click on the Advanced settings link

• Change the Allow management of content types? to “Yes” and click the OK button to apply the change

 

• In the Content Types section, click on the Add from existing site content types link

• Select the Report Builder Model, Report Builder Report, and Report data source content types from the Available Site Content Types list to be added, then click the OK button to apply the change.

• If you want to change the default or the visible order of the content types, you could click on the Change new button order and default content type link and modify accordingly.

• Return back to the SSRS Report Document library, and click on the New Document from the Documents tab. Now you should be able to see the Report Builder menus that have been created.

• To launch the Report Builder, click the Report Builder Report menu.  You can now use it to create the SSRS reports and save them to the SharePoint document library.

Deploy SSRS Report from Visual Studio to SharePoint

• To deploy the SSRS report using Visual studio 2008, you will need to point to the SharePoint Server site, not the Report Manager site. Using the example we created, you will need to set the TargetServerURL to be the http://localhost/, and set the TargetReportFolder to be http://Localhost/SSRS Report

• Make sure you don’t append the “/” at end of the TargetReportFolder such as http://Localhost/SSRS Report/, otherwise you will get the following deployment error:

  Error rsItemNotFound : The item 'http://Localhost/SSRS Report//Demo SSRS.rdl' cannot be found.
  Deploy complete -- 1 errors, 1 warnings

Manage SSRS Data Sources, Parameters, and Permissions

• Go to the right side of the report and click the drop down to open the Context Menu. You could set the data source, configure the parameters, or manage the permissions for the report by clicking on the corresponding context menu.

View SSRS Report

• You could now view the report from SharePoint Site by clicking on the report itself.

• Or view the report from the Report Server Site by navigating to the report directory.