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 10

By William Pearson

We will now create a new Dataset, based upon the MDX query used to define the Dataset of our primary report RS022_MDX_DRILLTHROUGH. In effect, we will use an identical query, with the DRILLTHROUGH statement added above the SELECT statement. Because we will be passing parameters to this report from the primary report, however, we will need to place the ultimate query within a string, which, as we have seen in other OLAP reporting articles in this series, complicates the construction of the query to some extent.

First, we will populate the Fields list with the SELECT query from the primary report. Next, we will modify the query with the DRILLTHROUGH statement, and prepare it to handle the passed parameters from the primary report upon drillthrough action.

18.  Type (or cut and paste) the following syntax into the Query pane of the Data tab:

  {[Measures].[Store Sales]} ON COLUMNS, 
  {Descendants([Product].[All Products],[Product].[Product Name],LEAVES)} ON ROWS

The new Dataset query appears as shown in Illustration 63.

Click for larger image

Illustration 63: The New Dataset Query

19.  Click the Run (!) button on the toolbar to execute the query.

The new Dataset appears in the Results pane, below the query, as partially depicted in Illustration 64.

Illustration 64: Partial View of the Returned Dataset

20.  Substitute the following for the third line (the ON ROWS specification) of the existing query.

{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good].
   [Good Light Beer]} ON ROWS

The Dataset query appears, with modifications enclosed in a red rectangle, as shown in Illustration 65.

Illustration 65: The Modified Dataset Query

21.  Click the Run (!) button on the toolbar, once again, to execute the query.

The new Dataset appears in the Results pane, below the query, as depicted in Illustration 66.

Illustration 66: Returned Dataset for the Modified Query

Our purpose is to create a query that selects a single tuple on each axis, so that we can use the DRILLTHROUGH statement in a "dummy" query, just long enough to instantiate the Data Fields for the report before we parameterize the query and convert it to a string. All we are doing here is modifying the query to select a single, qualified Product Name in the ON ROW specification. This single Product Name will be replaced by a parameter placeholder, as we shall see, in subsequent steps.

22.  Add the DRILLTHROUGH statement (just the keyword DRILLTHROUGH) to the query above SELECT, as shown in Illustration 67.

Illustration 67: Add the DRILLTHROUGH Statement above the SELECT Keyword

23.  Click the Run (!) button on the toolbar, as before, to execute the query.

The new Dataset appears in the Results pane, below the query, as partially depicted in Illustration 68.

Illustration 68: Partial Results of the DRILLTHROUGH Query

We note that the fields that appear in the new Dataset are those that we requested in our Drillthrough Options settings within the Cube Editor for the Sales cube earlier. (While order of columns is rigidly dictated by Analysis Services, this does not present an arrangement obstacle within Reporting Services, as we shall see). We have thus achieved the Drillthrough effect for a single Product Name, once again with the purpose of populating the Fields list appropriately.

24.  Click the Refresh Fields button atop the Data tab, to the immediate left of the Run (!) button, as shown in Illustration 69.

Illustration 69: Refreshing the Data Fields ...

The FoodMart_2000 Dataset Fields list is populated to reflect the returned Dataset, and thus the fields specified within the Sales cube Drillthrough Options, as depicted in Illustration 70.

Illustration 70: The Populated Fields List

We have populated the Fields list, and can next fit the query into string format. This involves removing carriage return breaks and making the query a single string. We are also ready to insert a parameterization "placeholder" for the report parameter we will create next. Finally, also within the new string, we will include the DRILLTHROUGH statement to leverage MDX to provide, as part of the newly returned Dataset, the data underlying the initial query, within the columns that we specified in Drillthrough Options from the Cube Editor earlier.

25.  Replace the query with the following exact syntax:

="DRILLTHROUGH SELECT { [Measures].[Store Sales]} ON COLUMNS, {[" & 
    Parameters!pX_ProductName.value & "]} ON ROWS FROM [Sales] WHERE 
        ([Time].[Year].[1997]) "

NOTE: Do not insert carriage returns / "line breaks." The query must remain as a single string, preceded by an equals ("=") sign and enclosed in quotation marks.

The new Dataset query appears as shown in Illustration 71.

Illustration 71: The Data Tab Dataset Query Replacement

The query refers, at this point, to a report parameter that has yet to be created; the query is therefore not yet functional. We will add this parameter (pX_ProductName) next. This report parameter will act as the mechanism that allows filtering upon the specific Product whose Store Sales value we select for drillthrough in the primary report.

26.  Select Report --> Report Parameters from the main menu, as depicted in Illustration 72.

Illustration 72: Adding a New Report Parameter

The Report Parameters dialog appears.

27.  Click the Add button on the dialog.

28.  Type the following into the Name box:


29.  Type the following into the Prompt box:

Product Name:

30.  Remove the checkmark from the Allow blank value checkbox.

31.  Ensure that, in the Available values section, the radio button to the immediate left of Non-queried is selected.

32.  Ensure that, in the Default values section, the radio button to the immediate left of None is selected.

The Report Parameters dialog appears as shown in Illustration 73.

Illustration 73: The Report Parameters Dialog for the Product Name Prompt

33.  Click OK to accept our settings and to close the Report Parameters dialog.

We return to the Data tab.

34.  Click the Preview tab to attempt to execute the report in Preview mode.

The report begins to run, and presents the prompt for the Report Parameter we have created. This serves as a "pre-test" that the modified Dataset query we have constructed is syntactically sound; recall that, because we are forced to enclose the query string within parentheses, the capability to execute the query with the standard Run (!) button is disabled.

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