SharePoint Dashboard with Common Filters using PerformancePoint Services 2010

Posted by: Pravinkumar Dabade , on 2/25/2011, in Category SharePoint
Views: 87091
Abstract: In this article we will create a dashboard with common filters using Sharepoint PerformacePoint services 2010 and SQL Server Analysis Services (SSAS) Data Source. This Dashboard will contain a Chart Report and Grid Report and will contain common filter for both the Reports. Finally, both the reports will render with our filter choice(s).

In this article we will create a dashboard with common filters using Sharepoint PerformacePoint services 2010 and SQL Server Analysis Services (SSAS) Data Source. This Dashboard will contain a Chart Report and Grid Report and will contain common filter for both the Reports. Finally, both the reports will render with our filter choice(s).

 

So, let’s get started by creating a SharePoint Site using a Template Business Intelligence Center. Click on  the Start button and go to SharePoint 2010 Central Administrator tool as shown here:

This action will open a web site. From this website click on “Create site Collections” from “Application Management” group as shown below:

This will open a Create site Page. From this page, choose a web application under which you want to create a Site collection. Once you choose a Web Application, give the title to your site collection and choose a URL. Optionally you can write a site description as shown below :

Now select a site template from Enterprise Group:

And at the end, provide your site collection administrator primary account with “Domain\Administrator”. Check the domain account and click OK.

This will create a site collection.

Now click the link and you will see your Business Intelligence center site as shown below:

Once your site is ready, browse the site and mouse hover on the Create Dashboard in the right hand section. You will see the following links

Click on the link “Start using PerformancePoint Services”. This link will take you to a page from where you can download a Dashboard Designer Tool by clicking a button “Run Dashboard Designer”.

This will download a Dashboard Designer tool at your client side. This is a Click Once Application. This tool now will help you to design the Data Sources, KPIs, Scorecards, Reports, Filters and Dashboards. The tool is shown below:

In the left hand section, you will see a Workspace Browser. In this window, you will see two settings –

1) Data Connections

a. This is used for creating a connection with different types of data sources as shown below

b. For our demonstration, we are going to use a multidimensional Data Source that is Analysis Services.

2) PerformancePoint Content

a. Using PerformancePoint Content we can design KPIs, Filters, Reports, Dashboards, Indicators and Scorecards as shown below :

For our demonstration, we are going to design two Reports (Chart Report and Grid Report), Filters and a Dashboard.

For this demonstration, I am using a Pro-clarity Data warehouse database which is a demo database provided by Microsoft. This database was given by Microsoft when Microsoft PerformancePoint Server 2007 was released in year 2007.

Download the Database over here

Restore the backup of the database with your Analysis Services. Now let’s create a data connection for our PDW database in our Dashboard Designer tool.

Right click Data Connection > Click New Data Source as shown here:

From Select a Data Source Template window, make a choice of Analysis Services and configure your PDW database as shown here:

Once you configure the connection to your Analysis Services and Database PDW, choose a cube “Sales” and then click “Test Data Source” button. You should get a connection Successful window.

Rename this data source to “PDW”.

Create a Chart Report from the PDW Data source

Next step is to create our Chart report from the PDW data source. For creating this report follow these steps:

Step 1: Right click PerformancePoint Content > New > Report and make a choice of “Analytic Chart” from “Select a Report Template” window as shown here:

Step 2: Select a Data Source PDW which we have created a short while ago. Rename your report to “PDW Chart Report”. The report view will look similar to the following:

Step 3: Now from the “Details” Window on your right hand side, drag and drop “Last 12 Cal Months Sales w/Sales” from “Named Sets” to the “Bottom Axis” section of our Chart Report. Now drag and drop Product “Family” from Product Dimension to “Series” of our Chart Report as shown below:

Step 4: Now your basic chart report is ready. Let’s make a choice of specific products by clicking the dropdown arrow and select products – “Mountain Bikes”, “Road Bikes” and “Touring Bikes”.

Step 5: Now let’s add Parameters to our chart report. To add the parameters to our chart report click on “Query” tab of your report as shown below :

This will show you a MDX query. Select “[Product].[Family].&[4]” from the query. In the Parameter’s section write a parameter name “ChartProduct1” and click on “Insert” button.

Repeat the above step for “[Product].[Family].&[5]” and “[Product].[Family].&[6]” to add Parameters “ChartProduct2” and “ChartProduct3” respectively.

You should have a screen that looks similar to the following:

Create a Grid Report from the PDW Data source

Now let’s create a Grid report. To create a Grid report, let’s follow these steps:

Step 1: Right click PerformancePoint Content > New > Report and make a choice of “Analytic Grid” from “Select a Report Template” as shown below:

Step 2: Select a Data Source PDW which we created a short while ago. Rename your report to “PDW Grid Report”. The report view will look similar to the following:

Step 3: Now from the “Details” Window on your right hand side, drag and drop “Quarter” from “Time Dimension and Calendar Attribute” to the “Columns” section of our Grid Report and make a choice of “Q1-06” and “Q2-06”. Now drag and drop “City” from “Geography Dimension” to the “Rows” section and Product “Family” from Product Dimension to the “Rows” section of our Grid Report and make a choice of products – “Mountain Bikes”, “Road Bikes” and “Touring Bikes” as shown below:

Step 4: Now you are ready with a basic Grid Report. Let’s add parameters to this Grid report. To do so, click on the “Query” tab.

This will show you a MDX query. Select “[Product].[Family].&[4]” from the query. In the Parameter’s section write a parameter name “GridProduct1” and click on “Insert” button.

Repeat the above step for “[Product].[Family].&[5]” and “[Product].[Family].&[6]” to add Parameters “GridProduct2” and “GridProduct3” respectively.

You should have a screen that looks similar to the following:

That’s all. Your reports are ready with parameters.

Designing Filters for Dashboard

Now let’s design our “Filters” which we can use on our Dashboard. To do this, follow these steps:

Step 1: Right Click PerformancePoint Content > New > Filter. This will show you a popup window “Select a Filter Template”. From there, select “Member Selection” as shown below:

Click the OK button. You will now see a wizard. From this wizard, first select our data source “PDW” and click the “Next” button. The next step is where you will select a “Filter Dimension” and “Filter Members”

Step 2: For the “Filter Dimension” click a button “Select Dimension”. It will show you all the dimensions. From that list, select a dimension “Product.Family”. Now click on “Select Members” button and select all the products one by one.

Click next and keep the default settings as it is. Finish the wizard. Now rename your filter to “Product1” name.

Repeat the above step twice and add two more filters with the name “Product2” and “Product3”.

Step 3: Now our filters are ready. Let’s create a Dashboard which will consolidate our reports (Chart and Grid) and filters which we have created above.

Right click PerformancePoint Content > New > Dashboard which will use a Dashboard template “Header – Two Columns” as shown below:

Rename your Dashboard with “PDW Filter Dashboard”. Your Dashboard will look similar to the one shown below:

Step 4: Now right click the “Header” and click “Split Zone”. This will split Header zone in two sections. Now right click Zone1 and again click “Split Zone”. Now your dashboard will look similar to the following:

Step 5: Now let’s add our Chart Report into Left Column of our Dashboard and Grid Report to Right Column of our Dashboard. We will also add our Filters Product1, Product2, Product3 in Header, Zone1, Zone2 sections respectively as shown below:

Mapping Filters to Chart Report Parameters

Now you have placed the reports and filters to appropriate positions as per our requirement. Let’s map the filters “Product1”, “Product2”, “Product3” to our chart report parameters “ChartProduct1”, “ChartProduct2”, “ChartProduct3”. To do this let’s follow below steps:

First drop down your “Product1” filter and drag and drop “Member Unique Name” to the “Drop Fields to Create Connections” section of our “Analytic Chart Report” as shown below:

This will show you a popup window “Connection”. From that window, select your Parameter “ChartProduct1” and from “Connect To” dropdown list “Member Unique Name”.

Repeat the above steps for filters “Product2” and “Product3”.

Repeat this procedure for your Grid Report as well.

Your dashboard should now look similar to the one shown here:

Now Save your dashboard and deploy it to SharePoint Site. You will see the filters on top of your dashboard. Change the filters and see that both the reports are rendering the data as per your filter choice.

Here’s how your Dashboard will look on a SharePoint Site:

That’s all! You have successfully created a Dashboard with Common Filters and deployed it to SharePoint Site.

Conclusion

In this article, we have seen how to create a SharePoint Dashboard using filters. We have also seen how to apply these filters commonly to all the reports which we are consolidating in our Dashboard. For this demonstration, we have used Microsoft Multidimensional Data Source that is SQL Server Analysis Services(SSAS) Database.

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 sergey on Monday, February 28, 2011 6:19 PM
Can we customize the masterpage and pagelayouts?
Comment posted by Deepak Srivastav on Wednesday, August 24, 2011 4:05 PM
great post...this helped me alot.
Thanks
Comment posted by Sabin on Monday, November 14, 2011 3:42 PM
Can you please explain me how this can be done using SharePoint custom list.
I have sharepoint 2010 enterprise edition installed in my server. I want to create similar dashboard using sharepoint list instead of excel services.

Thank  You
Sabin
Comment posted by Colin Edwards on Monday, March 19, 2012 12:06 PM
I can not find the 'Proclarity Data Warehouse' database anywhere on the internet to try this demo out. Was the database renamed when Proclarity was bought up by Microsoft (like AdventureWorks20080)?
Comment posted by Colin Edwards on Monday, March 19, 2012 12:35 PM
Can you please do a demo for the new Adventure Works SSAS database or email a link to the 'Proclarity Data Warehouse' database so I can try this demo out THANKS
Comment posted by Manek on Friday, June 1, 2012 3:51 AM
Can we have a similar performance point charts and BI with filters using Excel data published on sharepoint using Excel services.?
Comment posted by Abul on Tuesday, December 11, 2012 1:28 AM
Can you please tell me, its possible to apply the same kind of filter in a grid which takes hierarchy value from dimension.
Ex: I have a hierarchy dimension which display overall sales amount, then clicking on the (+) sign will drill-down further to product, product category etc. I need to apply filter to this. Please let me know whether its possible.
Comment posted by Abul on Tuesday, December 11, 2012 1:32 AM
Can you please tell me, its possible to apply the same kind of filter in a grid which takes hierarchy value from dimension.
Ex: I have a hierarchy dimension which display overall sales amount, then clicking on the (+) sign will drill-down further to product, product category etc. I need to apply filter to this. Please let me know whether its possible.
Comment posted by Serge on Tuesday, December 18, 2012 5:04 PM
I could use a valid link to the data warehouse.  Unable to proceed.  I did see a request for this to be done using the adventureworks, I don't have that DW either.  I would love to try your demo.

Thanks,
Comment posted by Jon on Monday, January 28, 2013 3:34 AM
Hello. I've been digging to find out if it is possible to connect a filter between different pages in Performance point services. I have a dashboard with some filters and two different pages corresponding to two different scorecards in the dashboard. I would like to go from the dashboard to any of the reports (in different pages as it is requested by our client) and the reports have to use the selection of the dashboard.
Comment posted by Merry on Wednesday, May 8, 2013 6:28 AM
Please provide 'Proclarity Data Warehouse' database

Categories

JOIN OUR COMMUNITY

POPULAR ARTICLES

FREE .NET MAGAZINES

Free DNC .NET Magazine

Tags

JQUERY COOKBOOK

jQuery CookBook