Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 20, 2005

MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX - Page 9

By William Pearson

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

Illustration 56: Adding a New Detail Report to the Project

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.

Illustration 57: The Add New Item - RS022_MDX_DRILLTHROUGH Dialog with Our Input

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.

Illustration 58: The New Report Appears in the Reports Folder - Solution Explorer

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.

Illustration 59: Select <New Dataset ...>

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.

Illustration 60: Select Microsoft OLE DB Provider for OLAP Services 8.0

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.

Illustration 61: The Data Link Properties Dialog – Connection Tab

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.

Illustration 62: Testing Positive for Connectivity via the Test Connection Button ...

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM