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

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.

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

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