Reporting Services (SSRS) reports with PerformancePoint

Thanks to Dan for his port on PerformancePoint Services.

Using Excel Services Reports with PerformancePoint Server (PPS). This has been a very popular posting and I thought I would add another one in regards to using Reporting Services (SSRS) reports with PerformancePoint (in PPS 2007 this type of report was called SQL Server Report).
Some of the reason that you might want to include a SSRS report in your PPS dashboard would be because:

• leverage an existing report created by an end-user
• incorporate existing operational reports
• use additional charting options – map, area, range, scatter, polar, bar (not column), funnel, 3D, sparklines, data bars
• need more flexibility and control over reports, styles, colors, scales, etc.
• join multiple data sources into a single report
• combine relational and OLAP data into a single report

The example that I will be showing is using SSRS in SharePoint Integrated Mode, but you can also do this in Native Mode as well, you would just see a different setup screen when you are configuring the report in Dashboard Designer (a tad bit easier in my opinion configuring these in Native Mode – which is labeled as ‘Report Center’ mode in Dashboard Designer, confusing I know…). I will also be using Report Builder 3.0 to create and deploy the report to the SharePoint site.

Go to Report Library in SharePoint site, select Documents from Ribbon, select New Document, and pick Report Builder Report	This will either launch Report Builder or ask you if you want to run and install the application if you haven’t done so yet
Report Builder is a ClickOnce application and by clicking Run you will install the application	Once installed the Report Builder application will start up
In this example we will build a Map	Reposition the map up a bit so it appears above the legends
A Bubble Map will be used to be able to analyze two metrics	A new data set will need to be added that contains the spatial data
A new data source will be added connecting to the Contoso Retail DW SSAS database	Use the Sale cube, filter for the United States, setup the Fiscal YQM as a Parameter, pick State Province Name, Sales Amount, and Sales Total Cost
Use STATENAME and map this to the State Province Name field from the data set	Pick a theme for the style, setup the bubble size to visualize Sales Amount, and polygon color for the Sales Total Cost
Setup Chart and Legend titles, polygon tooltip, remove color legend, resize/reposition map, and remove default marker size	Save report to SharePoint library
Now we are going to add a new Report to our existing PerformancePoint Content library	This will launch Dashboard Designer and like the Report Builder you may be prompted to install it (this is also a ClickOnce application)
If nothing launches then you need to make a small adjustment in your IE security settings to Enable ‘Automatic prompting for file downloads’	Now we will create the new PerformancePoint Report
Use the SharePoint Integrated mode, specify the URLs for the Report Server and the RDL file, uncheck the Show toolbar, and specify a name for the PPS report	Next we will create a filter that we can use with the report once it is displayed in the dashboard page
The filter we will create will be for the Fiscal YQM and we will remove periods that don’t have any Sales Amount	We will use a Tree style display and only allow a single selection
Name the filter and get ready to create the dashboard	Add a new Dashboard item
Name the dashboard item, page, add the filter, add the report, and remove the extra column (zone) on the page	Create a Connection (formerly link in PPS 2007) between the filter and the report
The filter will connect to the DateFiscalYQM parameter on the report and will pass the Member Unique Name (an SSAS member value to the report)	Save the PPS content items and deploy the dashboard to the Dashboards library
Select the Master Page and whether or not you want to include the page navigation or not	Test out the filter and view the results with the deployed PPS dashboard

My example here used the Contoso Retail DW sample data which is available from the Microsoft downloads here – Microsoft Contoso BI Demo Dataset for Retail Industry. This is also using Reporting Services 2008 R2 which includes the new Map report item, Report Builder 3.0, PerformancePoint Services, and SharePoint 2010 Enterprise.
I have two other postings that I did earlier in the year in regards to the new Map report item here that you can check out if you have questions in regards to that:

• Mapping data with SSRS 2008 R2
• Mapping data with Report Builder 3.0 (SSRS 2008 R2) Case Sensitive

Download:

Feel free to download the PPS Workspace file (ddwx) and the SSRS report (RDL) file from my SkyDrive which I have included in a zip file.

You might find this posting useful if you want to reuse the workspace file – Migrating PerformancePoint 2010 Content to New Server.

Enjoy and Happy SharePointing.

PerformancePoint Services 2010 (PPS) Hotfixes

Yesterday I decided to take a quick glance at the TechNet Support site to see if there had been any new hotfixes released for the latest version of PerformancePoint. I know that there was a fix added in SQL Server 2008 R2 CU5, PerformancePoint Services 2010 Analytical Grid Filter Fix, Sort of, but I was wondering if there was anything else. Well it turns out there has been. So far I was able to track down 3 hotfixes in addition to the SQL CU and then there has also been a SharePoint CU as well that includes these as well.

Title	KB Article	Information
SharePoint Server 2010 Cumulative Update Server Hotfix Package (MOSS server-package): March 3, 2011	KB 2475878	Cumulative update of hotfixes for SharePoint Server 2010
PerformancePoint Server 2010 hotfix package (ppsmawfe-x-none.msp, ppsmamui-xx-xx.msp): February 22, 2011	KB 2496951	Query String (URL) filter web part fix with scorecards and decomposition tree
FIX: An analytic grid that is connected to SSAS 2008 R2 returns incorrect data when you apply a filter to the analytic grid in PerformancePoint Dashboard Designer	KB 2463203	The fix for this is actually included in CU 5 for SQL Server 2008 R2. Refer to my blog posting above that discusses the issue and shows what has been done. There is a forum thread in regards to this as well – filter value function not working on analytic grid
PerformancePoint Server 2010 hotfix package (ppsmamui-xx-xx.msp, ppsmawfe-x-none.msp): December 14, 2010	KB 2466270	Scorecard fix for dimension names that include a % symbol.
PerformancePoint Server 2010 hotfix package (Ppsmawfe.msp): October 26, 2010	KB 2422440	Fix for Decomposition Tree displaying captions in multiple languages for the MUI

So far this is all I have tracked down. Hopefully now that this is fully baked into the SharePoint product now, for the most part, that the hotfixes and updates will be released a bit more frequently and be easier to track down. Maybe the PPS team will even blog about them as well? After all they took a six month break from blogging, so hopefully they have some good stuff built up to share now.

PerformancePoint 2010 Cascading & Apply Filters – SP1 Features

This is not a new concept, but for PerformancePoint it is. In Reporting Services you have always had the ability to setup parameters so that the selection in one parameter list would be able to filter the available values in another parameter list. Well now this has been added to PerformancePoint and it is available with the Multidimensional Filter types – Member Selection, MDX Query, and Named Set filter types. When you go to create a new filter of one of these types you will see a new setting in them. This new option is to select a measure (metric) that will be used to pass a query to the other filter to return the list of available values that satisfy that query.

Member Selection

The new selection is the ‘Filter measure:’ option and the informational dialog box states the following:

Select the measure used to determine which values to display when this filter is driven by another filter.

This is the measure (metric) that will be used in combination with the filter member values passed to this filter to display the available list of values to the end-user to select from. So if I had a filter that was for Product Category and passed that to another filter that was Product Subcategory and the Product Subcategory was configured with ‘Sales Amount’ measure then the Product Subcategory filter would display a list of items that had ‘Sales Amount’ for the Product Category items that were selected. A tad bit confusing perhaps, but this is how it works.

MDX Query

Named Set

This option is not available with the other filter types, just the ones displayed above – Member Selection, MDX Query, and the Named Set.
Ok, so now that you have a tour of that new option lets setup a dashboard with a couple of filters and a report.

Create Filters

Member Selection – Product Category

Member Selection – Product Subcategory

Create Analytical Grid Report

In this report I used the Product hierarchy and chose only the Product Name descendants of All, picked the Calendar Year hierarchy, and placed the Sales Amount measure in the background. I also used the filter option to remove blank rows and columns.

Create Dashboard and Connect the Items

For the connections I connected the two filters together and then connected the Product Subcategory to the Product Sales report.

Connection to the Product Subcategory filter

Connection to the Product Sales Analytical Grid Report – uses a connection formula as well

In this example I am leveraging a Connection Formula. The reason I am doing this is because the hierarchies that are involved in this example. I am not referencing the same hierarchy in each item and I want to be able to display the product names in the report instead of the subcategory values. So I am taking the display name in the subcategory filter and using that in a formula to return the children (product names) in the report.

Deployed Dashboard in SharePoint

You can see that the subcategory filter is filtered by the category filter and only the ‘Tv and Video’ subcategory members are being listed. The subcategory filter selection is also filtering the report which is displaying all of the product names that are associated with the ‘Television’ subcategory.
If we make another selection in the category list we will see everything get updated again.

Pretty slick.
Ok, now on to the other new feature that was added into the service pack 1 – Apply Filters Button.

Apply Filters Button

When you setup a dashboard now you will see a new selection in the Details pane in the Filters section called ‘Apply Filters Button’.

So what is this for? Hmmm, is this something similar to Reporting Services perhaps? Answer – Yes, with an added bonus.

If you drag and drop this onto the dashboard page and go into the edit settings for this new item you will get some options you can configure. The first one is the text that you would like to be displayed on the dashboard page for the button. And the next one is whether or not you would like to provide a checkbox for the end users to be able to save their selections for this dashboard page – this will be stored as their default values for these filters. This means that when they come back to this page at a later time these filter selections will automatically be selected for them. In the past the last selection of items from the filters was always saved and stored for the users, but now they have the control to determine which values get saved (if you want them to – optional).

The other thing about this new feature is that when you make selections from the filters the items in the dashboard (with the exception of linked filters) will not be filtered. In order to get the other items to filter on the dashboard you need to click the button. Once you do this your scorecards and reports will refresh and display the data based on the selections in the filters (assuming they are connected of course).
I was hoping that this feature might somehow allow you to retain your default member selection settings in the initial filter setup, but that does not appear to be the case. The application still retains the last selection by the user unless you provide them the ability to save their own defaults with the new ‘Apply Filters Button’ option.
Anyway, these are just a couple of the new features along with hotfixes that are available in service pack 1.
Check out more information here:

• Description of SharePoint Server 2010 SP1
• What’s New in PerformancePoint Services and SP1?

Enjoy!
By the way, after I upgraded to SP1 the build version of Dashboard Designer was the following:

14.0.6016.1000

Prior to the upgrade it was:

14.0.4750.1000

I had installed a hotfix prior to doing the service pack 1 install. I wanted to check out some other fixes before this release – PerformancePoint Services 2010 (PPS) Hotfixes.

Service Pack 1 for SharePoint 2010 Products and its known issues

Service Pack 1 for SharePoint 2010 Products is Now Available for Download
Service Pack 1 includes stability, performance, and security enhancements that are a direct result of your feedback. 

IMPORTANT NOTE

It is strongly recommended to install the June 2011 Cumulative Update immediately after the installation of Service Pack 1. The June Cumulative Update includes several important security and bug fixes that are not included Service Pack 1. 

Installing Service Pack 1

Prior to installing Service Pack 1 you should carefully read the known issues and release notes at

http://support.microsoft.com/kb/2532126

Service Pack 1 includes all fixes released through April 2011 so it can be installed directly to RTM builds of SharePoint 2010 Products, or any prior Cumulative Update.
Install the service packs in the following order on every server in the farm.

1. Service Pack 1 for SharePoint Foundation 2010
2. Service Pack 1 for SharePoint Foundation 2010 Language Pack (if applicable)
3. Service Pack 1 for SharePoint Server 2010
4. Service Pack 1 for SharePoint Server 2010 Language Pack (if applicable)

The SharePoint 2010 Products Configuration Wizard or "psconfig –cmd upgrade –inplace b2b -wait” should be run once on every server in the farm following the final update installed.
The version of content databases will be 14.0.6029.1000 after successfully installation. For more in-depth guidance for the update process, we recommend reviewing the following articles. These articles provide a correct way to deploy updates and identify known issues (and resolutions).
Prepare to deploy a software update for SharePoint Foundation 2010
Install a software update for SharePoint Foundation 2010
Prepare to deploy a software update for SharePoint Server 2010
Install a software update for SharePoint Server 2010

Frequently Asked Questions

Q: Can I install Service Pack 1 on RTM builds of SharePoint 2010 Products?
A: Yes, Service Pack 1 can be installed directly on RTM builds; however, we suggest you install Service Pack 1 then apply the June 2011 Cumulative Update.
Q: Do I need to run psconfig after the install of every package?
A: No, apply all of the available packages then run psconfig - the database will only be updated once, to the newest version.
Q: Do I need to run psconfig on every machine in the farm?
A: Yes. Although database is already updated, the binaries on each server need to be set and permissioned using psconfig.
Q: Will there be a slipstream build including Service Pack 1 available for download?
A: At this time a slipstream build including Service Pack 1 is not available. 

Additional Resources

To learn about what’s new in Service Pack 1 read the Service Pack 1 for SharePoint Foundation 2010 and SharePoint Server 2010 whitepaper.
Learn more about installing updates for SharePoint 2010 at the Updates for SharePoint 2010 Products Resource Center
For a description of new functionality in Service Pack 1 see the Service Pack for SharePoint 2010 Coming Soon... blog post on the SharePoint Team Blog.