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 Feb 26, 2008

Support Parameterization from Analysis Services - Page 3

By William Pearson

Preparation: Modify the OLAP Report for Use within Our Practice Session

We will next make a few modifications to prepare the report for our practice session. Our objective will be to begin the session with a simple OLAP report that contains basic parameterized filters as required by our client colleagues (just as we did in preparing the report to support its own parameters within the articles we cited earlier). This time, however, we will only be creating the parameters within Reporting Services to serve as skeletal placeholders from which we will reference support objects that we will add to our Analysis Services data source in the respective procedural section below. Some of the support structure that is automatically created within Reporting Services as part of this process will be discarded / ignored. While there are other ways of approaching the creation of the parameters in Reporting Services, we are simply taking a short cut to finalize preparation and get to our focus subject matter, the creation and alignment of the parameter support objects in Analysis Services, more rapidly.

Let’s open the report (for those of us not already there) and make the necessary settings to place it into a state from which we can commence our “from scratch” practice steps.

1.  Right-click DBJ_OLAP_Report.rdl (or your own choice of a similar report) in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 5, as necessary.

Illustration 5: Opening the New Report ...

We will begin by adding additional parameters to our sample report. These parameters will initially be of a standard, basic picklist variety. We will then work with these parameters to align them with support objects we later construct within the report’s underlying Analysis Services data source.

Add Additional Parameters through the Query Builder Interface and Review the Automatic Creation of Support Objects

As we stated in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, and elsewhere within this, and other of my, series’, the Design Mode button in the toolbar of the Data tab allows us to easily shift between Design view and Query view, when working with our OLAP queries. The idea, obviously, is to provide those who are not at ease with direct MDX a means of creating queries within a graphical interface.

We have begun with a basic report with core datasets, and are ready to add three rudimentary, date-related prompts that will meet our objectives. We will create these prompts within Design view, by taking the following steps:

1.  Click the Data tab.

We enter the Data view, which defaults to the presentation of the first of two pre-existing datasets, ProductData. To state what is probably obvious, we can tell that we are in Design view, primarily because we see the column headings for our query components in the Data grid, and we do not see MDX syntax. We also see that the Design Mode button is depressed, as depicted in Illustration 6.

Illustration 6: MDX Query Builder in Design Mode (Compressed View)

We have accessed the Data tab to add three date-related filters (each of which we shall parameterize): one each for Calendar Year, Calendar Quarter and Calendar Month. Let’s first run the existing query to ascertain that all is in order with regard to the retrieval of data.

2.  Click the Execute Query button atop the Data tab, as shown in Illustration 7.

Illustration 7: Click Execute Query to Retrieve Data with the Existing Query ...

Data populates the Data pane, and appears similar to that depicted in Illustration 8.

Illustration 8: Initial Query Operates Successfully in a Quick Test

Having ascertained that the core query is in working order, we will now add the date-related filters, as well as the “vanilla” parameters that we intend for them to support.

3.  In the Metadata pane, expand the Date dimension, as required.

4.  Within the expanded Date dimension, expand the Calendar folder.

5.  Right-click the Date.Calendar Year attribute hierarchy that appears within the expanded Calendar folder.

6.  Select Add to Filter within the context menu that appears, as shown in Illustration 9.

Illustration 9: Select Add to Filter in the Context Menu

The Date dimension populates the Dimension column, within a newly created row of the Filter pane. The Calendar Year attribute hierarchy appears within the Hierarchy column, as well.

7.  Leave the Operator setting (to the immediate right of the Hierarchy column) at its default of “Equal.”

8.  Place a checkmark in the checkbox that appears in the Parameters setting (to the immediate right of the Filter Expression setting), the rightmost column in the new row of the Filter pane.

Our completed filter-with-parameter entry appears in the new row of the Filter pane, as depicted in Illustration 10.

Illustration 10: The Filter Pane Reflects Our Addition of the Calendar Year Attribute Hierarchy

We have successfully added a new parameter, in conjunction with a filter, within the primary dataset underlying our report. The addition of the parameter within the graphical interface, as we have seen in previous articles, has already begun a sequence of events that will trigger the automatic creation of a corresponding Report Parameter. A supporting dataset will also be created, as soon as we move to the Layout tab. But first, we need to add entries within the Filter pane for the additional Date filters we intend to add.

9.  Click the leftmost box (which contains the placeholder <Select dimension>, as before), within the Dimension column, in the bottom row of the Filter pane (this time, beneath the box containing Date, which we added in creating the first entry in the Filter pane earlier).

The dropdown selector is again enabled.

10.  Select Date from the list that appears in the selector.

11.  Select Date.Calendar Quarter of Year within the Hierarchy box to the immediate right, using the selector that is built in.

12.  Leave the Operator setting (to the immediate right of the Hierarchy setting) at its default of “Equal” (checking the box will make it appear), as we did within the first entry.

13.  Place a checkmark in the checkbox that appears in the Parameters setting, once again.

We will now create the final parameterized filter within the Filter pane.

14.  In the bottom row of the Filter pane, click the leftmost box within the Dimension column, once again.

15.  Select Date from the list that appears in the selector, as we did before.

16.  Select Date.Month of Year within the Hierarchy box to the immediate right, using the selector that is built in.

17.  Leave the Operator setting (to the immediate right of the Hierarchy setting) at its default of “Equal,” as we did within the first entry.

18.  Place a checkmark in the checkbox that appears in the Parameters column, as we have done with the previous row entries we have made in the Filter pane.

The Filter pane, with all four entries, appears as shown in Illustration 11.

Illustration 11: The Four Parameterized Filters in the Filter Pane

19.  Select Report ->Report Parameters ... from the main menu.

The Report Parameters dialog opens, this time revealing four Report Parameters, as depicted in Illustration 12.

Illustration 12: The Four Report Parameters, including the Three New Date Parameters

We will make adjustments to each of the new Report Parameter entries in later steps, where it will be most efficient to accomplish all at one time.

20.  Click OK to dismiss the Report Parameters dialog.

21.  Click the Layout tab to transit temporarily away from the Data tab.

22.  Click the Data tab, once more, to return immediately.

23.  Click the downward selector arrow on the right side of the Dataset selector.

Based upon our discussions in earlier articles, we see the behavior we have expected. All five Datasets, including the originally existing two Datasets and the three we have added via the enactment of parameterized filters within the Filter pane (and subsequent departure from the Data tab), now appear, as shown in Illustration 13.

Illustration 13: All Datasets Appear as Expected ...

We have successfully added three rudimentary parameters, based upon filters we added within the Filter pane of the primary Dataset underlying our report. The addition of the parameters within the graphical interface, as we have verified, triggered the automatic creation of corresponding Report Parameters. Supporting datasets were also created, as soon as we moved away from the Data tab. The timing of the automatic creation of objects can thus be understood; the sequencing becomes very important in the event that we need to modify or remove any of the objects after they are initially created and aligned.

Because we will be referencing parameter support objects outside Reporting Services, which we will create later in Analysis Services, we will not need the automatically created parameter support datasets. We will remove these later – we can thus reference their structures, in the meantime, which are already aligned to the existing Report Parameters, as a part of quick construction of the Analysis Services replacements.

24.  Leave the report open at the Data tab for later steps.

We are now ready to transit to the Analysis Services Cube Designer to add parameter support to the Adventure Works cube.

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