Modify the FoodMart Sales Report

In this section, we will copy the existing FoodMart Sales report, and then open it in Reporting Services' Report Designer, where we will begin the modifications that the information consumers have requested.

Set up a Data Connection for the FoodMart Sales Report

Our next step is to set up a Data Connection. As we have noted numerous times throughout our series, Reporting Services can connect with, and create the DataSets it needs from, virtually any ODBC or OLE DB-compliant data source (in addition to the obvious MSSQL Server and MSAS data stores). .NET-based API's add the potential for other data sources, assuming that you have a legacy, or otherwise "alternative," scenario on your hands.

Let's set up a Connection to support the DataSet within our practice example.

1.  Right-click the Shared Data Sources folder underneath the new RS011 project tree in the Solution Explorer.

2.  Select Add New Data Source from the context menu that appears, as depicted in Illustration 7.

Illustration 7: Select Add New Data Source from the Context Menu

The Data Link Properties dialog appears, defaulted to the Connection tab.

3.  Click the Provider tab, to select it.

4.  Select Microsoft OLE DB Provider for OLAP Services 8.0 from the Provider list box, as shown in Illustration 8.

Illustration 8: Selecting Microsoft OLE DB Provider for OLAP Services 8.0 - Providers Tab

5.  Click Next, to move to the Connection tab.

6.  In the Data Source box, type the name of the server upon which MSAS resides (mine is MOTHER1 in the illustration that follows).

7.  Type in the server name again in the Location box.

8.  Provide the appropriate authentication information to access the MSAS environment on the server you have selected.

I am using Windows NT Integrated Security, as this is not a production environment. For a production environment, selections here require careful consideration.

9.  Select FoodMart 2000 in the list of data sources that appear when we click the down-arrow selector button at the next box (and thus receive confirmation that our server / authentication information is adequate to display the sources) at the top of the Data tab).

The completed Data Link Properties - Connection tab appears as depicted in Illustration 9.

Illustration 9: Data Link Properties - Connection Tab

10.  Click the Test Connection button to confirm connectivity.

The Microsoft Data Link message box appears, indicating a successful test, as shown in Illustration 10.

Illustration 10: Testing Positive for Connectivity ...

11.  Click OK to close the message box.

12.  Click OK to accept the settings we have made, and to close the Data Link Properties dialog.

Having established the data source, we are now ready to create a copy of the FoodMart Sales report, which we will then modify to meet the expressed requirements of the information consumers.