MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX - Page 9
October 20, 2005
Procedure: Enable Drillthrough from the Primary Report to a Targeted Detail Report
The next steps in constructing the specified drillthrough capability include the creation of the targeted detail report that the Purchasing consumers have specified. We will create the new report itself first, and then return to the primary report to enact settings to enable and align the drillthrough action. Because the primary report must reference a report parameter within the targeted report as part of aligning the two, we will create the targeted report, with its parameter, first.
Create a New Detail Report
We will create a basic detail report, to serve as the report we target from drillthrough within our primary report, in the steps that follow:
1. Right click the Reports folder in the Solution Explorer.
2. Select Add from the context menu that appears.
3. Select Add New Item from the cascading menu that appears next, as depicted in Illustration 56.
Click for larger image
The Add New Item - RS022_MDX_DRILLTHROUGH dialog appears.
4. Click Report in the Templates pane to create a new empty report.
5. Type the following into the Name box at the bottom of the dialog:
The Add New Item - RS022_MDX_DRILLTHROUGH dialog appears as shown in Illustration 57.
6. Click Open to create the new report.
RS022_DRILLTHROUGH_TARGET.rdl appears in the Reports folder of the Solution Explorer, as depicted in Illustration 58.
We will first create the Dataset for the new report, before designing it further in Layout view.
7. Click the Data tab within the Report Designer.
8. Select <New Dataset ...> in the Dataset selector, as shown in Illustration 59.
The Data Link Properties dialog appears, defaulted to the Connection tab.
9. Click the Provider tab.
10. Within the list under Select the data you want to connect to, select Microsoft OLE DB Provider for OLAP Services 8.0, as depicted in Illustration 60.
11. Click Next to navigate to the Connection tab.
12. Type the server name into the Data Source box (mine is MOTHER1).
13. Select and complete the appropriate authentication choices for your environment, within the section labeled Enter information to log on to the server.
14. In the selector labeled Enter the initial catalog to use, select FoodMart 2000.
The Data Link Properties dialog Connection tab appears as shown in Illustration 61.
15. Click the Test Connection button, in the bottom right corner of the dialog.
Assuming that our input is correct in the settings for the Data Link Properties dialog, we will receive a message box indicating a successful connection, as depicted in Illustration 62.
16. Click OK to dismiss the message box.
17. Click OK on the Data Link Properties dialog to accept settings and close the dialog.
We are returned to the Data tab of the Report Designer, where we see the FoodMart_2000 appears in the Dataset selector.