Track project resource usage by using a dashboard built on Excel Services, PerformancePoint Services, and Project Server

Applies to: SharePoint Server 2010 Enterprise

You can use Excel Services in Microsoft SharePoint Server 2010 and PerformancePoint Services in Microsoft SharePoint Server 2010 with Microsoft Project Server 2010 data to create reports and dashboards to track performance. For example, you can create a dashboard to track resource usage for multiple projects.

This article contains information about how to create a basic dashboard that contains a PerformancePoint scorecard and an Excel Services report. You can use the example dashboard used in this article as a starting point to learn how you can use PerformancePoint Services and Excel Services together with Project Server to manage projects.

The example dashboard compares planned work to actual hours submitted so you can see at a glance which projects are on or off target as measured by resource usage. Sample data is used for our dashboard. However, you can use your own data to create a similar dashboard.

The dashboard described in this article resembles the following display.

In this article:

-
Plan the dashboard

-
Create and publish the dashboard

-
View and use the dashboard

Plan the dashboard

Before you begin to create dashboards, create a dashboard plan that considers the intended audience, the information that you want to display, and the kinds of items that you want to include in the dashboard. For more information, see Plan a PerformancePoint dashboard to show organizational performance.

Example dashboard plan

The example dashboard is targeted at project managers who are new to the business intelligence capabilities in SharePoint Server 2010 and Project Server 2010. By using sample data that is included with Project Server 2010, we create a scorecard that measures performance as measured by resource usage and an Excel Services report that provides additional information about planned and actual hours worked over time.

The following tools and applications are used to create the example dashboard:

• Excel 2010

• Excel Services

• PerformancePoint Services

The example dashboard is intentionally kept simple so that you can easily re-create it. The example dashboard consists of the following items:

• A scorecard that contains one key performance indicator (KPI) and a list of projects.

• An Excel Services report that contains a PivotChart report displayed as a line chart.

• A dashboard filter that determines how many projects are displayed in the scorecard.

• A dashboard page that contains the reports and the dashboard filter.

The following process is used to create the example dashboard:

1. Microsoft Excel 2010 is used to create a workbook (TimesheetData.xlsx) and publish it to SharePoint Server. We use the workbook to display a PivotChart report.

2. PerformancePoint Dashboard Designer is used to create and configure a scorecard, a report, a dashboard filter, and a dashboard page.

3. Dashboard Designer is used to publish the dashboard to SharePoint Server, where we can use it to view information.

Create and publish the dashboard

Based on the dashboard plan outlined in Example dashboard plan, we first create and assemble our dashboard items, and then we publish the finished dashboard to SharePoint Server. We first use Excel, and then we use PerformancePoint Dashboard Designer.

Create, configure, and publish an Excel workbook

For the example dashboard, we create and publish an Excel workbook that uses an external data connection. We first create the workbook and configure the data connection, and then we create a PlannedVsActualOverTime page that contains a PivotChart report. Then, we publish the workbook to SharePoint Server.

To create the TimesheetData workbook

1. In Excel 2010, on the Data tab, in the Get External Data group, click Existing Connections.

   The Existing Connections dialog box opens.

2. In the Existing Connections dialog box, click Browse for More.

3. In the Address box, type the Web site address (URL) to the Data Connections document library that contains your Project Server 2010 data connections.

   The URL resembles http://<server name>/<Project Web App site name>/<Business Intelligence Center name>/<Data Connections document library>.

   (For example, we use the URL http://litware-proj/pwa/projectbicenter/Data%20Connections%20for%20PerformancePoint/English%20(United%20States).)

4. Click Project Server – Timesheet Data, and then click Open.

5. In the Import Data dialog box, select the following options:

   • In the Select how you want to view this data in your workbook section, select PivotChart and PivotTable Report.

   • In the Where do you want to put the data? section, select Existing Worksheet.

6. Click OK to populate the worksheet.

7. Add data to the PivotTable report and the PivotChart report by specifying the following options:

   1. From the PivotTable Field List pane, drag Planned Work to the Values section.

   2. From the PivotTable Field List pane, drag Timesheet Line Actual Work to the Values section.

   3. From the PivotTable Field List pane, drag Period Start Date to the Row Labels section.

   The PivotTable report populates, and the PivotChart report displays a bar chart.

8. Click the bar chart, and then in the PivotChart Tools section of the ribbon, on the Design tab, in the Type group, click Change Chart Type.

9. In the navigation pane, click Line, and then select a line chart type. Click OK.

10. To adjust the size and position of the chart, make the following changes:

    1. In the upper-left corner of the PivotTable report, click the Row Labels column heading, and then type Time Period to change the column heading.

    2. Click the Sum of Planned Work column heading, and then type Planned to change that column heading.

    3. Click the Sum of Timesheet Line Actual Work column heading, and then type Actual to change that column heading.

    4. Reduce the width of the Planned and Actual columns.

    5. Move the PivotChart report so that it is closer to the PivotTable report. Align it with the upper-right corner of the PivotTable report.

11. Rename the worksheet to “PlannedVsActualOverTime.”

12. Define the PivotChart and PivotTable as a named item. Do this by using the following procedure:

    To define the PivotChart and PivotTable as a named item

    1. On the PlannedVsActualOverTime worksheet, select the range of cells that contains both the PivotTable report and the PivotChart report. Then, on the Formulas tab, in the Defined Names group, click Define Name.

       The New Name dialog box opens.

    2. In the Name box, type PlannedVsActualOverTime, and then click OK.

Now that we have created the Excel workbook, the next step is to publish it to SharePoint Server 2010.

To publish the workbook

1. On the File tab, click Save & Send, and then click Save to SharePoint.

2. In the Save to SharePoint section, click Publish Options.

3. In the Publish Options dialog box, on the Show tab, use the list to select Items in the Workbook.

4. Select all the named items in the list, and then click OK.

5. In the Save to SharePoint section, click Browse for a location, and then click Save As.

   The Save As dialog box opens.

6. In the Save As dialog box, specify the following options:

   • In the address box, type the URL to the Sample Reports document library.

     The URL resembles http://<server name>/<Project Web App site name>/<Business Intelligence Center name>/< document library>.

     (For example, we use the URL http://litware-proj/pwa/projectbicenter/Sample%20Reports.)

   • In the File name box, type TimesheetData.

7. Click Save to publish the workbook.

Open Dashboard Designer

You can easily open Dashboard Designer from the Business Intelligence Center, as described in the following procedure.

To install and open Dashboard Designer

1. Using your web browser, open the Project Web App site.

2. In the Quick Launch, click Business Intelligence.

   The Business Intelligence Center opens.

3. In the Business Intelligence Center, click a section, such as Monitor Key Performance, Build and Share Reports, or Create Dashboards.

   More information about each area is displayed in the center pane.

4. In the center of the screen, click a Start using PerformancePoint Services link.

   The PerformancePoint site template opens.

5. Click Run Dashboard Designer.

After you have opened Dashboard Designer at least one time, you can open it from the Start menu on your computer. To do that, click Start, and then click All Programs. Locate SharePoint, and then click PerformancePoint Dashboard Designer. Or, you can continue to open Dashboard Designer from the Business Intelligence Center.

For more information about Dashboard Designer, see Plan, design, and implement a PerformancePoint dashboard.

Create a data connection in Dashboard Designer

Using Dashboard Designer, we create a data connection to a table that is stored in SQL Server 2008 R2. We use this data source to create a KPI, a scorecard, and a dashboard filter.

To create the SQL Server table data connection

1. In Dashboard Designer, in the Workspace Browser, click Data Connections.

2. On the Create tab, in the Dashboard Items group, click Data Source.

3. In the Select a Data Source Template dialog box, select SQL Server Table, and then click OK.

4. In the center pane, on the Editor tab, in the Connection Settings section, specify the following settings:

   1. In the Server box, type the name of the server that contains the SQL Server table.

      (For example, we use litware-sql.)

      Note

      The PerformancePoint Services Unattended Service Account must have Read permissions to the server that you specify here.

   2. In the Database box, type PWA_Reporting, which is the name of the database that contains the table that we want to use.

   3. In the Table list, select dbo.MSP_TimesheetLine_UserView.

5. In the Data Source Settings section, keep the default selections, and then click Test Data Source.

   Tip

   If the Test Data Source button is not enabled, click in the Refresh Interval box to enable it.

6. If the Test Connection dialog box indicates that the connection is successful, click Close. If the Test Connection dialog box indicates that the connection failed, make sure that your environment is configured correctly and then repeat this procedure from Step 4.

7. In the center pane, click the Properties tab. Then, in the Name box, type Timesheets to name the data connection.

8. In the Workspace Browser, right-click the data source, and then click Save.

9. In the center pane, click the View tab. Then click Preview Data to view the data.

   For this example, we note the columns that are in the following list:

   • Column 1 contains a list of time periods and is named “PeriodName.”

   • Column 7 contains a list of projects and is named “ProjectName.”

   • Column 9 contains a list of planned work hours and is named “Planned Work.”

   • Column 10 contains a list of billable work hours and is named “Billable Actual Work.”

   • Column 12 contains a list of overtime billable work hours and is named “Billable Actual Overtime Work.”

   • Column 14 contains a list of actual work hours (billable or otherwise), and is named “Timesheet Line Actual Work.”

   We use these columns in our scorecard and dashboard filter.

   Tip

   When you create the scorecard and dashboard filter, you might see the columns listed either by number or by name. The columns we use are included in this section for you to use as reference information if you see the columns listed by number.

For more information about how to create data sources in Dashboard Designer, see Create data connections (PerformancePoint Services).

Create a scorecard

Using Dashboard Designer, we create a scorecard. To create the scorecard, we first create a KPI. The KPI that we create uses calculated metrics to compare planned billable work hours to actual billable hours worked. We use calculated metrics because we use the sum of two columns for the Actual metric, and a third column for the Target metric.

To create a KPI that uses calculated metrics

1. In Dashboard Designer, in the Workspace Browser, click PerformancePoint Content.

2. On the Create tab, in the Dashboard Items group, click KPI.

3. Select Blank KPI, and then click OK.

   The KPI opens for editing in the center pane of the workspace.

4. Create a calculated metric for the Actual value by using the following procedure.

   To configure data mappings for the KPI’s Actual metric

   1. In the center pane, on the Editor tab, in the Data Mappings column, in the Actual row, click the hypertext that says 1(Fixed values).

      The Fixed Values Data Source Mapping dialog box opens.

   2. Click Change Source to open the Select a Data Source dialog box.

   3. Click the Calculated Metric tab.

   4. In the Templates section, select Blank Calculation, and then click OK.

      The Calculated Metrics Data Source Mapping dialog box opens.

   5. In the Source column for Value1, click the hypertext that says 1 (Fixed values).

      The Fixed Values Data Source Mapping dialog box opens.

   6. Click Change Source to open the Select a Data Source dialog box.

   7. Select the Timesheets data source, and then click OK.

      The Dimensional Data Source Mapping dialog box opens.

   8. In the Select a measure section, use the list to select Column 10, which is the “Billable Actual Work” column, and then click OK.

      The Dimensional Data Source Mapping dialog box closes, and the Calculated Metrics Data Source Mapping dialog box is open.

   9. In the Source column for Value2, click the hypertext that says 1 (Fixed values).

      The Fixed Values Data Source Mapping dialog box opens.

   10. Click Change Source to open the Select a Data Source dialog box.

   11. Select the Timesheets data source, and then click OK.

       The Dimensional Data Source Mapping dialog box opens.

   12. In the Select a measure section, use the list to select Column 12, which is the “Billable Actual Overtime Work” column, and then click OK.

       The Dimensional Data Source Mapping dialog box closes, and the Calculated Metrics Data Source Mapping dialog box is open.

   13. In the Formula section, type Value1+Value2. Then click OK.

       (We use the formula “Value1+Value2” because we want this metric to use the sum of Billable Actual Work and Billable Actual Overtime Work.)

5. Create a standard measure for the Target value by using the following procedure.

   To configure data mappings for the KPI’s Target metric

   1. In the center pane, on the Editor tab, in the Data Mappings column, in the Target row, click the hypertext that says 1(Fixed values).

      The Fixed Values Data Source Mapping dialog box opens.

   2. Click Change Source to open the Select a Data Source dialog box.

   3. Select the Timesheets data source, and then click OK.

      The Dimensional Data Source Mapping dialog box opens.

   4. In the Select a measure section, use the list to select Column 9, which is the “Planned Work” column, and then click OK.

6. Format the numbers for the scorecard by using the following procedure.

   To format the numbers in a scorecard

   1. In the center pane, on the Editor tab, in the Number column, in the Actual row, click the hypertext that says (Default).

      The Format Numbers dialog box opens.

   2. Use the Format list to select Number.

   3. In the Decimals box, type 1.

   4. Click OK.

   5. Repeat these steps for the Target row.

7. Configure the KPI’s indicators by using the following procedure.

   To configure the indicators that are used in the KPI

   1. In the center pane, on the Editor tab, in the Indicators column, in the Target row, click the indicators that are shown.

   2. In the Thresholds section, click Set Scoring Pattern and Indicator.

      The Edit Banding Settings dialog box opens.

   3. On the Select the Scoring Pattern page, use the Scoring pattern list to select Closer to Target is Better, and then click Next.

   4. On the Select an Indicator page, select a Stoplight indicator, and then click Next.

   5. On the Specify the worst value page, type the number 0, and then click Finish.

8. In the center pane, click the Properties tab. Then, in the Name box, type Planned vs Actual Billable Work to name the KPI.

9. In the Workspace Browser, right-click the KPI, and then click Save.

After we create the KPI, we add it to the scorecard, and then we add dimension members to rows in the scorecard. Finally, we configure view settings for the scorecard.

To create the blank scorecard

1. In Dashboard Designer, in the Workspace Browser, click PerformancePoint Content.

2. On the Create tab, in the Dashboard Items group, click Scorecard.

   Depending on how Dashboard Designer is configured, one of the following actions occurs:

   • The Select a Scorecard Template dialog box opens. If this occurs, proceed to Step 3.

   • A blank scorecard opens for editing. If this occurs, skip Step 3, and proceed to Step 4.

   Tip

   You can easily configure Dashboard Designer either to use the Create a Scorecard wizard or to create a blank scorecard every time that you create a scorecard. For more information, see Turn on or off the scorecard wizard in Dashboard Designer.

3. In the Select a Scorecard Template dialog box, in the Category pane, select Standard, and in the Template pane, click Blank Scorecard. Then, click OK.

   The scorecard opens for editing in the center pane of the workspace.

4. In the center pane, click the Properties tab. Then, in the Name box, type Resource Utilization Scorecard to name the scorecard.

5. In the Workspace Browser, right-click the scorecard, and then click Save.

To add the KPI to the blank scorecard

1. Begin with the scorecard open for editing in Dashboard Designer.

2. In the Details pane, expand KPIs, and then expand PerformancePoint Content.

3. Drag the Planned vs Actual Billable Work KPI to the Drop items here section on the left side of the screen.

   The scorecard updates to show the KPI as a row in the scorecard.

4. On the Edit tab, in the View group, click Update.

   The scorecard updates to show overall performance for the KPI.

5. In the Workspace Browser, right-click the scorecard, and then click Save.

For more information about how to add KPIs to a scorecard, see Add KPIs to a scorecard by using Dashboard Designer.

To add dimension members to the scorecard

1. Begin with the scorecard open for editing in Dashboard Designer.

2. In the Details pane, use the Data Source list to select the Timesheets data source.

3. In the Details pane, expand Dimensions, and then drag Column 7, which is the “ProjectName” column, to the scorecard, positioning it on the line between the name of the KPI and the Actual column in the scorecard.

   The Select Members dialog box opens.

4. Right-click in the Select Members dialog box, click Select Visible to select all the dimension members, and then click OK.

5. On the Edit tab, in the View group, click Update.

   The scorecard updates to show performance information for the dimension members that have been added.

6. In the Workspace Browser, right-click the scorecard, and then click Save.

For more information about how to add dimension members to a scorecard, see Add dimension members to a scorecard by using Dashboard Designer.

As a dashboard author, you can configure view settings for most view types that include scorecards. For example, you can specify whether the scorecard should contain a toolbar that dashboard consumers can use to apply filters or use rollup KPIs. For the example dashboard, we apply the settings that are included in the following procedure.

To configure view settings for the scorecard

1. Begin with the scorecard open for editing in Dashboard Designer.

2. On the Edit tab, in the View group, click Settings.

3. In the navigation pane of the View Settings dialog box, click Toolbar, and then select Show scorecard toolbar.

   By default, all the options under Show scorecard toolbar should be selected.

4. In the navigation pane of the View Settings dialog box, click Filters, and then select Filter empty rows.

5. Click OK to close the View Settings dialog box.

6. In the Workspace Browser, right-click the scorecard, and then click Save.

Scorecards can vary widely in complexity and style. For more detailed information about how to create scorecards, see Create a scorecard by using Dashboard Designer.

Create a dashboard filter

Using Dashboard Designer, we create a Projects filter for the example dashboard. The filter enables dashboard users to select one or more projects for which they want to view performance information in the scorecard. We create the filter now, and then connect it to the scorecard later in this article.

To create the dashboard filter

1. In Dashboard Designer, in the Workspace Browser, click PerformancePoint Content.

2. On the Create tab, in the Dashboard Items group, click Filter.

   The Select a Filter Template dialog box opens.

3. In the Template pane, click Member Selection, and then click OK.

4. On the Select a data source page, select the Timesheets data source, and then click Next.

5. On the Select Members page, click Select Dimension.

6. Select Column 7, which is the “ProjectName” column, and then click OK.

   The Select Members button becomes active.

7. Click Select Members, and wait for the list of dimension members to display.

8. Right-click in the Select Members dialog box. Then click Select Visible to select all the dimension members, and then click OK.

9. Click Next to proceed to the Select Display Method page.

10. Select Multi-Select Tree, and then click Finish.

11. In the center pane, click the Properties tab. Then, in the Name box, type Projects to name the filter.

12. In the Workspace Browser, right-click the filter, and then click Save.

For more information about how to create dashboard filters, see Create a dashboard filter by using Dashboard Designer.

Create a Web Part to display the Excel Services report

By using Dashboard Designer, we create a PerformancePoint Web Part to display an Excel Services report in our dashboard. First, we create the basic structure of the report. Then, we create a connection to the Excel workbook that we want to use.

To create the basic structure of the report

1. In Dashboard Designer, in the Workspace Browser, click PerformancePoint Content.

2. On the Create tab, in the Reports group, click Other Reports.

   The Select a Report Template dialog box opens.

3. In the Select a Report Template dialog box, click Excel Services, and then click OK.

   The report opens for editing in the center pane of the workspace.

4. In the center pane, click the Properties tab. Then, in the Name box, type Excel Services Report to name the report.

5. In the Workspace Browser, right-click the report, and then click Save.

To create a connection to the Excel Services workbook

1. Begin with the Excel Services report open for editing in Dashboard Designer.

2. In the center pane, on the Editor tab, in the Report Settings section, specify the following settings:

   1. In the SharePoint Site box, type the URL to the site that contains the document library where the Excel Services file is stored.

      The URL resembles http://<Project Web App site name>/<Business Intelligence Center name>.

      (For example, we use the URL http://litware-proj/pwa/projectbicenter.)

   2. In the Document Library list, select the Sample Reports document library.

   3. In the Excel Workbook list, select English (United States)/TimesheetData.xlsx.

   4. In the Item Name list, select PlannedVsActualOverTime.

      Note

      The Item name list will be empty if one of the following conditions is true:

      • The Excel Services report has no named items.

      • The Excel Services report contains named items. However, those named items have not been published to SharePoint Server 2010.

      If this occurs, republish the workbook to make sure to publish the named items. See the procedure “To publish the workbook,” earlier in this article.

3. If you want to view the report, click the View tab in the center pane.

4. In the Workspace Browser, right-click the report, and then click Save.

For more information about how to display Excel Services reports in PerformancePoint Web Parts, see Create an Excel Services report by using Dashboard Designer.

Create and assemble a dashboard page

Now that we have created our individual dashboard items, we are ready to create and assemble our dashboard page. First, we create the page, selecting a particular page layout template. Then, we add the scorecard, Excel Services report, and filter to the page. Finally, we connect the dashboard filter to the scorecard.

To create the dashboard page

1. In Dashboard Designer, in the Workspace Browser, click PerformancePoint Content.

2. On the Create tab, in the Dashboard Items group, click Dashboard.

   The Select a Dashboard Page Template dialog box opens.

3. Select Header, 2 Columns, and then click OK.

4. In the center pane, on the Editor tab, in the Pages section, click the text that says Page 1, and type Project Resource Utilization to name the dashboard page.

5. In the center pane, click the Properties tab. Then, in the Name box, type Project Resource Utilization to name the dashboard. When the dashboard is published, the name that you assign to the dashboard appears as a folder in SharePoint Server.

6. In the Workspace Browser, right-click the dashboard, and then click Save.

You can create dashboards that contain multiple pages by using the same or different page layout templates. For more information, see Create a dashboard page by using Dashboard Designer.

To add items to the dashboard page

1. Begin with the dashboard open for editing in Dashboard Designer.

2. Click the Editor tab.

3. In the Details pane, expand Scorecards, and then expand PerformancePoint Content. Drag the Resource Utilization Scorecard to the Left Column zone.

4. In the Details pane, expand Reports, and then expand PerformancePoint Content. Drag Excel Services Report to the Right Column zone.

5. To avoid having additional scrollbars displaying for the Excel Services report, you must specify auto-size settings. To do this, follow these steps.

   To specify auto-size settings for an Excel Services report

   1. On the Edit tab, click Edit Item to open the Item Settings dialog box.

   2. On the Size tab, select both the Auto-size width and Auto-size height options, and then click OK.

6. In the Details pane, expand Filters, and then expand PerformancePoint Content. Drag the Projects dashboard filter to the Header zone.

7. In the Workspace Browser, right-click the dashboard, and then click Save.

For more information about how to add items to a dashboard page, see Add dashboard items to a page by using Dashboard Designer.

To connect the dashboard filter to the scorecard

1. Begin with the dashboard open for editing in Dashboard Designer.

2. Click the Editor tab.

3. Click the Projects filter. Then, on the Edit tab, click Create Connection to open the Connection dialog box.

4. On the Items tab, use the Send values to list to select Left Column – (1) Resource Utilization Scorecard.

5. On the Values tab, use the Connect to list to select Row, which is located in the Axis section.

6. Use the Source value list to select Member Unique Name.

7. Click OK to close the Connection dialog box.

8. In the Workspace Browser, right-click the dashboard, and then click Save.

When you connect a dashboard filter to a report or a scorecard, you typically have many configuration options available to you. For more information, see Connect a filter to a report or a scorecard by using Dashboard Designer.

Publish the dashboard

Now that we have created and assembled our dashboard, we can publish it to SharePoint Server so that we can view and use it.

To publish the dashboard

1. Begin with the dashboard open for editing in Dashboard Designer.

2. In the Workspace Browser, right-click the dashboard, and then click Deploy to SharePoint.

   The Deploy To dialog box opens.

3. In the Deploy To dialog box, expand Business Intelligence Center, and then select Dashboards.

4. In the Master Page list, select v4, and then click OK.

   (The v4 item corresponds to the default master page for PerformancePoint dashboards.)

   The dashboard is published to SharePoint Server and then opens in a web browser.

   Note

   If the dashboard does not deploy and you receive an error message, you might not have the necessary permissions assigned to you in SharePoint Server. You must have at least Design permissions assigned to you. For more information, see Authorization and permissions in PerformancePoint Services (SharePoint Server 2010).

For more information about how to publish dashboards, see Deploy a PerformancePoint dashboard.

View and use the dashboard

Now that we have published the example dashboard to SharePoint Server, we can use it to obtain answers to specific questions, such as “Which project is the most off target as measured by resource usage?” To answer that question, we first make sure all the projects are visible in the scorecard.

We select all the items in the filter.

To apply the dashboard filter

1. Click the Projects filter to expand it. Notice that it uses an expandable tree control, which enables us to select one or more items.

2. Select the Select visible check box to select all the items.

3. Click Apply.

   The filter closes, and the scorecard updates to display rows for the items that we selected by using the filter.

Now we can easily see which projects are the most over or under target as measured by resource usage.

Now suppose that we only want to view performance information for some projects, instead of all projects. To configure which projects are displayed, reapply the dashboard filter, clearing the check boxes of the projects that you do not want to view.

See Also

Concepts

Plan, design, and implement a PerformancePoint dashboard