Create a PivotTable report by using PerformancePoint Dashboard Designer

You can use PerformancePoint Dashboard Designer to create dashboards that contain a variety of report types, including a PivotTable report. A PivotTable report is a report view in Office Excel that enables you to organize and summarize large amounts of data. To learn more about PivotTable reports, see Overview of PivotTable and PivotChart reports.

Important: If you want to create an interactive report that uses data stored in SQL Server 2005 Analysis Services, we highly recommend that you create an analytic grid instead of a PivotTable report. Analytic charts and grids provide more functionality than you can expect to achieve by using a PivotTable report.

When you create a PivotTable report by using Dashboard Designer, you do so in two stages. During Stage I, you create the basic structure of your report. During Stage II, you configure your report to display information.

Stage I: Create the basic structure of your report

  1. In Dashboard Designer, click the Create tab, and then click Other Reports. ClickPivotTable, and then click OK. The Create a PivotTable Report wizard opens.

  2. In the Name box, type a name for your report. To specify where you want to store your report in PerformancePoint Monitoring Server, click Select Display Folder and then select or create a folder. Click Finish.

  3. In the Confirmation page, review the information in the Details pane, and then click Close. Proceed to Stage II.

Stage II: Configure your to display information

Important: Before you begin, make sure that you know the name and location of the data that you want to use in your PivotTable report. For example, if you plan to connect to a database or external data source, you need to know the name of the database and the server where it resides. Depending on how you specify your connection, you may also need to know the command text or structured query language (SQL) query that you will use to complete your connection. For more information, see "Working with Source Data" in Overview of PivotTable and PivotChart reports.

  1. In the center pane of the workspace, click the Editor tab. Then, click the Commands and Options toolbar command. The Commands and Options dialog box opens.

    The Commands and Options toolbar command enables you to connect to a data source for your PivotTable report.

  2. Click the Data Source tab. To set up your data connection, select one option in the Get data using section AND one option in the Use data from section.

    • In the Get data using section, select one of the following options:




Click Edit to open the Select Data Source dialog box. Select the data source that you want to use, and then click OK.

Data source control

Use the Data source control list to select an available item.

Tip: If there are no items in the list, then use the Connection option.

  • In the Use data from section, select one of the following options:



Data member, table, view, or cube name

Use the Data member, table, view, or cube name list to select an available item.

Command text or SQL

Specify the data that you want to use by typing your command text or a SQL query.

  1. Tip: To learn more about connecting to data sources with Office Excel, see Overview of connecting (importing) data.

  2. After you have specified the data source for your report, you add the filter fields, column fields, and row fields that you want to use for your report. Click the Add Fields toolbar command to open the list of fields that are available for your PivotTable report.

    The Add Fields box enables you to drag and drop items to display information in your PivotTable report.

    Drag the items that you want to display in your report to the appropriate sections in the center pane of the workspace. As you add items, the report automatically refreshes to display information.

  3. After you finish adding fields to your report, close the Add Fields dialog box.

  4. Click the Home tab, and then click Publish Item to save your report to PerformancePoint Monitoring Server.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!