Create an analytic chart or grid by using Dashboard Designer

  • Article

Applies to: SharePoint Server 2010 Enterprise

By using PerformancePoint Dashboard Designer, you can create analytic reports for your dashboards. Analytic reports are dynamic, visual representations of data that can be displayed as interactive line charts, bar charts, pie charts, and tables (which are called grids). Analytic charts and grids use data that is stored in SQL Server 2008 Analysis Services or in SQL Server 2005 Analysis Services. For more information, see Overview of PerformancePoint analytic charts and grids.

To watch a video about how to create an analytic chart, see Video: Creating an analytic chart by using PerformancePoint Dashboard Designer (https://go.microsoft.com/?linkid=9727268).

Create an analytic chart or grid

Before you begin to create your analytic chart, make sure that the data source that you want to use is available.

To verify that your data source is available

  1. In Dashboard Designer, click the Home tab, and then click Refresh.

  2. In the Workspace Browser, click Data Connections.

  3. In the center pane of the workspace, review the list of data sources on the Server and Workspace tabs.

  4. Confirm that the data source that you want to use is available. If it is not listed, then you might have to create a data connection.

When you create an analytic chart, you do this in several stages. First, you create the basic structure of your report. Then, you configure the report to display data.

To create the basic structure of your report

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

  2. In the ribbon, click the Create tab.

  3. In the ribbon, in the Reports group, click Analytic Chart or Analytic Grid.

    The Create an Analytic Chart Report (or Create an Analytic Grid) wizard opens.

  4. In the Select a Data Source window, select the SQL Server Analysis Services data source that you want to use.

  5. Click Finish.

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

  6. In the center pane, click the Properties tab.

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

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

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

After you have created the basic structure of your report, configure it to display information. When you configure the report to display information, you can select from two methods.

You can use the Design tab to drag items from the Details pane and preview your report. As you configure the report, it automatically updates in the center pane so you can see how it will appear in the dashboard.

You can use the Query tab to specify or edit the custom Multidimensional Expressions (MDX query) that is used to display information.

Tip

If you create an analytic chart by using the Query tab (and not the Design tab), dashboard users might be unable to drill up or down into the data.

To configure the report to display information by using the Design tab (recommended)

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

  2. In the Details pane, expand Measures, Dimensions, or Named Sets to display the list of items that are available in each category.

  3. Drag an item to the Series (or Rows), Bottom Axis (or Columns), or Background section.

    Tip

    To display data in your analytic report, you must put at least one item in each Series (or Rows) and Bottom Axis (or Columns) section.
    Placing an item in the Background section is optional.
    You can use many combinations of measures, dimensions, and named sets. For example, you can place a named set in one section and a measure in another section. Or you can put one dimension hierarchy in one section and a different dimension hierarchy in another. You can also put measures in the Background section, or you can leave the Background section blank.

    • The Series section corresponds to the vertical axis of your chart, and the Bottom Axis section corresponds to the horizontal axis.

    • The Rows section corresponds to the horizontal rows of your grid, and the Columns section corresponds to the vertical columns.

    • Any items that you put in the Background section (this is optional) serve as hidden filters for your analytic chart or grid. For example, if you put a Geography dimension in the Background section and then select some, but not all, of the geographical regions in the hierarchy, then the information that is displayed in your analytic report will be limited to those geographical regions that you selected.

      Note

      If you put a dimension in the Background section and select the All option in the list of members, make sure that you do not select individual dimension members, too. Otherwise, you might receive an error message. The error message indicates that there was an error running the data source query.

  4. For each dimension that is located on your chart, you can either keep the default selection (this is typically All), or you can select the dimension members that you want to display.

    To select individual dimension members

    1. Right-click a dimension, and then click Select Members. Or click the down arrow next to the dimension name, and then click Select Members.

      The Select Members dialog box opens.

    2. Use the expandable tree control to view the list of members that are in the dimension hierarchy.

    3. Select the items that you want to display in your analytic report.

    4. Click OK.

      The Select Members dialog box closes and your analytic report updates to display the members that you selected.

  5. Repeat steps 2-4 for each item that you want to display in your chart.

  6. In the ribbon, use the toolbar commands in the View group of the Edit tab to specify the view type and settings for your analytic report.

    Important

    If you are creating an analytic grid that uses a PowerPivot data connection, make sure that you use the tabular layout option instead of the compact layout. Otherwise, dashboard users might get error messages when they attempt to use Value Filters on the report.
    To apply a tabular layout to the grid, on the Edit tab, in the View group, use the Layout menu to select Tabular Layout.

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

To configure the report to display information by using the Query tab (requires familiarity with MDX)

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

  2. In the MDX pane, specify the MDX query that you want to use for your analytic report.

    For example, if you want to show sales information for different products across different sales territories, your MDX query might resemble the following example.

        SELECT
    HIERARCHIZE( { [Product].[Product].[Product Category Name].&[8], [Product].[Product].[Product Category Name].&[7], [Product].[Product].[Product Category Name].&[6], [Product].[Product].[Product Category Name].&[5], [Product].[Product].[Product Category Name].&[4], [Product].[Product].[Product Category Name].&[3], [Product].[Product].[Product Category Name].&[2], [Product].[Product].[Product Category Name].&[1] } )
    ON COLUMNS,

    HIERARCHIZE( { [Sales Territory].[Territory Hierarchy].[Sales Territory Group].&[North America], [Sales Territory].[Territory Hierarchy].[Sales Territory Group].&[Europe], [Sales Territory].[Territory Hierarchy].[Sales Territory Group].&[Asia] } )
    ON ROWS

    FROM [Sales]

    WHERE ( [Measures].[Sales Amount] )

    The actual query that you use depends on the data cube and the kinds of information that you want to do display.

  3. (This is optional.) If you want to connect dashboard filters to the analytic report, you must configure the report to receive filters.

    Important

    The following procedure applies only to analytic charts or grids that were created by using a custom MDX query.

    1. In the center pane, in the MDX pane, select a code example that represents a dimension member, dimension hierarchy, or a named set.

      Depending on the kind of filter that you want to use for the analytic chart or grid, the code example that you select will vary.

      For example, you might have an analytic chart that uses an MDX query that includes the expression, [Geography].[Geography].[Region]. That code example represents a dimension hierarchy in the Geography dimension.

      To configure the chart to receive a geography filter, select the complete code example [Geography].[Geography].[Region]

    2. In the Parameters section, type a name for your parameter, and then click Insert.

      The parameter name appears in the MDX pane.

      For example, if you insert a parameter called Geo into the code example, [Geography].[Geography].[Region], the MDX pane displays <<Geo>> instead of that code example.

      You can now link a dashboard filter to report by using this parameter. Then, when a dashboard user selects an item in the filter that is linked to this report, <<Geo>> is replaced with that item in the query. The analytic report displays the results.

    3. To add more than one parameter, repeat Steps 3a and 3b.

      For example, consider an analytic chart that has the following code, which includes two parameters.

          SELECT {[Time].[Calendar].[Year].&[2006]} ON COLUMNS, { TOPCOUNT({DESCENDANTS(<<Product>>, [Product].[Product].[Product])}, 20, ([Time].[Calendar].[Year].&[2009], [Measures].[Sales Amt]))} ON ROWS FROM [Sales] WHERE (<<Geography>>, [Measures].[Sales Amt])

      This query enables you to link two kinds of filters to the analytic report: a Products filter and a Geography filter.

      Based on this MDX query, a dashboard user who selects a product category in the Products filter, and then geographical region in the Geography filter, will see the top 20 products (in descending order) for that geographical region in the year 2009.

  4. In the center pane, click the Design tab to preview your analytic chart or grid.

  5. In the ribbon, use the toolbar commands in the View group of the Edit tab to specify the view type and settings for your analytic report.

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

Edit an analytic chart or grid

After you have created your analytic report, you can configure it to adjust how you want the information to be displayed. For example, you can configure the information that is displayed. Or you can change the view type of a report, such as by changing a bar chart to a line chart, or a chart to a grid.

When you want to configure the information that is displayed in an analytic report, you can use one of two methods:

  • You can use the Design tab in the center pane of the workspace to add or remove the dimensions, measures, and named sets that you use in the report. You also use the Design tab to preview the analytic report.

  • Use the Query tab in the center pane of the workspace to change the MDX query that is used to display information in the report.

To use the Design tab to add, remove, or change the items that appear in a report

  1. Begin with an analytic report open for editing in Dashboard Designer. In the center pane of the workspace, click the Design tab.

  2. Make one or more of the following changes to your analytic chart or grid:

    Action Procedure

    Add an item to an analytic chart or grid

    1. In the Details pane, expand Measures, Dimensions, or Named Sets to display the list of items that are available in that category.

    2. From the Details pane, drag an item to the Series (or Rows), Bottom Axis (or Columns), or Background section.

    3. Repeat for each item that you want to display in your chart.

    Remove an item from a chart or grid

    In the Series (or Rows), Bottom Axis (or Columns), or Background section, right-click the item that you want to remove, and then click Remove.

    Move an item to a different location in the chart or grid

    In the Series (or Rows), Bottom Axis (or Columns), or Background section, right-click the item that you want to move, and then click the section where you want to place the item.

    Or:

    Drag the item that you want to move to a different section.

    Display different dimension members in the chart or grid

    1. Right-click a dimension, and then click Select Members. The Select Members dialog box opens.

    2. Use the expandable tree control to view the list of dimension members. Select the items that you want to display in your analytic chart, and then click OK.

      The Select Members dialog box closes and your analytic chart or grid updates to display the members that you selected.

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

To use the Query tab to add, remove, or configure the items that appear in a report

  1. Begin with an analytic report open for editing in Dashboard Designer. In the center pane of the workspace, click the Query tab.

  2. In the MDX pane, edit the MDX query that appears. Or, type a new MDX query.

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

See Also

Concepts

Overview of PerformancePoint analytic charts and grids
Create a PowerPivot data connection (PerformancePoint Services)