DotNetCurry Logo

Cascading Filters in Performance Point Services dashboard using SharePoint 2013

Posted by: Pravinkumar Dabade , on 3/22/2013, in Category SharePoint
Views: 29698
Abstract: In this article we will see how to create and apply cascading filters in Performance Point Services dashboard using SharePoint 2013.

In my previous article, I introduced some SharePoint Server 2013 and PerformancePoint Services Enhancements. We will continue our series and explore how to create and apply cascading filters in Performance Point Services dashboard. Please note that this feature is available with SharePoint Server 2010 SP1 and SharePoint Server 2013. We will implement this feature using SharePoint Server 2013.

 

For this demonstration, I am using –

  • Windows Server 2012.
  • Microsoft SharePoint Server 2013.
  • Microsoft SQL Server Analysis Services 2012.

First of all, we will create a SharePoint site using a SharePoint template called “Business Intelligence Center” as shown below –

Specify the user name and create the site.

Now it’s time to design the dashboard. For designing the dashboard, we need a dashboard designer. Earlier in SharePoint Server 2010, the dashboard designer was launched on a button click. But now the dashboard designer can be launched through the ribbon. To launch the dashboard designer, click on “Settings” and “View Site Contents”. Once you are on the “Site Contents” page, click on Dashboards library. Go to “PERFORMANCEPOINT” ribbon and click on “Dashboard Designer” as shown below –

dashboarddesigner

This will launch the Dashboard Designer tool -

dashtoolcontents

For this demonstration, I am using Analysis Services database which is “PDW” – “ProClarity Data Warehouse”. The first step is to create a “Data  Connection”. Right click “Data Connections” and click on “New Data Source” context menu. It will show you different types of data sources. Choose “Multidimensional” and “Analysis Services”. Click the “OK” button.

Now specify the connection string. Also specify appropriate Server Name, Database Name and Cube name as shown below –

Rename the data source to “PDW” and save the data source. Now let’s add a new “Analytic Chart” report. Right click “PerformancePoint Contents”, New and Report. This will show you the different report templates as shown below –

analyticreport

As soon as you create an “Analytic Chart” report, it asks you which data source you would like to use. Choose the PDW data source and click the “Finish” button. Rename the report as “PDW Chart Report”.

Now from the “Details” pane which is on our right hand side, drag and drop “Last 12 Cal Months w/Sales” into our “Bottom Axis” section and drag and drop “Family”, “Country”, “Region” and “City” from Geography and Product dimension into a Series section as shown below –

designchartreport

Now your chart report is ready.

Let’s add one more new report, which is “Analytic Grid” report with the name “PDW Grid Report”. Drag and drop “Quarter” from time dimension into Columns and drag and drop “Geography Country”, “Geography Region”, “Geography City” from Geography and “Product Family” Product dimensions respectively into Rows section as shown below –

 

designgridreport

Now your grid report is ready.

 

Adding Parameters to Reports

Let’s add Parameters to our both the reports. To add Parameters, first open your “PDW Chart Report” in design mode. Click on the Query tab as shown below –

querytab

This will show you a MDX query. From the MDX query, select [Geography]. [Country]. [Country].ALLMEMBERS and in the Parameters section at the bottom, give a parameter name as “ChartCountry” and click on Insert button. Repeat this procedure for Region and City with the parameter name “ChartRegion” and “ChartCity”.

Now open the “PDW Grid Report” and click on the “Query” tab. Let’s add the parameters “GridCountry”, “GridRegion” and “GridCity” for Country, Region and City respectively.

Now let’s add Filters. To add the filters, right click on “PerformancePoint Content” and click on add New Filter as shown below –

addfilter

Make a choice of “Member Selection” as shown above and click on the “OK” button. Choose the “PDW” data source and click on the “Next” button. In a Select Member step, choose filter dimension “Geography. Country”. In Filter Members, choose few countries as per your choice. In Select Display Method step, choose “List”. Rename the filter with the name “Country”. Repeat this procedure to add “Region” and “City” filter. Make a choice of appropriate Regions as well as Cities as per your country choices.

Now it’s time to create a Dashboard and deploy the same to our SharePoint site. To create a Dashboard, right click “PerformancePoint Contents” and click on New > “Dashboard”. Make a choice of “Header and 2 Columns” dashboard page template. Rename your dashboard to “PDW Dashboard”. Also rename Page to “PDW Chart and Grid Report”.

Split the Header zone, into 3-sections. Now your dashboard should look like the following –

 

dashboarddesign

From the “Details” pane, expand the Filters section. Drag and drop the filters “Country”, “Region” and “City” in Header, Zone 1 and Zone 2 sections respectively. Now expand “Reports” section from the “Details” pane and then PerformancePoint Contents. Drag and drop “PDW Chart Report” into our bottom left column and “PDW Grid Report” into bottom right column.

Now to apply the cascading behavior, expand the Country filter and then drag and drop “Member Unique Name” on Region filter as shown below –

applycascade

Repeat the same for Region. Expand the Region filter from the Zone 1 and drag and drop “Member Unique Name” on City filter which is located into Zone 2. This procedure will provide a cascading behavior to our filters.

Now let’s join the Filters to our reports as well. Let’s drag and drop “Member Unique Name” from Country filter on our report which will create a connection to map the filter value to our “ChartCountry” parameter. Repeat this procedure for Region filter as well as City filter.

Repeat the above procedure to map our filters to the PDW Grid report as well.

Save the dashboard and deploy it to our SharePoint site. To deploy the dashboard to SharePoint site, right click “PDW Dashboard” and click on “Deploy to SharePoint”. It asks you for the master page settings. Use the “Default Site Master Page” and click “OK” button.

Your final report will look like below –

 

finaldashboard

Conclusion

In this article we have see how to create and apply cascading filters in Performance Point Services dashboard.

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Pravinkumar, works as a freelance trainer and consultant on Microsoft Technologies. He is having over 10 years of experience in IT and is also a Microsoft Certified Trainer(MCT). He has conducted various corporate trainings on all versions of .NET Technologies including .NET, SharePoint Server, Microsoft SQL Server, Silverlight, ASP.NET, Microsoft PerformancePoint Server 2007 (Monitoring). He is passionate about learning new technologies from Microsoft. You can contact Pravinkumar at dabade[dot]pravinkumar [attherate] gmail[dot]com


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Dhrub on Wednesday, June 5, 2013 2:15 AM
Consider below scenario:
The column having following content:
a
b
a,b

When I click on filter it gives me option like
a
b

a,b

But I require unique value in filter that is only a and b.

Kindly suggest how do we do it
Comment posted by Gautam Kumar on Monday, August 4, 2014 1:34 AM
Very good post, very helpful. But i could not get only the Cascading filters working.
Comment posted by Benjamin Rusell on Thursday, August 7, 2014 9:51 AM
I've had terrific results and experience with a third party SharePoint analytics solution - CardioLog Analytics. They offer both a free edition for SMB and also a commercial edition in various packages for enterprise organizations.  http://www.intlock.com/intlocksite/productsandservices/cardiolog/cardiolog-analytics.asp