Design an Analytic Grid Report using PerformancePoint Services (SharePoint 2010)

In this article we'll show you how to create a simple Analytic Grid Report using PerformancePoint Services for SharePoint 2010. This article assumes you have the following:

  1. Implemented SharePoint Server 2010 Enterprise
  2. Experience working with SharePoint 2010.
  3. Have created a Business Intelligence site in SharePoint 2010.
  4. Exposure to PerformancePoint Dashboard Designer.
  5. General know-how of report development.
  6. Have installed Adventure Works 2008 R2 Data Warehouse sample database and AdventureWorks 2008R2 Analysis Services Project. You can find them http://msftdbprodsamples.codeplex.com/

 Steps

  1. Run PerformancePoint Dashboard Designer
  2. Creating a Data Connection
  3. Referencing a Time Dimension
  4. Create a Filter(s)
    1. Member Selection
    2. Time Intelligence
  5. Create Report - Analytic Grid
  6. Create Dashboard
  7. Attach Filters
  8. Deploy Dashboard
  9. Run Report in SharePoint 2010 Business Intelligence Center

 

Note: An Analytic Grid can only use an Analysis Services data source.

Run PerformancePoint Dashboard Designer

1. If you already have installed the Dashboard Designer, you can navigate to Start >> All Programs >> SharePoint >> PerformancePoint Dashboard Designer; if not, simply follow the steps below:

  1. Navigate to your SharePoint Business Intelligence Center site. For example, https://sharepoint/bi
  2. From the Quicklaunch, click on PerformancePoint Content link. You should see something similar to below.

3. When the PerformancePoint Content page renders, click on + Add New Item from the middle pane. This will install (if you don't already have it) and/or open PerformancePoint Dashboard Designer.

Configure SharePoint URL

Note: This step may have already been configured for you. It's a good idea to review the configuration to familiarize yourself with Server Settings for PerformancePoint Dashboard Designer.

1. Click the upper left button of the Dashboard Designer window.

2. Click the Designer Options button.


3. Click the Server tab.

4. Change the SharePoint URL value to your desired location, if it's not already included.

 

Create New Data Source

1. Right click on Data Connections and select New Data Source.

2. The Select a Data Source Template dialog will prompt you for a Category and Template. From the Template pane, select Analysis Services and click OK.

3. The Connection Setting pane renders. Click the Editor tab and select the Use Standard Connection option.

4. Type the name of your database server name in the Server: textbox. For example, MyDBServer

5. Below the Server textbox, select the "Adventure Works DW 2008R2" from the Database dropdown list. This is an Analysis Services (AS) database. Note: your name of the AS database may differ.

6. Leave Roles textbox empty.

7. Select the 'Adventure Works' item from the Cube dropdown list. You may keep the default settings in the Data Source Settings pane. So far, your configuration should look similar to below.

8. Click the Properties tab. Give the data source a name. For example, "AdventureWorksDW2008R2".

9. Click the Time tab. Here, we will configure our data source to work with Time Intelligence by specifying values that determine how time will be interpreted for our data source.

10. Select Date.Date.Fiscal from the Time Dimension dropdown list.

11. Under Reference Member section, select Browse...  button and choose November 1, 2010.

 

12. Under the Hierarchy level section, select Day. The Reference Date section should automatically choose the present day - keep this default value.

 

 13. Your final Time configuration should look similar to below.

14. From the Workspace Browser pane, right click the AdventureWorksDW2008R2 data source and click Save.

 Create New Filters 

         We will be creating two Filters for this demo - Date and Sales Territory. The Date filter will allow us to capture yeartodate, quarter, current year and the previous 1-4 years. Let's begin.

 

Date Filter

1. From within the Workspace Browser, right click PerformancePoint Content folder and select New >> Filter from the list. This will open the Select Filter Template window.

 

2. From within the Select Filter Template window, select the Time Intelligence template item and click OK.


3. The Create Filter window renders and you should see the Select a data source page. Click Add Data Source from the available menu items.

4. You should see your previously create data source under the Workspace tab. Double click on the AdventureWorksDW2008R2 data source item from the list. This will add the data source the Select a data source page and place a check in the box right of the AdventureWorksDW2008R2. Click Next.



5. The next page of the wizard is Enter time formula. Add the following formulas, as presented in the image below. After you have completed adding the formulas, click the Preview button to valdate the member values exist. Click Close after previewing the members.

 

6. Click Next.

7. From the Select Display Method page, select Multi-Select Tree style from the list and click Finish to complete the Date Filter configuration.

8. Select the Properties tab and name the Filter Date.

9. For the Display Folder textbox, enter Retail\Filter.

10. Right click the Date filter from the Workspace Browser and click Save

 

Sales Territory Filter

1. Repeat steps 1-4 from above (Date Filter), but for step 2. select the Member Selection template.

2. After completing step 4. and clicking Next, you will navigate to the Select Member page. Here, you will configure which Filter Dimension, Members and Filter Measure to use. Make the following configurations:

Filter Dimension = Sales Territory.Sales Territory

Filter Members = All Sales Territories, All descendants of "All Sales Territories"

Filter Measure = Default Measure (Reseller Sales Amount)

Note: The default measure may differ from your Cube configuration.

3. Click Next to navigate to the Select Display Method page.

4. From the Select Display Method, select the Multi-Select Tree style item.

5. Click Finish to close the window.

6. Click the Properties tab and and name the Filter  Sales Territory.

7. For the Display Folder textbox, enter Retail\Filter.

8. Right click the Sales Territory filter from the Workspace Browser and click Save

Your should see the following Filters under the Workspace Browser.

 

Create Report - Analytic Grid

This report will present Internet Sales Summary (i.e., Sales Amount, Gross Profit, Gross Profit Margin, Average Sales Amount and Order Quantity) by Product Categories hierarchy and Fiscal Year dimensions. We will include background filtering for Date and Sales Territory.

1. From within the Workspace Browser, right click the Retail folder and select New >> Report.

 

2.The Select a Report Template window renders. Choose the Analytic Grid template from the list and click OK.

3. The Select a Data Source page renders - Make sure the Workspace tab is selected; choose the AdventureWorksDW2008R2 data source from the list and click Finish. The Analytic Grid design area will render.

You will notice three different sections on the bottom that allow you to drag items to Rows, Columns (bottom axis) and Background (optional). The Background serves as a hidden filter. You may add an item to the Background as either a static filter, or attach a custom Filter to a Background item when you define your Dashboard. Note: we will perform the latter for this demo.

The far right pane Details provides you with existing Measures, Dimensions and Named Sets available to drag to the Rows, Columns and/or Background sections.

4. Expand the Measures node and drag the following items to the Columns section. Collapse the Measures node when you have finished adding the items below.

1. Internet Sales Amount

2. Gross Profit

3. Gross Profit Margin

4. Average Sales Amount

5. Order Quantity

5. Expand the Dimensions >> Product node and drag the following item to the Rows section. Collapse the Dimensions node when you have finished adding the items below.

1. Categories

6. Expand the Date node and drag the following item to the Background section. Collapse the Date node when you have finished adding the items below.

1. Date

7. Expand the Sales Territory node and drag the following item the Background section. Collapse the Sales Territory node when you have finished adding the items below.

1. Sales Territory


Try expanding the All Products node under the Product Categories column of the Analytic Grid. You should see something similar to below.

8. Click the Properties tab and name the report "Internet Sales Summary".

9. Rename the Display Folder to "Retail\Reports".

10. Right click the report from under the Workspace Browser and seclect Save.

 

Create Dashboard

We will now design our Dashboard to include the Filter's and Analytic Grid report we created earlier in this demo. After completing the design, we will save/deploy to SharePoint Business Intelligence Center and test the results.

1. Right the Retail folder and select New >> Dashboard.

2. The Select a Dashboard Page Template window will render. Choose the 2 Rows template from the list and click OK.

 

3. The Dashboard designer view renders. This demo is using a two zones - one header (filters) and one detail (analytic grid). From within the Pages section, change the Page 1 name to "Internet Sales".

4. From within the Details pane, expand the Filters >> PerformancePoint Content >> Retail >> Filter nodes.

5. Drag both Date and Sales Territory filters to the Top Row zone. Collapase the Filters node when you have completed this step.

6. Expand the Reports >> PerformancePoint Content >> Retail >> Reports nodes.

7. Drag the Internet Sales Summary report to the Bottom Row zone.

Your Dashboard designer should look similar to below.


8. From within the Date filter, drag teh Member Unique Name item to the Drop Fields to Create Connections section of the Internet Sales Summary report.

You should see the following Connection window. Accept the default setting and click OK.

 

9. From within the Sales Territory filter, drag teh Member Unique Name item to the Drop Fields to Create Connections section of the Internet Sales Summary report.

You should see the following Connection window. Accept the default setting and click OK.

 

10. Click the Properties tab.

11. Change the Name value to Sales Dashboard.

12. Changte the Display Folder value to Retail\Dashboard.

13. Cick Save icon from the upper left corner of the Dashboard Designer Ribbon.

14. From within the Workspace Browser pane, right click the Sales Dashboard item and select Deploy to SharePoint...

15. The Deploy window renders. Click the Dashboard folder. Keep the default value of v4 under the Master Page dropdown.

16. Check the Include page list for navigation. If you plan to have a dashboard consist of more than one page, select the Include page list for navigation. This will add a simple hyperlink navigation to the master dashboard, while deployed in SharePoint. 

17. Click OK. Deployment process begins... You will now be redirected to SharePoint Business Intelligence Center - Internet Sales. You should see something simliar.

18. Click the Date filter. Uncheck the Year to Date and check the Last Year item.

19. Click the Sales Territory filter and check Europe.


You have completed a simple Dashboard! Go ahead an configure your Analytic Grid to include the Sales Territory Region dimension in the Rows section of the report. Feel free to modify the dashboard by including addtional dimensions and/or measures.

 

Cheers!

 

Add comment