SharePoint: Create Dashboards with KPI, Scorecard and Report using Excel Services

Posted by: Pravinkumar Dabade , on 3/11/2011, in Category SharePoint
Views: 91249
Abstract: In this article we will create a SharePoint dashboard using Excel Services Data Source. This Dashboard will contain KPIs, Scorecard and a Report. Then we will deploy this dashboard to our SharePoint Site.

In this article we will create a dashboard using Excel Services Data Source. This Dashboard will contain KPIs and a Scorecard. Then we will deploy this dashboard to our SharePoint Site.

The steps to create a SharePoint Site using a Template Business Intelligence Center remains the same as shown in my previous article SharePoint Dashboard with Common Filters using PerformancePoint Services 2010. I also assume you have downloaded the Dashboard Designer tool at your end. If you not yet done so, please check my previous article and check the initial few steps to learn how to do so.

 

This Dashboard Designer tool will help you to design the Data Sources, KPIs, Scorecards, Reports, Filters and Dashboards. The tool is shown below :

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

1) Data Connections.

a. It is used for creating a connection with different types of data sources.

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

2) PerformancePoint Content –

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

For this demonstration we are going to use Excel Service Data source. So, let’s get ready with some excel data as shown below:

Note: For those who have read my previous article Create Excel Dashboards in SharePoint, please note that the previous article covered a scenario where the Excel workbook is located on the client machine.
 
SharePoint has a Excel Service which keeps Excel document on server and renders it on the browser. So in this article, we are using Excel services and designing the Dashboard with Excel Report and Scorecard with KPIs.

Once you fill the data in the excel sheet, select the columns and rows and name it by going to ‘Design’ ribbon - ‘Sales Table’ as shown below:

Now save your excel work book with a name of your choice. After this step, we need to publish this workbook to our SharePoint site. To do so, we will create a ‘Document Library’ in our SharePoint site. Follow below steps:

From your home page, click on ‘All Site Content’ link button as shown below:

This will show you all the contents of SharePoint site. From that page, click on ‘Create’ link button and from the Library section choose the ‘Document Library’ and name it as ‘Excel Documents’ as shown below:

Click on ‘Create’ button. This will create a document library in our SharePoint site. Now let’s get back to our excel workbook. In the excel workbook, click on ‘File’ menu and make a choice of ‘Save and Send’ menu. This will show you a ‘Save to SharePoint’ option. Make a choice of ‘Save to SharePoint’ and click on ‘Publish Options’ as shown below:

Once you click on ‘Publish Options’, you will see a dialog box which will ask you to publish the complete workbook. Click on a drop down box and make a choice of ‘Sheets’. Then from the list of sheets, make a choice of ‘Sheet1’ as shown below:

Now click on the ‘OK’ button. After this step, click on ‘Save As’ button and type the URL of your document library (http://localhost:23964/sites/sqlbiDashboard/Excel%20Documents) and hit the ‘Enter Key’:

Save this Excel workbook with the name ‘Sales Data’ as shown above. Once you save it, it will show you the excel sheet in the browser. So now we are ready with our Excel data.

Now let’s create a Data source using our Dashboard Designer tool. For creating Excel Services Data source follow these steps:

Right click Data Connection > Click to New Data Source as shown below:

From Select a Data Source Template window, make a choice of ‘Excel Services’ as shown here:

Rename this data source with ‘Excel DS’. Now let’s configure the data source for fetching the data from Excel workbook which is configured in our excel services. For this specify the site URL, make a choice of Document Library, then make a choice of Excel Workbook and at the end write a name of the table which we have given while designing the table in excel workbook; that is ‘Sales Table’ as shown below:

Once you configure the data source, test the data source by clicking ‘Test Data Source’ button shown in the above figure, and save the data source.

Now go to ‘View’ tab and preview the data. If you check the column names, they are ‘Column1’, ‘Column2’ etc. So we will rename the columns as per our excel worksheet. Choose the column as shown below and rename it from the ‘Details – Properties section’ window on the right hand:

Now repeat the above step for all the other columns. The column names are listed in the following table:

Create Scorecard and KPI’s from Excel Service Data Source

Now let’s create a Scorecard and KPIs from our Excel Service Data source which we created in the above steps. To do this follow these steps:

Right click PerformancePoint Content > New > Scorecard. From the Tabular category, choose ‘Excel Services’ as shown below:

Click on OK button. It will take you to the wizard. The first step is to choose the Data source. Now let’s make a choice of ‘Excel DS’ data source which we created a few steps back. Click on ‘Next’ button.

In the next step, let’s add two KPIs :

Your wizard should look similar to the following:

Keep all the default options and complete the wizard. Rename your scorecard with ‘Excel Service Scorecard’.

After this, we are going to modify our Scorecard. From the ‘Details’ window on the right hand side, collapse the dimensions and drag and drop ‘State’ dimension to ‘Sales’ and ‘COGS’ KPIs as shown below:

Once you drag and drop the ‘State’ dimension, it will show you a dialog box of all the available States. Make a choice of country ‘Delhi’, ‘Mumbai’ and ‘Pune’.

Now go to ‘Edit’ Ribbon and click on ‘Update’ button. Now drag and drop ‘Time’ dimension on ‘Actual’ and ‘Target’ as shown below and click the ‘Update’ button:

Once you drag and drop the ‘Time’ dimension, it will show you a dialog box of all the available Time. Make a choice of year ‘Q1 CY 2006’ and ‘Q2 CY 2006’.

Now you are ready with your scorecard.

Creating an Excel Service Report

Let’s create an Excel Service report. To create a report, follow these steps:

Right click PerformancePoint Content > New > Report. From the ‘Select a report template’ dialog box choose ‘Excel Services’ as shown below:

Rename your report with ‘Excel Services Report’ and configure the report options as shown below:

That’s all your report is ready.

Add Excel Services Scorecard and Report to Dashboard

Now let’s add our scorecard and report to the dashboard. To do so, follow these steps:

Right click PerformancePoint Content > New > Dashboard. Make a choice of ‘Two Column’ dashboard template from the pop-up dialog box. Now from the ‘Details’ window collapse the scorecards Drag and Drop ‘Excel Service Scorecard’ to left column of the dashboard. Now collapse the Reports section and choose the Excel Services Report and drag and drop it to right column of the Dashboard as shown below:

Now rename your dashboard ‘Excel Services Dashboard’ and Save it. Then deploy it to SharePoint Site. After deploying it to SharePoint Site, you should see something similar:

Conclusion

In this article, we have seen how to make use of Excel Services data for creating Excel Services Data Source in Dashboard Designer tool. We have also seen how to create KPIs, Scorecard, Report and a Dashboard using Excel Services Data Source. For this demonstration we have used Excel Service as a source of our data for creating a 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 DC 7 on Thursday, May 31, 2012 12:43 PM
Hi Pravin,
Is this possible to create KPI, Scorecard and Report using SharePoint Online? If not possible, what can you recommend to have Dashboard in SP Online 365. Thanks
Comment posted by A nice information on Friday, June 29, 2012 1:48 AM
If MS Office is not present in SP Server, how we have to proceed for Import from Excel WorkBook(Not Excel Services)
Comment posted by Mayank P on Thursday, October 4, 2012 7:38 AM
Hi Pravin,

Can we design dashboard without using PPS in SharePoint 2010. If yes, could you pl share the details.
Comment posted by Laura on Wednesday, March 20, 2013 8:40 AM
Hi, Pravin.  When I do the steps you have outlined here, only one member shows up under my dimensions.  No matter what I do, it only shows the one member to choose.  Can you help tell me what I may be doing wrong?

Thanks!
Comment posted by Darshan on Wednesday, June 12, 2013 1:56 PM
Pravin,

I am having same problem as Laura - I am getting only 1 memeber in customer dimension.  Can you please help us?

Thanks,
Comment posted by aamy on Monday, June 17, 2013 7:21 AM
Great work :)
Comment posted by Vijay Jaiswal on Friday, July 26, 2013 12:50 AM
I want to learn more excel, need your valuable help.
Comment posted by Kiran on Thursday, September 19, 2013 3:05 PM
I am having same problem getting only 1 memeber in state time dimension.  Can you please help us?
Comment posted by Riya Menghani on Monday, October 7, 2013 1:53 AM
Great article to start with Performance Point services in SharePoint 2010.

Categories

JOIN OUR COMMUNITY

POPULAR ARTICLES

FREE .NET MAGAZINES

Free DNC .NET Magazine

Tags

JQUERY COOKBOOK

jQuery CookBook