MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist - Page 6
July 26, 2004
Create the Report File
Creating the Report File is straightforward, and handled through the following steps:
10. Right-click the Reports folder in Solutions Explorer.
11. Select Add from the context menu that appears.
12. Click Add New Item from the cascading menu, as shown in Illustration 20.
Click for larger image
The Add New Item dialog appears.
13. Click Report in the Add New Item dialog.
14. Type the following into the Name box, leaving other settings at default:
The Add New Item dialog appears, as shown in Illustration 21.
Click for larger image
15. Click the Open button at the bottom of the Add New Item dialog.
The design environment opens. We see the Data, Layout and Preview tabs appear. Our report has opened in Data View, as shown in Illustration 22.
The design environment that we see is known as Report Designer. We will construct a very basic OLAP report at this point, primarily to illustrate the use of the components that we assembled in MSAS. For more detail on the Report Designer environment, or on Reporting Services in general, see my MSSQL Server 2000 Reporting Services series at Database Journal.
Create the Data Connection
Our next step is to set up a Data Connection, from which we create a Dataset within our practice example.
1. Select New Dataset in the Dataset selector at the top of the Data tab, as depicted in Illustration 23.
As soon as we click the New Dataset selection, the Data Link Properties dialog box appears, defaulted to the Connection tab.
2. Click the Provider tab to select the appropriate provider for our data source.
3. Select Microsoft OLE DB Provider for OLAP Services on the Provider tab, as shown in Illustration 24.
4. Click Next to move to the Connection tab.
5. Type the name of the computer housing the targeted MSAS database, FoodMart 2000, in the Data Source box near the top of the Connection tab.
6. Enter the appropriate security information / settings (I am using Windows NT Integrated security for this lesson in my own environment).
7. Select FoodMart 2000 in the initial catalog box at the bottom of the tab.
8. Click the Test Connection button to verify connectivity to the data source.
We receive a message box, indicating a successful test connection, as depicted in Illustration 25.
The settings on the Connection tab of the Data Link Properties dialog should resemble those shown in Illustration 26.
9. Click OK to accept the settings we have made, and to close the Data Link Properties dialog.
Report Designer next presents us with the dataset design tool, based upon our newly connected source. We are immediately positioned to design our dataset query, which brings us to the next step.