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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 16, 2007

Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II - Page 6

By William Pearson

Procedure: Adding Cascading Parameters in Reporting Services 2005

As we noted earlier, our intent within this article is to begin our examination of Cascading Parameters by taking the simplest approach: we will add Parameters using the Query Builder interface in Design Mode. We will parameterize hierarchical levels of the Date dimension, so as to be able to easily verify, with no ambiguity, that the resulting cascading parameters work effectively – that they present the appropriate child members based upon the selection made in the parameter preceding the level to which they belong. Moreover, we will examine the internal processes that take place “behind the scenes.”

In other articles of this series, we add Parameters via more manual, less “visually direct” channels (some of the steps of which will take place within the Query view versus the Design view), for various reasons that become apparent within the scenarios we encounter there. We compare and contrast methods throughout the related articles, where relevant, introducing, within each, new concepts upon which we can build more sophisticated parameterization support into our organizations’ reports.

Add Cascading Parameters through the Query Builder Interface

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 produced the core dataset, and are ready to build the cascading, date-related prompts that will meet our objectives. We will create our rudimentary cascading parameter chain within Design view, by taking the following steps:

1. Click the Data tab.

We enter the Data view, as we did earlier. 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 shown in Illustration 36.


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

We have returned to add three “chained” filters (which we will parameterize): one each for Calendar Year, Calendar Quarter and Calendar Month.

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

3. Expand the Date Calendar hierarchy, once again.

While, as we shall see, we might simply drag the hierarchical levels that we need to the Filter pane, it is, in my opinion, easier to work directly with the pane selectors. (We have expanded the Date.Calendar hierarchy more as a visual reference, in this case.)

4. Click the leftmost box (which contains the placeholder <Select dimension>), within the Dimension column, in the single existing row of the Filter pane, to enable its dropdown selector.

5. Select Date from the list that appears in the selector, as depicted in Illustration 37.


Illustration 37: Adding the First Date Filter ...

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

7. Leave the Operator setting (to the immediate right of the Hierarchy setting) at its default of “Equal,” which appears when we click the box.

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 top row of the Filter pane.

Our completed Filter-with-Parameter entry appears in the top row of the Filter pane, as shown in Illustration 38.


Illustration 38: Filter Pane of the MDX Query Builder - Settings for First Parameterized Filter

We have successfully added a new Parameter, in conjunction with a filter, within the Dataset underlying our report. The addition of the Parameter within the graphical interface, as we shall see, has already triggered the automatic creation of a 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 filters within the chain.

NOTE: As we have noted in other articles, and as we will reiterate multiple times, it is important to remember that, although the Report Parameter and its underlying Dataset are created automatically when we enable parameterization within the Filter pane of the MDX Query Builder (Design Mode), the removal of the “Parameter” checkmark within the associated row of the Filter pane, or even the deletion of the entire corresponding filter row, will not produce an opposite effect. The Report Parameter and the Dataset will remain until they are manually removed. Moreover, the disablement of parameterization within the Filter pane, followed by re-enablement and / or recreation of the Filter pane entry will, unless we intervene before we re-enable / recreate the Parameter, result in the creation of two Report Parameters.

9. Click the Layout tab to transit to Layout view.

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

We recall that we previously removed all Report Parameters, as part of our preparation for the practice exercise. Yet we note, within the Report Parameters dialog that has appeared, the presence of a new Report Parameter, called DateCalendarYear. This Report Parameter was created automatically when we designated our new row within the Filter pane as a Parameter (via the enabling checkbox).

Another important observation we might make is that the From query setting within the Available values section of the dialog is active (the radio button is selected), and that the setting references a Dataset (and the Value and Label fields therein) with the same name as the new Report Parameter - a Dataset, we will discover, that has also been automatically created. The purpose of this Dataset is to support the selection picklist for the new Report Parameter.

Finally, we note that a couple of settings have been set for us: in the Prompt section (within the upper third of the dialog) of the Report Parameters dialog, the Multi-value setting is checked. While we might certainly leave the setting in place, we will disable it for the purposes of our practice session.

In addition, in the Default values section (in the lower third of the dialog), we find that the radio button for Null has been preselected. We will leave the latter preselected setting (“Null”) in place; the disablement of the Allow null value setting in the Prompt section effectively means that, although the parameter selector defaults to “blank” at runtime, the information consumer will be forced to make a selection before he / she can proceed further with executing the report. This arrangement works well in many environments, particularly where default settings tend to be overlooked, at times, and the report run with unintended constraints - obviously settings should be adjusted to accommodate local requirements and realities.

11.  Replace the default string that appears within the Prompt box, Date.Calendar Year, with the following, more user-friendly label:

Calendar Year:

12.  Uncheck the checkbox labeled Multi-value to disable it.

The Report Parameters dialog appears, with our adjustments, as depicted in Illustration 39.


Illustration 39: Settings for the Automatically Created Report Parameter

13.  Click OK to accept settings, and to dismiss the Report Parameters dialog.

14.  Click the Preview tab next, to execute the report.

The report runs, and then displays a drop-down parameter prompt labeled Calendar Year, the selector box of which, by default, contains the placeholder <Select a Value>, as shown in Illustration 40.


Illustration 40: The New Runtime Parameter Appears ...

15.  Click the downward arrow to the right of the Calendar Year parameter selector to expose the picklist of Year choices.

16.  Select CY 2003.

17.  Click the View Report button in the upper right corner of the Preview tab.

The report runs again, and returns for the Date level groupings, for Calendar Year 2003, as expected.

We will now return to the Data tab, to examine the Dataset which has been automatically created, to populate the picklist in support of the new Report Parameter.

18.  Click the Data tab once again.

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

20.  Select the new DateCalendarYear Dataset that appears, as depicted in Illustration 41.


Illustration 41: Select the New Dataset ...

The Dataset loads, and presents the MDX syntax in Query view. This is the query that has been automatically created to generate the picklist to support the new DateCalendarYear Report Parameter (we saw the DateCalendarYear Dataset referenced within the Dataset selector, where columns from the new Dataset were assigned to the Value field and Label field selectors, within the Available values section of the dialog. As we have noted, the Dataset was created automatically, after the Report Parameter was itself created, and connected to its underlying Dataset via the three settings we described, which were also set automatically by Reporting Services.

As we noted in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, the automatically MDX generated query creates additional fields, in addition to retrieving data from the cube, whose sole purpose is support of the parameter picklist, from which information consumers make the selections at runtime. These fields are:

  • ParameterCaption
  • ParameterValue
  • ParameterLevel

All three fields are products of calculated members created via MDX syntax (using the WITH MEMBER keyword combination). As we discover within other articles of the series, there are multiple ways to approach picklist support (I very often construct members such as the above at the Analysis Services level, to afford central maintenance and reusability, among other, perhaps less obvious, advantages). This is simply the mechanism through which Reporting Services accomplishes support of the automatically generated Report Parameter. As we saw earlier, the Value and Label settings within the Report Parameter dialog reference the ParameterValue and ParameterCaption fields, respectively, within this Dataset.

NOTE: While we will encounter and discuss MDX functions, expressions, queries, and related subject matter throughout the MSSQL Reporting Services series, please refer to the articles of my MDX Essentials series, whose member articles are published monthly here at Database Journal, for detailed information about MDX.

Having examined various details about the objects that are created to support a parameterized filter, we will return to the Data tab to create the remaining two members of the cascading parameters chain.

21.  Using the dropdown Dataset selector, as before, select ProductData, to return to the primary dataset and Filter pane.

22.  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.

23.  Select Date from the list that appears in the selector, once again.

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

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

We will take a small detour, at this point, to confirm our understanding as to the timing of Report Parameters creation, based upon our activities within the Filter pane.

26.  Leaving settings in the second row of the Filter pane, select Report ->Report Parameters ... from the main menu, once again.

The Report Parameters dialog opens, revealing only the single Report Parameter that we saw earlier, DateCalendarYear.

27.  Click OK to close the Report Parameters dialog.

28.  Returning to the second row of the Filter pane, where we left off with our settings to establish a second parameterized filter, place a checkmark in the checkbox that appears in the Parameters setting, once again.

29.  Leaving the Filter pane once again, select Report ->Report Parameters ... from the main menu.

The Report Parameter dialog opens, this time revealing two Report Parameters. We see that the DateCalendarYear parameter that existed before has been joined by the DateCalendarQuarterofYear parameter. It thus becomes evident that the act of checking the Parameters checkbox in the respective Filter pane row triggers the creation of the associated Report Parameter.

30.  Click the DateCalendarQuarterofYear item within the Parameters list to select the new parameter within the Report Parameters dialog.

31.  Replace the default string that appears within the Prompt box, Date.Calendar Quarter of Year, with the following, more user-friendly label:

Calendar Quarter:

32.  Uncheck the checkbox for the Multi-value setting to disable it, as we did with the first Report Parameter.

The Report Parameters dialog - with the DateCalendarQuarterofYear parameter selected - appears, with our adjustments, as shown in Illustration 42.


Illustration 42: Settings for the New Calendar Quarter Report Parameter

33.  Click OK to accept settings, and to dismiss the Report Parameters dialog.

Our completed entry appears in the second row of the Filter pane.

34.  Click the downward selector arrow on the right side of the Dataset selector, once again.

An examination of the Dataset selection picklist reveals only the ProductData and DateCalendarYear Datasets. In this manner, we can verify that, although the creation of the Report Parameter, complete with internal references to a supporting Dataset, is triggered by our placing a checkmark in the Parameters setting of the respective Filter pane row, the Dataset itself is not physically created until we leave the Data tab (as we did in our previous example, when we clicked the Layout tab to transit to the Layout view).

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

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

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

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

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

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

The Filter pane, with all three entries, appears as depicted in Illustration 43.


Illustration 43: The Three Parameterized Filters in the Filter Pane

40.  Select Report ->Report Parameters ... from the main menu, as before.

The Report Parameters dialog opens, this time revealing three Report Parameters.

41.  Click the DateMonthofYear listing (the bottom of the three) within the Parameters list to select the new parameter within the Report Parameters dialog.

42.  Replace the default string that appears within the Prompt box, Date.Month of Year, with the following, more user-friendly label:

Calendar Month:

43.  Uncheck the checkbox for the Multi-value setting to disable it, as we did with the previous two parameters.

The Report Parameters dialog - with the DateMonthofYear parameter selected - appears, with our adjustments, as shown in Illustration 44.


Illustration 44: Settings for the New Month Report Parameter

44.  Click OK to accept settings, and to dismiss the Report Parameters dialog.

45.  Click the downward selector arrow on the right side of the Dataset selector, once again.

An examination of the Dataset selection picklist reveals only the ProductData and DateCalendarYear Datasets, as we left them earlier.

46.  Click the Layout tab, once again, to transit away from the Data tab.

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

48.  Click the downward selector arrow on the right side of the Dataset selector, one last time.

Based upon our earlier discussion, we see the behavior we have expected. All four Datasets, including the primary Dataset 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 depicted in Illustration 45.


Illustration 45: All Datasets Appear as Expected ...

We have successfully added our cascading 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.

NOTE: As we have noted previously, although a Report Parameter and its underlying Dataset are created automatically when we enable parameterization within the Filter pane of the MDX Query Builder (Design Mode), the removal of the “Parameter” checkmark within the associated row of the Filter pane, or even the deletion of the entire corresponding filter row, will not produce an opposite effect. The Report Parameter and the Dataset will remain until they are manually removed. Moreover, the disablement of parameterization within the Filter pane, followed by re-enablement and / or recreation of the Filter pane entry will, unless we intervene before we re-enable / recreate the Parameter, result in the creation of two Report Parameters.

Order is somewhat important in preventing confusion: for example, when deleting, the Report Parameter needs to be deleted first, with the underlying Dataset (to which it refers) being deleted next, before creating a new Report Parameter – even of the same name – within the Filter pane. A Report Parameter can, of course, be repointed, but will default to an incorrect, remaining Dataset if the Dataset to which it was initially married was, for some reason, deleted “from under it.”

It is important to remember that changing a name can also have consequences from the perspective of alignment, and that if one “side of the equation” is modified, the other side must also be modified. Changing the Prompt label has no adverse effect, as we have seen multiple times within the steps of our practice exercise.

We are now ready to verify the operation of the cascading parameters we have installed within the OLAP report.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date