APPLIES TO: 2013 2016 2019 SharePoint Online
![]() -->
Nov 29, 2017 I had the same problem trying to create a relationship between a table with multiple duplicate task start dates and a date table that I created in Excel. I knew my date table didn't have duplicate values, but still couldn't get the relationship to connect.
This article describes, step by step, how to use Excel 2016 to create a basic dashboard that contains two reports and two filters. The example dashboard described in this article resembles the following image:
Figure: Basic Excel Services dashboard that contains two reports and two slicers
Our example dashboard uses data that is imported into Excel using an OData data feed. This makes it possible to publish the workbook to a library in SharePoint Server 2013. By following the steps in this article, you'll learn how to import data into Excel, use that data to create reports in a worksheet, and connect filters to those reports.
Before you begin
Before you begin this operation, review the following information about prerequisites:
![]() Plan the dashboard
Before you begin to create a dashboard, we recommend that you create a dashboard plan. The plan does not have to be extensive or complex. However, it should give you an idea of what you want to include in the dashboard. To help you prepare a dashboard plan, consider questions such as the following:
Our example dashboard is designed to be a prototype that you can use to learn how to create and publish Excel Services dashboards. To show how we might create a dashboard plan for a similar dashboard, see the following table.
Table: Basic plan for our example dashboard
Now that we have created our dashboard plan, we can begin to create the dashboard.
Create the dashboard
To create the dashboard, we begin by creating a data connection. Then, we use that data connection to import data into Excel. Next, we create the reports and the filter that we want to use. After that, we publish the workbook to SharePoint Server 2013.
Part 1: Get data into Excel
Our example dashboard uses data that is imported into Excel via an OData data feed to connect to Adventure Works sample data. For more information, see AdventureWorks OData Feed (on CodePlex). We begin by importing data into Excel.
To import data into Excel by using an OData data feed
At this point, we have imported data into Excel by using an OData data feed. The next step is to create a relationship between the tables of data. To do that, we'll use the Power Pivot Add-In for Excel. If the PowerPivot tab is not visible in Excel, enable the add-in by using the following procedure.
To enable the PowerPivot add-in for Excel
Now that the Power Pivot add-in for Excel is enabled, the next step is to create a relationship between the tables of data. This will enable us to create reports and filters using data from the two tables.
To create a relationship between tables in a Data Model
At this point, we have imported two tables of data into Excel. We have also created a relationship between the tables so that we can create reports and filters that use the two tables as a single data source.
Part 2: Create reports
For our example dashboard, we'll create two reports, as described in the following table:
Table: Dashboard reports
We begin by creating the ProductSales report.
To create the ProductSalesReport chart
At this point, we have created a PivotChart report showing product sales. The next step is to create a PivotChart report that shows sales amounts across different geographical locations.
To create the GeoSalesReport chart
At this point, we have created our two reports for our basic dashboard. The next step is to create filters.
Part 3: Add filters
Using Excel, there are several different kinds of filters we can create and add to a dashboard. For example, we can create a simple filter by putting a field in the Filter section of the Fields list. We can create a slicer, or, if we are using a multidimensional data source such as Analysis Services, we can create a timeline control. For this example dashboard, we'll create two slicers. This filter will enable people to view information for a particular year or a particular sales representative.
To add slicers to the dashboard
At this point, we have created a dashboard. The next step is to publish it to SharePoint Server 2013, where it can be used by others.
Publish the dashboard
To publish the workbook to SharePoint Server 2013, we'll follow a two-step process. First, we make some adjustments that affect how the workbook is displayed. Then, we publish the workbook to a SharePoint library.
![]()
We begin by making adjustments to the workbook. By default, our example dashboard displays gridlines on the worksheet that contains our dashboard. In addition, by default, the worksheet is called Sheet1. We can make some minor adjustments that will improve how the dashboard will be displayed.
To make minor display improvements to the workbook
The next step is to publish the workbook to a SharePoint library. The workbook uses native data that we imported into Excel, which means that we can publish it to a library in SharePoint Server 2013. Use one of the following procedures to publish the workbook.
To publish the workbook to a library in SharePoint Server 2013
Now that we have created and published the dashboard, we can use it to explore data.
Use the dashboard
After the dashboard was published to SharePoint Server 2013, it is available for people to view and use it.
To open the dashboard
Now that the dashboard is open for viewing, we can use it to obtain answers to specific questions, such as those that are described in the following table.
Table: Using the dashboard to obtain answers to specific questions
See alsoConcepts![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |