In this article I would like to present how to integrate PerformancePoint Server 2007 (CTP 3 version) scorecards with Reporting Services in dashboards. In this step by step guide I will show how to create scorecard in Dashboard Designer and connect it with a Reporting Services report which may contain more detailed information, for example long descriptions.

Let’s assume that we analyze progress of projects in our company. Scorecard object allows us to present basic KPIs for each project and quarter of year. Additionally our users want to see detailed descriptions about every project’s stages.

So let’s start creating the solution.

The first step is to open Dashboard Designer and create Data Source object connected to the OLAP database. In this example our OLAP database is named VattenfallBSC with cube Mierniki, which contains Actual and Target values for our projects. The screenshot depicted in Figure 1 shows configured data source ds1 connected to SQL Server 2005 Analysis Services.

 

 

Figure 1. Configured Data Source to the Analysis Services database

 

The next step is to create a scorecard with KPI’s. To create scorecards we can use Scorecard Wizard (KPI’s based on Analysis Services cube) or manually create KPI’s and put them into new blank scorecard.

The following procedure shows how to create an Analysis Services scorecard, which will work with either SQL Server 2000 or SQL Server 2005.

1. On the Create tab of the Ribbon, click Scorecard.

2. In the Select a Scorecard Template dialog box, click Microsoft in the left navigation pane and then select the Analysis Services template. Click OK.

3. Specify the scorecard name and default display folder location, and then click Next. Select the Grant Read permission to all authenticated users on the PerformancePoint Scorecard server check box.

4. Select a data source (in this example ds1) by clicking one from the list, and then click Next.

5. Add new key performance indicators (KPIs) by clicking New KPI.

6. Click the down arrow in the Name text box to change the name of the KPI.

7. Click the down arrow in the Band type text box to change the Band type.

8. In the Target field , type your desired target value if other than 1.

9. When finished, click Next.

10. If you wish to import members and dimensions from your data source, select the Set members for the columns check box.

11. Click Select Dimension and select a dimension from the list provided.

12. Click Select Member and select a member from the list provided.

13. Click Finish. The wizard builds your scorecard. When you see the confirmation screen, click Close. Your completed scorecard appears in the Workspace.

The screenshot depicted in Figure 2 shows new scorecard created in our example (not exported to Sharepoint site yet).

 

 

Figure 2. Scorecard preview in Dashboard Designer

 

Now we need to prepare Reporting Services report with detail information about our projects (for example this report may contain more detailed information about project’s stages with long descriptions).

This report have to be parametrized by Project Number (report will be linked by this parameter with scorecard by using dashboard parameter).

Creating reports in Reporting Services is not described in this article.

After creating and deploying report on Reporting Services server we have to configure report in Dashboard Designer.

To Create a SQL Server Report

1. Click the Create tab in the Ribbon, and then click Other Reports.

2. In the Select a Report Template Wizard, click SQL Server Report, and then click OK.

3. Type the report name in the Name text box (required). If desired, you may name a new default display folder location in the Default display folder location text box. These folders appear in the Workspace Browser pane and are useful for organizing multiple reports.To grant read permission to all authenticated users, select the Grant read permission to all authenticated users on the PerformancePoint Scorecard server check box.

4. Click Finish. Once the Confirmation screen appears, click Close. The name of this report now appears in the Workspace Browser under Reports.

5. On the Summary tab, type the name of the server where your SQL data resides.

6. Click Browse to select your SQL Server report name. Note: This report must already exist in SQL Server.

7. Select the Show toolbar checkbox if you wish to have the SQL toolbar to display on the report web part.

8. Select the Show parameters checkbox if you wish to have the SQL report parameters included on the report to filter large set of records.

9. Select the Show docmap checkbox if you wish your report view to include a document map, a separate side pane appears next to the report.

10. In the Zoom box, select the desired viewing size of the report.

11. In the Format box, select the desired format of the report.

12. In the Section box, type the section.

13. In the DocMap ID box, type the docmap number.

14. Click Edit to adjust Report Parameters.

15. A preview of the SQL Server Reporting Services report will display in the lower pane of the Summary tab.

The screenshot depicted in Figure 3 shows configured SQL Server Report. As you can see the param1 value is the report’s parameter representing project numbers. Report is stored on http://bi2007/reportserver2 and a report server is configured to run in Native Mode (not in Sharepoint Integration Mode).

 

 

Figure 3. Configuring SQL Server Report in Dashboard Designer

 

As we’ve created scorecard and SQL Server Report now we need create a dashboard.

To Create a Dashboard

1. Click the Create tab in the Ribbon, and then click Dashboard.

2. In the Select a Dashboard Template Wizard, click the desired layout icon, and then click OK.

3. Type the Dashboard name in the Name text box (required).

4. If desired, you can designate a new folder in Default display folder location text box. These folders appear in the Workspace Browser pane and are useful for organizing multiple data sources.

5. To grant read permission to all authenticated users, select the Grant Read permission to all authenticated users on the PerformancePoint Scorecard Server check box.

6. When finished, click OK. The name of the Dashboard now appears in the Workspace Browser under Dashboards.

7. From the Details pane, use a drag and drop method to move the desired elements onto the Dashboard canvas in the workspace pane.

In our project we have to create a dashboard with three zones (3 rows). The first zone will contain Filter object. The second zone will contain a Scorecard. The very bottom zone will contain a SQL Server Report.

To link a Scorecard with SQL Server Report we need to create a dashboard Filter.

To create a Filter with Member Selection

1. After creating the Dashboard, click the Parameter tab in the workspace pane in the center of the screen.

2. Click New Parameter.

3. In the Select a Dashboard Parameter Template wizard, click the Tabular Member Selection icon, and then click OK.

4. Type a parameter name in the Name text box (required).

5. Type a description of the parameter in the Description text box (not required).

6. Select a data source for the parameter from the list displayed. Click Next.

7. Click Select Dimension. Select the desired dimension from the list displayed. Click OK. (see Figure 4)

8. Click Select Members. Select the desired Member from the list displayed. Expand folders if necessary to display all data. Click OK.

9. Choose how you want the parameter to display on the Dashboard: A list, a tree or a multi-tree format. Click Finish.

10. If the parameter is successfully created, a confirmation page will display. Click Close.

11. The newly created parameter will be listed in the Parameter workspace. It is also now available in the Details pane when you click the Summary tab of the Dashboard workspace.

 

 

Figure 4. Configuring a dashboard Filter

 

 

Figure 5. Dashboard Filters settings in Dashboard Designer

 

Now we need to link the new Filter with both scorecard and report.

1. On the newly created dashboard click the Summary tab of the Dashboard workspace.

2. Drag Filter object (created in the previous section) from the Details pane and drop it into Zone 1 on the dashboard (see Figure 6).

3. Drag the MemberUniqueName attribute of Filter object and drop into the Zone 2 (Scorecard object).

4. In Filter Link Editor set connection settings (see Figure 7).

5. Drag the Display attribute of Filter object and drop into the Zone 3 (SQL Server Report object).

6. In Filter Link Editor set connection settings (see Figure 8).

 

 

Figure 6. Configuring Zone areas in the dashboard

 

 

Figure 7. Filter Link Editor – settings for the scorecard

 

 

Figure 8. Filter Link Editor – settings for the SQL Server Report

 

 The last step is to publish all objects to the PerformancePoint Server and export the dashboard to the Sharepoint site.

1. Click the Home tab in the Ribbon, and then click Publish All.

2. Click the Export tab in the Ribbon, and then click Sharepoint

3. After configuring export settings click Finish. The preview website will display.

The screenshots depicted in Figure 9 and Figure 10 shows dashboard deployed on the Sharepoint Server site. By selecting value from Project list we can filter information in all dashboard’s components. In Figure 9 the SQL Server Report parameter is visible but of course we can hide this parameter by setting option Show Parameters on Summary tab in Dashboard Designer (see Figure 3). After selecting new value in Report list the scorecard and the report are updated simultaneously.

 

 

Figure 9. Scorecard and SQL Server Report on the Sharepoint site

 

 

Figure 10. Filtering scorecard and SQL Server Report on the Sharepoint site