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.

PerformancePoint 2010: Decomposition Tree is not available

Are you unable to find the Decomposition Tree in the menu? Is it not visible?

Reason is simple, make sure SilverLight 3 (or 4) is installed. Once it is installed, it will be available for you. Thanks for Dinusha for reminding me on this.

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.

PerformancePoint 2010 Error: The URL is not available, does not reference a SharePoint site, or you do not have permission to connect.

If you face for this error when connecting to SharePoint site through PerformancePoint designer;

… then check the domain account which has been used for PerformancePoint service application has been added to site content database as an owner. Once added, you are not going to see this error .

PerformancePoint 2010 Error: The URL must be in one of the following zones: Local, Intranet, or Trusted

If you face for this error when connecting to SharePoint server from Dashboard Designer;

… simply add the site for Trusted Sites in your Internet Explorer.

MCTS: SQL Server Database Administrator – Maintaining a MS SQL Server Database 2008 – 6231A Course

Course 6231A: Maintaining a SQL Server 2008 Database – This is my first class for 2011. Class will be started on Sunday, 23rd January 2011. If you are a DBA or interest on SQL Server Administration, join with us. Some topics that are covered with this course are Installing and Configuring, Security, Back up strategies, Disaster Recovery, High Availability, Replication and data transferring (see more on http://www.microsoft.com/learning/en/us/course.aspx?id=6231A).

Good news! Microsoft gives 40% discount for students for Microsoft exams. All you have to do is, go to an authorized Centre and prove that you are a student of any university (both private and government), then you get 40% discount for any exam. Good Luck!

Cannot login to SharePoint 2010 site when the Host Header is set

Everybody prefers a good URL for sites rather than server name with ports. Same for SharePoint Web Applications (SharePoint sites). When a Web Application is created, it allows us to set the URL for the site as we want through Host Header as well as the port. For example, when a Web Application is created as http://SPServerName:3000 , URL can be set as http://MyTestWeb.com with Host Header input box. Once created you need to make sure that a DNS record is created and pointed to SharePoint server.

My environment is set up with two Windows 2008 R2 Enterprise Editions. Once server is configured as the Domain Controller and other has SharePoint 2010 and SQL Server 2008 R2.

Here is the way I set;

Add URL I need for the Web Application:

Set DNS record in DNS Server, in my case it is DC:

Done. I should be able to navigate my site (after creating a Site Collection for the Web Application) using the URL set with Host Header (in my case, it is http://bi.andromeda.com) without using http://W2008R2-SP:30000 . But I am unable….

My Problem
When I open the browser for http://bi.andromeda.com , browser prompts me the authentication dialog box and asks for an user id and a password. It prompts three times even with correct user id and password and then opens a blank page. Could not figure out the reason for a while, finally Prabath sent me an URL that contains a solution for this. The solution is given by Tech Battle Field and post is http://spiderwool.blogspot.com/2010/09/access-denied-host-header-sharepoint.html.

In summary, solution is adding a new Multi-String registry key named BackConnectionHostNames in SharePoint server at HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0 and set the host names with values (Read the post for more details). This is what I did:

Finally it worked. Thanks for Tech Battle Field for sharing this and thanks for Prabath for guiding me onto it.

Awarded Microsoft MVP again for 6th time

MVP again!

Microsoft has awarded the title again for the 6th time, thanks for Microsoft, Microsoft Sri Lanka, Wellignton and Lilian for recommending me.

Two new MVPs are introduced; Prabath Fonseka (SharePoint) and Preethiviraj (SQL), Congratulation guys!