PPS Reports: Integrating Reporting Services Reports with PerformancePoint 2010 Dashboards

The success of Business Intelligence solution can be measured with Visualization of data, hence Visualization in proper manner is important. It is handled through many ways, providing easier access for information to business users. One of the key components in Microsoft Business Intelligence suite is PerformancePoint 2010 which supports visualizing data in number of ways. PPS allows business user to interact with data, organize as he wants and change the content as he wants with its reporting features. Some of reporting facilities available with PPS are Reporting Services reports, Analytic charts, grids, and Decomposition trees. This post focuses on Reporting Services reports, basically, how a Reporting Services report can be added to PPS dashboard.

Microsoft Report Viewer 2008
In order to use Reporting Services report with PPS Dashboard Designer, you need to make sure that Microsoft Report Viewer is installed in your machine. If not, you may face errors like “Unable to connect with Report Server” or “Could not load file or assembly ‘Microsoft.ReportViewer.WinForms’…..”. It is available for 2005, 2008 and 2010 now. It looks like PPS Dashboard Designer works only with 2008 (I have 2008 SP1) but you can try 2010 too. Here are links for downloading it:
Microsoft Report Viewer Distributable 2008
Microsoft Report Viewer Distributable 2008 SP1
Microsoft Report Viewer Distributable 2010

Getting Started
As usual, you have to start with PPS Dashboard Designer. If you have configured Business Intelligence Web Application and site properly with SharePoint 2010, there will a link available for downloading this ClickOnce application (see Configuring PerformancePoint 2010 for more details). Click on Run Dashboard Designer for downloading the designer.

Once open, Save it with a meaningful name. Then select PerformancePoint Content folder in left-pane and go to Create tab in the ribbon. Click on Reporting Services.

Report Setting
You need to set few settings in order to have a Reporting Services report in your dashboard.

Server Mode: This allows you to set the mode of Reporting Server you are going to connect with. If it is hosted in SharePoint, select SharePoint Integrated, else select Report Centre. In my case, it is SharePoint Integrated mode.

Report Server URL: This is the URL of Report Server Web Service which you set of Reporting Services Configuration Manager.

Report URL: This is the exact URL of the report. If it is hosted in SharePoint, make sure that you have given the proper URL (including the extension). If you select Report Centre, you will be able to browse the report and set.

Show toolbar, Show Parameters, Show DocMap: You can set the visibility of these items. Note that Document map will not be available for all reports. It has to be created with either Report Builder or BIDS.

Zoom: Allows you to set the viewing size.

Format: Default is HTML 4.0. You can select other types such as Excel or PDF. Based on the type you select, report will be either shown in the web page or will be downloading to client machine.

Section: This allows you to set the page to be displayed. If you set 3 for this, 3rd page will be open as the default page.

DocMap ID: It is supposed to show the element set with this as the default page. I could not figure it out the way of setting it, for me, it did not work.

Report Parameters: This allows to set parameters default values.

Once settings are done, add a dashboard and place the report, and publish to SharePoint site. SharePoint site can be set with File Button –> Designer Options –> Server –> SharePoint URL. Make sure it is set before publishing.

Here is the published dashboard.

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part III

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated mode is straight forward and simple. If you have read my post Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part I and Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part , then you know the way of configuring it. You will not face much issues after configuring if it is done with Standalone SharePoint 2010 architecture. If it is a SharePoint farm which components are distributed, few things should be considered. Here are some of points you need to consider when Reporting Services is configured in SharePoint 2010 Farm. Note that this was tested with a Web Application which Authentication is set as Classic and NTLM is set for Authentication Provider.

Domain accounts created
Reporting Services Account: Domain account, no admin privileges. Usually name it as a Domain\ReportingServices. Set this with Reporting Service Configuration Manager.

Application Pool Account: Domain account, no admin privileges. Usually name as Domain\SP_SSRSApp. Use this when the Web Application is created with SharePoint.

Site Collection (or Site) account: Domain account, need local admin rights. Usually name as Domain\SP_Admin.

** All accounts are maintained as Managed Accounts in SharePoint.

Reporting Services Windows Service Account
This should be a domain account. Once it is set with Reporting Services Configuration Manager, you need add the same account to SharePoint too. First, have it as a Managed Account (Central Administration –> Security –> General Security –> Configure Managed Accounts). Then add the account to the Site where you have configured Reporting Services Libraries. You can add this account to Home Members group. Errors you may get if it is not added are “The permissions granted to user ‘Domain\User’ (what ever account you used for logged in) are insufficient for performing this operation.” and “rsAccessDenied”. Note that error has no link with the service account but it is related to it. You may get this error with Report Builder or BIDS when deploying reports, or viewing reports.

In addition to that, you need to make sure that the account is added to Web Application content database which is created for Web Application. If not, you may get and error “Report Server has encountered a SharePoint error”.

Other than that, this account is in ReportServer database too. It is automatically added, but make sure that it is there.

Allow Anonymous Setting
Make sure that Allow Anonymous is set as false when the Web Application is created. I faced an issue related deployment from BIDS if this is not set to false (read my post on it). Once the application is created, double-check with IIS (Site –> Authentication) and see whether it is disabled. As per the Authentication and Authentication Provider we set, Only ASP.NET Authentication and Windows Authentication should be enabled.

SharePoint Integrated Reporting Services: BIDS pops up Login Dialog box indefinitely

Annoying…. This is all about SharePoint integrated Reporting Services deployment via BIDS. Web application for Reporting Services has been created with SharePoint 2010 and all necessary libraries are added (if you need to see the way of configuring SSRS 2008 R2 in SharePoint 2010, please see my posts Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part I and Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part II).

Now the problem is, popping up login dialog box indefinitely in BIDS when try to deploy reports, regardless of what account is supplied. As usual, went through the internet, found many solution but none of them worked for me. One of picked solutions by me is, disabling anonymous user. But it did not work too.

Solution
After all unsuccessful attempts, I created a new web application and set the Anonymous Access to No when creating the Web Application. Then I pointed the project’s target URL to newly created site, Wow, no login dialog box. I tried the same with old site too (by disabling Anonymous Authentication in IIS) but did not work. So, not sure about this solution but it worked with the new site. If you face the same, try a new site and see.

PivotViewer Extension for SQL Server Reporting Services

You might have already seen Microsoft Silverlight PivotViewer for Excel (see my post on it). Just like that you are going to see PivotViewer for SSRS. Good news? Yes, but there is a bad news too, it is not yet available for downloading, it a kind of prototype, here how it goes;

Anyway, have a look on this video, this shows it.

Article: Reporting Services 2008 R2: Geospatial Visualization – Part II

If you have read the article Article: Reporting Services 2008 R2: Geospatial Visualization – Part I, then here is the part II of it. It has been published at www.sql-server-performance.com.

Here is the direct link: http://www.sql-server-performance.com/articles/biz/geospatial_visualization_ii_p1.aspx

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part II

If you need to create an extremely user-friendly environment for Reporting Services 2008 R2, SharePoint 2010 Integrated Mode is the best solution. Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part 1 explains the initial configuration up to SharePoint integration. This post discusses creating a web site for Reporting Services with relevant libraries and content types, finally publishing reporting from BIDS.

Let’s start creating a Web Application. Go to SharePoint Central Administration and click Manage Web Applications under Application Management. Once Application Management is open, click on New to create a new web application.

Some of the attributed in New Web Application Form as as follows;

• Authentication
  Select Classic Mode Authentication
  
• IIS Web Site settings
  Select Create a new IIS web site option. I have named it as Report Server – 10005 and set the port as 10005.
  
• Security Configuration
  Leave default values
  
• Public URL
  In my case, URL is http://DP03:10005
  
• Application Pool
  Make sure Create new application pool is selected. I have named it as ReportServer – 10005.
  
• Database Name and Authentication
  Set the SQL Server name for Database Server. I have named the database as WSS_Content_ReportServer_10005.

Click OK to save setting. Once the process is completed, Application Created windows is appeared. Click OK to continue. New web application will be listed in Web Application window.
 
Next step is creating a site collection. Go back to Central Administration and click create site collections under Application Management. This opens Create Site Collection window. Let’s ceate one;

• Make sure that correct web application is selected (In my case, it is http://DP03:10005/.
• Add Title as Report Server.
• Let’s use the root for the Web Site Address. Do not select sites from the drop-down. Leave it blank.
• Select the Template as Team Site.
• Set both Primary Site Collection Administrator and Secondary Site Collection Administrator. Since I have everything in one machine, I set both as DP03\Administrator;.
• Click OK to create the site.
  

Once done, a message will be appeared saying Top-Level Site Successfully Created. Click on the link to go the site (In my case, link is http://dp03:10005). This is what I see;

Site needs libraries for holding reports and connections. Let’s create two libraries for the site.

• Click on Libraries link in left pane. It lists all document libraries available.
• Click the button Create for creating a new library.
• Select Document Library and Set the name as Reports.
  
• Click Create for creating the library. Once the library is open, click on Library Settings for adding content types.
   
• When Library Settings is open, library related things such as General Settings, Columns, and Views are shown. By default, it does not show Content Type. In order to enable content type, click Advanced Settings under General Settings. Once the window is open, select Yes for Allow management of content types.
  
• Click OK to save settings. Note that now the Content Type is shown under Library Settings. Click Add from existing site content types link under Content Type. This opens Add Content Types page.
• Select Report Server Content Type from Content Types From drop-down. Then move Report Builder Report from left listbox to right listbox.
  Note that if you are not seeing “Report Server Content Types”, that indicates that Add-In has not been properly installed”.
  
• Click OK to save settings. Report Builder Report content type is now appeared under Content Types of Reports library.
  
• We have to create another library for data connection. Follow same steps for creating the library. Name it as Data Connections. Then enable Allow management of content types as did for Reports library. Then add Report Data Source content type for it.
  

Now SharePoint 2010 is ready for holding reports from Reporting Services 2008 R2. Let’s try to publish some reports to this. Open Business Intelligence Management Studio and create a Reporting Services project. If you have AdventureWorks 2008 Sample Report Project, you can use it for testing. Once the project is open, get the properties of the project. Set following properties;

• TargetDataSourceFolder: http://dp03:10005/Data Connection
• TargetReportFolder: http://dp03:10005/Reports
• TargetServerURL: http://dp03:10005/

Now deploy reports and see. Reports will be published to SharePoint site we created. Once published, open the home page of the site (http://dp03:10005) and click on Reports link in left pane. It lists all the reports published.

Click one of reports and see whether it works fine. Here is the output of Company Sales 2008 report.

All done. Reporting Services 2008 R2 is integrated with SharePoint 2010. I will be exploring more on this, specially SharePoint related things. Once they are explored, I will publish them.

Update: Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part III

SSRS 2008 R2: Sri Lanka Map Report

Just created a Polygon-Layer Sri Lanka map using SQL Server 2008 R2 for showing population by provinces. It is fun to work with R2, I will share the way of creating this with an article soon.

Reporting Services Error on Doughnut Chart when Tooltip is set: Fixed in 2008 R2?

If you have read my previous post “Reporting Services Error on Doughnut Chart when Tooltip is set: Parameter is not valid”, you know about this error. I just checked this with 2008 R2 and, wow, it has been fixed it seems. Great.

Developers Training Kit for SQL Server 2008 R2

Microsoft has published "SQL Server 2008 R2 - Training Kit for Developers" that contains presentations, demos, videos and hands-on lab related to new features of SQL Server 2008 R2. It is free, all can download. Here is the link: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d This requires new AdventureWorks database, it is available here: http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=24854

Hands-On Labs: Authoring Reports with Reporting Services 2008 R2

I will be doing a Hands-On labs session tomorrow (10th Wednesday) at TechEd Sri Lanka 2010, on Authoring Reports with Reporting Services 2008 R2. This session includes some of the new features of R2 like Maps, Sparklines, DataBars, Shared DataSets, Shared Report Items, and more if time is permitted. If you interest, make sure you are at the lab after lunch, it has been scheduled from 1.15pm to 3.15pm.