Reporting Options for Analysis Services Cubes: MS Excel 2002 - Page 4

April 7, 2003

13.  In the Server text box, type the name of the server, as shown below.

Illustration 6: The Multidimensional Connection Dialog

In the illustration above, I supplied MOTHER (the name of my server PC) into the Server box. Optionally, the name localhost can be supplied, if Excel and the cube share the same server, according to the Microsoft documentation.

14.  Click Next.

The Multidimensional Connection Select the database ... dialog appears, asking that we select the target database / OLAP Data Source. Here we will select the FoodMart 2000 database that accompanied the Analysis Server installation, as we see below.

Illustration 7: Select the FoodMart 2000 Database

15.  Click Finish.

The Create New Data Source dialog reappears, with the new target data source indicated to the right of the Connect... button.

16.  Select the Sales cube in Box 4.

The FoodMart 2000 sample database supplies several other cubes, any of which could be selected here as a data source.

After selecting the Sales cube, the Create New Data Source dialog should resemble the illustration below.

Illustration 8: The Completed Create New Data Source Dialog

17.  Click OK.

We return to the Choose Data Source dialog.

18.  Ensuring that the Sales Cube data source remains selected, (as shown in Illustration 9 below), click OK to return to the Step 2 of 3 dialog, where we left off with the PivotTable and PivotChart Wizard.

Illustration 9: Our Sales Cube Data Source is Selected

Once we return to the Step 2 of 3 dialog, notice, as in the illustration below, that "Data fields have been retrieved" now appears to the right of the Get Data button.

Illustration 10: Indication that Data Fields have been Retrieved

  1. Click Finish.

An empty PivotTable report appears, allowing us to begin browsing the cube / designing the report immediately. In addition to the PivotTable report template, the PivotTable toolbar and the PivotTable Field List appear; the PivotTable Field List provides a selection of report building components (we discuss these in the next section). The Analysis Server is now providing the dimension and measures information to the PivotTable report directly from the cube.