Share via

Create an Excel Services report by using Dashboard Designer

  • Article

 

Applies to: SharePoint Server 2010 Enterprise

By using PerformancePoint Dashboard Designer, you can create Excel Services in Microsoft SharePoint Server 2010 reports for your dashboards. An Excel Services report is a Microsoft Excel workbook that was published to Microsoft SharePoint Server 2010 by using Excel Services. Most Excel Services reports contain one or more charts and tables. When you add an Excel Services report to a PerformancePoint dashboard, you reuse an existing report that can contain views, such as PivotTable reports and PivotChart reports, which you cannot create by using Dashboard Designer. In addition, you can connect PerformancePoint dashboard filters to the Excel Services report. For more information, see Overview of Excel Services reports displayed in PerformancePoint Web Parts.

In this article:

  • Create a reference to an Excel Services report

  • Publish named items in an Excel workbook

Create a reference to an Excel Services report

In Dashboard Designer, you do not actually create an Excel Services report. Instead, you create a PerformancePoint Web Part to display an existing Excel Services report.

Important

Before you start, make sure that you have collected the following information:

  • The name of the report that you want to use

  • The Microsoft SharePoint Server location (including the SharePoint site and document library) where the Excel Services report is stored

  • Any named items that might exist in the Excel Services report

  • Any parameters that might exist in the Excel Services report, and information about how those parameters are configured. (You would typically need this information to connect a PerformancePoint dashboard filter to the Excel Services report.)

    If the Excel Services workbook is stored in a different server farm than the one that you are using for Dashboard Designer, parameters in the Excel workbook will not be available in Dashboard Designer.

Also make sure that you and the dashboard users have the necessary permissions to view the report.

When you create an Excel Services report, you do it in several stages, as described in the following list:

  1. Create the basic structure of the report.

  2. Create a connection to the report.

  3. Configure the report to display the particular information that you want to show.

  4. (This is optional.) Specify toolbar and interactivity settings for the report.

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.

  5. In the Name box, type the name that you want to use for the report.

  6. (This step is optional.) To specify a location for the report, click the Display Folder button, and then select or create a folder.

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

  8. Create a connection to the report.

To create a connection to the report

  1. Begin with the report open for editing in Dashboard Designer. In the center pane, click the Editor tab.

  2. In the Report Settings section, in the SharePoint site box, do one of the following:

    • Type the Web site address (URL) of the SharePoint site where the Excel Services report is hosted.

    • If the SharePoint site list is available, use it to select the SharePoint site where the Excel Services report is hosted.

  3. Use the Document library list to select the document library where the Excel Services report is hosted.

  4. Use the Excel workbook list to select the Excel Services report that you want to display.

  5. (This step is optional.) If you want to view the report, do one of the following:

    • To view the report in a browser window, on the Editor tab, click the View button that is located next to the Excel workbook list.

    • To view the report in Dashboard Designer, in the center pane of the workspace, click the View tab.

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

After you have created and saved the report, you can configure it, or you can add it to a dashboard page.

To configure the report to display particular information

  1. Begin with the report open for editing in Dashboard Designer. In the center pane, click the Editor tab.

  2. (This step is optional.) If you want to display an item in the workbook instead of display the whole workbook, use the Item name list to select an item.

    If the Item name list is empty, see Publishing named items in an Excel workbook later in this article.

  3. (This step is optional.) If the Excel Services report includes parameters and you want to specify default selections for those parameters, follow these steps:

    1. In the Workbook Parameters section, locate the parameter for which you want to specify a default value.

      Note

      If the Workbook Parameters section is empty, either the Excel Services report has no parameters or the workbook is stored in a different server farm than the one that you are using for Dashboard Designer.

    2. In the Value column for that parameter, type the default value that you want to use.

      Tip

      You might want to view the Excel Services workbook in its SharePoint site to see which parameter(s) you want to use.

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

Now that you have created and configured the report, you can either add it to a dashboard page or specify toolbar and interactivity settings for the report.

To specify toolbar and interactivity settings for the report

  1. Begin with the report open for editing in Dashboard Designer. In the center pane, click the Editor tab.

  2. On the ribbon, click the Edit tab, and then click Report Settings.

    The Report Settings dialog box opens.

  3. In the pane on the left side of the Report Settings dialog box, click Interactivity, and then specify the options that you want to use.

    Important

    Do not use the Typing and Formula Entry option. It does not have functionality in the current version of PerformancePoint Services in Microsoft SharePoint Server 2010.

    The Interactivity options are configured for the Excel workbook when it is published to SharePoint Server. To view detailed information about these options, see Interactivity Settings in Excel Web Access Web Part custom properties (https://go.microsoft.com/?linkid=9730256).

  4. In the pane on the left side of the Report Settings dialog box, click Toolbar, and then specify the options that you want to use.

    The Toolbar options are Excel Services options that are configured for the Excel workbook when it is published to Excel Services. To view detailed information about these options, see Toolbar and Title Bar section properties in Excel Web Access Web Part custom properties (https://go.microsoft.com/?linkid=9730257).

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

You can now add the report to a dashboard page.

Publish named items in an Excel workbook

If you want to configure an Excel Services report to display a named item that is defined in Excel, the named item must be published to SharePoint Server. Otherwise, the Item name list will be empty in Dashboard Designer.

Simply publishing the workbook does not ensure that named items are published, too.

To publish named items in an Excel workbook

  1. In Excel, 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 Web site address (URL) to the document library where you want to publish the workbook.

    The URL resembles http://<server name>/<sharepoint site name>/<document library>.

    (For example, we use the URL http://litware-sp/bicenter/Reports.)

  7. Click Save to publish the workbook.