Support Parameterization from Analysis Services - Parameter Defaults

Tuesday Apr 29th 2008 by William Pearson
Share:

BI Architect Bill Pearson demonstrates a straightforward solution – from the Analysis Services layer - for supporting parameter defaults that represent the last periods of data entry within our cube.

About the Series ...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services (“Reporting Services”), presenting an overview of its features, with tips and techniques for real-world use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools needed to complete the hands-on portion of this article, see BlackBelt Administration: Linked Reports in Report Manager, another article within this series.

Introduction

In Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, we acknowledged a common enterprise reporting requirement: the capability to filter reports at run time for specific information. We noted that this need is typically managed via parameterization (known in other enterprise reporting applications as “prompting”) where the filter criteria is requested (and hence the consumer is “prompted”) when the report is run. Depending upon the parameter type (the most common are type-in and picklist), the filters are typically enacted when the consumer types or selects a value, or a series of values.

In Customize Automatically Created Parameter Support Objects Pt. I, we concentrated our focus, once again, upon picklist parameters, which we noted to be a frequent choice among information consumers for user-friendly operation within reports. We next focused upon the effective use of parameter defaults, in Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, in making reports that contain parameters even more user-friendly and efficient at runtime.

In a subsequent article of this series, Support Parameterization from Analysis Services, we extended our examination of parameterization support yet further, continuing our work within the scenario we established within Customize Automatically Created Parameter Support Objects Pt. I, again with an objective of meeting the need of the hypothetical client to support parameterization, including picklists, within the report, but with a significant difference: We exposed the steps involved in offering a simple means of supporting our solution from within the Analysis Services layer of the integrated Microsoft BI solution. In examining the support of parameterization from Analysis Services, we created a new clone of an existing sample OLAP report, containing a Matrix data region, to which we made structural modifications, to prepare for our practice exercise session. We next discussed, and then implemented, an approach, from within Analysis Services, to meet the need of our hypothetical client to support Report Parameters from the Analysis Services layer of the integrated Microsoft BI solution. Finally, we accessed and employed the new Analysis Services parameter support components from within Reporting Services, reviewing how the various components of the parameter support solution we proposed were tied together.

Finally, in our most recent article prior to this one, Support Parameterization from Analysis Services – Cascading Picklists, we continued to work with the basic OLAP report we created in Support Parameterization from Analysis Services. Our primary focus was to perform a refinement from the perspective of the support we provided from the Analysis Services layer of our integrated BI solution. We established cascading picklists within the report we created in Support Parameterization from Analysis Services (just as we will undertake the addition of support for intuitive parameter defaults at runtime in this article), for greater user-friendliness and overall operational efficiencies. As a part of our examination of supporting cascading picklists, we discussed the utility of establishing cascading parameters within a report to make it more user-friendly and effective, and then we made modifications to the datasets underlying our report parameters to incorporate cascading picklist support.

In this article, we will take up another refinement to our parameters that we will, once again, support via objects that we create within Analysis Services. We will thereby meet a business requirement to generate parameter selection defaults at runtime within the same OLAP report for which we have established cascading parameters supported within the Analysis Services layer. In pursuing this refinement, we will:

  • Reopen the sample Report Server project, AdventureWorks Sample Reports, and access the existing sample report we prepared in Support Parameterization from Analysis Services.
  • Discuss the utility of establishing default parameters within a report to make it more user-friendly and effective.
  • Discuss and implement an approach, from within Analysis Services, to meet the need of our hypothetical client to present parameter picklist defaults that represent the last period of data entry in our cube.
  • Create a new dataset within our report to incorporate parameter default support.
  • Overview how the various components of the default support solution we propose are tied together, as a part of a hands-on practice session where we create and align the necessary components to support our parameter defaults.
  • Preview the report to verify the effectiveness of our solution in runtime action.

Defaults from Analysis Services

Objective and Business Scenario

As any regular reader of this series is aware, parameterization can be implemented in many ways to fit client business needs, ranging from the most basic picklist prompts to sophisticated prompts that “push the envelope,” with regard to going beyond out-of-the-box uses for these components. In Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, we noted that one of the most impressive enhancements introduced with Reporting Services 2005 is its capability to automatically create the complete “chain” of components, including filter, report parameter, and supporting dataset(s), that we need to quickly add basic parameterized filters to our reports. We discussed the fact that the automatically created objects serve us well with minimal modification in many cases; we emphasized, however, the frequent need to “tweak” the components supporting parameterization, often a bit beyond mere cosmetics, to obtain more sophisticated features. In two subsequent articles of our series, Reporting Services: Customize Automatically Created Parameter Support Objects and Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, we outlined some of the customization needs that might arise, and got some hands-on exposure to the steps required to make the modifications involved.

As we mentioned within these two articles, as well as within many others throughout my various Database Journal series’, we might offer solutions to meeting reporting requirements that are completely contained within the reporting layer of the integrated Microsoft business intelligence solution, but that other options exist at the Analysis Services and MSSQL Server RDBMS levels. An important consideration during the design phase of any implementation effort, as we have mentioned repeatedly, is that, while we can still manage much customization within the reporting / presentation layer of our integrated business intelligence solution, we also have multiple options for placing the intelligence behind parameterization (as well as other functionality) within other layers of the solution. For example, I have often supported parameter picklists with support objects I have created within the Analysis Services database that serves as a data source for the report(s) under consideration. As I have emphasized in many other of my articles, one of the numerous advantages of “placing the intelligence” into layers outside Reporting Services lies within the fact that we can thus avoid adding the intelligence to support our needs within every individual report where it is useful; it also means a central point of maintenance, the enforcement of consistent application of the logic and / or business rules across multiple reports, and other benefits.

Working with a new basic OLAP report in Support Parameterization from Analysis Services, we began with an objective of meeting the need of a hypothetical client to support parameterization, including picklists, within the report. In that article we exposed the steps involved in offering a simple means of supporting our solution from within the Analysis Services layer of the integrated Microsoft BI solution, and then accessed and employed the new Analysis Services parameter support components from within Reporting Services.

NOTE: When we consider the fact that we have many options for “where to install the intelligence” within the multi-layered BI solution, we can begin to see that a grasp of all layers is critical. For a general summary of my opinions surrounding the importance of thinking "multi-dimensionally" within the design and implementation of a business intelligence system, see Multi-Layered Business Intelligence Solutions ... Require Multi-Layered Architects.

Having created, in Support Parameterization from Analysis Services, a basic sample OLAP report containing a matrix data region, to which we added simple filter / parameter combinations (based upon structures we created within Analysis Services) and having previewed the effectiveness of our solution for rudimentary runtime parameterization, we next delved into the modifications we needed to make to meet additional business requirements of our hypothetical client, the AdventureWorks organization. In Support Parameterization from Analysis Services – Cascading Picklists we described a scenario where the same client team had made an additional request for our support in accomplishing its objectives. Our client colleagues informed us that information consumers had asked that they refine the report we created together in Support Parameterization from Analysis Services to make the existing parameterization even more user-friendly by providing cascading behavior. We described just what we meant by “cascading” (that is, the set of values of one parameter depends upon the value chosen in another, typically “previous,” parameter), confirmed our understanding of the need with the client representatives, and constructed a working example of a way to provide the cascading picklists that our colleagues had requested for the new report parameters – an example, we proposed, that could be modified to drive different picklist behavior, should the consumers desire further refinements after “road testing” the initial enhancements.

In this article we will continue our extended examination of Parameters in Reporting Services, concentrating upon another specific refinement we can make to our parameters to “fine tune” them to local business environments. We will again reopen the basic sample OLAP report containing a matrix data region, with which we have worked in the immediately previous articles of this series, to which we will focus upon adding parameterization support to meet additional business requirements of our hypothetical client. The requirements will be largely identical to those which we met completely within the Reporting Layer in Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets.

To detail the specifics, the client team has made a request for our further support in accomplishing its reporting objectives. Our client colleagues inform us that information consumers have asked that they refine an existing report to make the existing parameterization even more user-friendly by providing intuitive parameter picklist defaults. They tell us that, because the vast majority of information consumers accessing this report seek to return results based upon the “most recent period” (including Year, Quarter, and Month) for which our cube contains data (specifically, they tell us, from the perspective of Internet Sales Amount data), parameter defaults reflecting these most recent periods would mean more rapid report execution for most users, while still allowing consumers with different requirements the capability of specify their own individual needs.

While this is relatively common desire on the part of information consumers, there are, as I have implied, multiple ways to approach the support of intelligent parameter defaults within the integrated Microsoft business intelligence solution, examples of which, again, include the procedures we took within Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, where we demonstrated an option that is completely contained within Reporting Services. In this article we will expose a means for supporting dynamic parameter defaults from within the Analysis Services layer of the integrated BI solution.

We confirm our understanding of the foregoing needs, and propose to construct working examples of a way to provide the intuitive defaults that our colleagues have requested for the new report parameters, supported from within components that we add into the Analysis Services layer – examples, we add, that can be modified to specify different defaults, should the consumers request them after “road testing” the initial labels, and so forth.

Preparation

Preparation: Locate and Open the Report Project and Report Created in Pt I

As a part of preparation for our practice session in Support Parameterization from Analysis Services, we created a clone of a sample OLAP report, containing a matrix data region, for which we ascertained connectivity of its shared Analysis Services data source. We then made structural modifications to the report, to prepare for our practice exercise session with three date-related parameters, whose additions to the report were requested by our hypothetical client. We created, within the graphical Design Mode of the MDX Query Builder, three filters for which parameterization was enabled via the Filter pane setting.

We discussed, and then implemented, an approach to meet the need of our client to support Report Parameters from the Analysis Services layer of the integrated Microsoft BI solution. We then accessed and employed the new Analysis Services parameter support components from within Reporting Services, examining how the various components of the parameter support solution we proposed are tied together to support our runtime parameters.

In the immediately following article, Support Parameterization from Analysis Services – Cascading Picklists (the one just preceding this article), we resumed where we had left off at the end of Support Parameterization from Analysis Services, modifying our newly created Report Parameters further to support the cascading behavior requested by the client, as described in the section above.

In this practice session, we will resume where we left off at the end of Support Parameterization from Analysis Services – Cascading Picklists, modifying our newly created Report Parameters further to support the defaults requested by the client, as described in the section above. We will perform the preparation and practice steps which follow within the SQL Server Business Intelligence Development Studio, as we did within Support Parameterization from Analysis Services.

1.  Reopen SQL Server Business Intelligence Development Studio, as appropriate.

2.  Close the Start page, if desired.

3.  Reopen the AdventureWorks Sample Reports project, which contains the sample reports that ship with the Reporting Services component of the MSSQL Server 2005 suite, as well as the clone report we created in Support Parameterization from Analysis Services.

4.  Reopen the report with which we conducted our practice session in Support Parameterization from Analysis Services, which we named DBJ_OLAP_Report, by double-clicking the report within the Solution Explorer.

DBJ_OLAP_Report opens in Layout view, as depicted in Illustration 1.


Illustration 1: Our Sample Report in Layout View

If the Analysis Services database is already added to the project in your environment because you previously left it there, then you can skip the next section.

Preparation: Add the Analysis Services Database to the Project

As is necessary, we will continue our preparation by adding the Analysis Services database within our newly reopened project in the Business Intelligence Development Studio. As I note in many of my articles that involve multiple layers of the integrated Microsoft BI solution, I typically like to set up a lab environment for each of my client or research projects where I have both the respective UDM and reports involved with the engagement within an integrated solution in Visual Studio. This ensures ease in testing cube modifications through to the report layer from a single, central location, as well as providing the advantage of effective source control, among numerous other conveniences. For example, in this particular case, I will have both a copy of the sample Adventure Works DW and the AdventureWorks Sample Reports projects added into a single solution within the Business Intelligence Development Studio, where I can access all member objects from one point, the Solution Explorer.

If the Analysis Services Database needs to be added to your project, continue with the following steps in the Business Intelligence Development Studio:

1.  Select File -> Open from the main menu.

2.  Click Analysis Services Database ... from the cascading menu, as shown in Illustration 2.


Illustration 2: Selecting an Analysis Services Database into the Project ...

The Connect to Database dialog appears.

3.  Ensure that the radio button to the immediate left of Connect to existing database (atop the dialog) is selected.

4.  Type the appropriate name within the Server input box.

5.  Select the appropriate name within the Database selector (the Analysis Services database with which we have established connectivity of our report clone above – Adventure Works DW), just underneath the Server input box.

6.  Click the radio button to the immediate left of Add To Solution (in the bottom section of the dialog), to select this option.

The Connect to Database dialog appears similar to that depicted in Illustration 3.


Illustration 3: The Connect to Database Dialog, with Our Input

7.  Click OK to accept our input, and to dismiss the dialog.

The Reading database from the server... message box appears briefly, as shown in Illustration 4.


Illustration 4: Reading the Database from the Server ...

The Adventure Works DW Analysis Services project opens, and we see the various associated objects appear within Solution Explorer, as depicted in Illustration 5 (with Dimensions folder collapsed to conserve space).


Illustration 5: The Adventure Works DW Analysis Services Project Joins the Solution ...

We can now access our sample report and its underlying Analysis Services database, and thus test cube enhancements through to the report layer, from a single, central development environment.

Practice

Practice: Add Parameter Default Support Objects to the Analysis Services Layer

We will next add parameter default support within the Analysis Services database upon which our sample report is based. To do so, we will access the Cube Designer within the Business Intelligence Development Studio, taking the following steps:

1.  Right-click the Adventure Works cube within the Solution Explorer.

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


Illustration 6: Opening the Cube Designer ...

Add Calculated Members in Analysis Services to Support Parameter Defaults within Reporting Services

As we learned in Reporting Services: Customize Automatically Created Parameter Support Objects and Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, as well as in other recent articles within our series, when we create parameterized filters within our dataset(s), Reporting Services automatically creates support objects for self-contained parameterization at the reporting layer. These support objects include Report Parameters, as we have seen, as well as datasets to support each of those Report Parameters. The datasets use the WITH MEMBER keywords to create calculated members at runtime, when the datasets are instantiated in Reporting Services, which are then referenced, by default, in the Value and Label fields within the associated Report Parameter settings page.

Because we took the shortcut approach of having Reporting Services generate the support objects automatically in our creation of the date-related parameters in Reporting Services: Customize Automatically Created Parameter Support Objects we had the Report Parameters in place, ready to then be pointed to the calculated members we created in Analysis Services in Support Parameterization from Analysis Services. In this article, we will save time in like manner, creating support objects in Analysis Services which we will then reference in a dataset, and then tie to the appropriate Report Parameters in Reporting Services, as we shall see. We will continue with the somewhat intuitive date-related parameters we have already established, as we create the calculated members we will need in Analysis Services to support parameter defaults. We could, of course, include additional custom calculated members that are entirely different, should the need arise in our local business environments.

We review the parameter default requirements with the client representatives, reiterating that, while parameterization can certainly be managed using the automatically created objects provided within Reporting Services, several benefits accrue to us in creating the support objects within the Analysis Services layer. Positing the intelligence within the cube will, we assert, mean that the same logic can be carried forward to multiple reports by simply referencing the calculations in their respective data sources, versus performing the calculations for every affected measure, as an early example, in the report (and adding to processing time, etc.). Moreover, we emphasize that maintaining the logic in a single location in the Analysis Services layer means a single point of maintenance: we can modify the logic in one place and rely upon the changes to “ripple through” to all reports that reference that logic, versus having to mechanically modify each individual report. This also ensures consistency of application of the logic to all affected reports: none will be overlooked for prospective modifications in the logic, for instance, when modifications become necessary, nor will different logic be accidentally applied across different reports.

From within the now open Cube Designer, we will begin creating Analysis Services objects, specifically a named set and several calculated members, to support parameter defaults in our practice report. Keep in mind that there are multiple ways to accomplish this effort: we will focus upon the creation of simple calculated members that we can easily pull into our existing report datasets to illustrate a straightforward approach that might be handled in more sophisticated ways.

1.  Select the Calculations tab within the Cube Designer.

The Calculations tab opens.

2.  Click the bottom entry in the Script Organizer pane (the name of the bottom item will likely differ in your own environment) to anchor the cursor, as depicted in Illustration 7.


Illustration 7: Click the Bottom Entry in the Script Organizer to Position the New Calculation

3.  Click the New Named Set button atop the Calculations tab, as shown in Illustration 8.


Illustration 8: Click the New Calculated Member Button

Clicking the New Named Set button adds a new named set to the Script Organizer pane (by default named “[Named Set]”) and displays fields for its definition in the calculations form in the Calculations Expressions pane.

At this stage, let’s review what we already have in place in the report datasets. The focus is to build upon what we have previously put in place, and leverage the existing datasets to simply include, as part of the data they entrain from Analysis Services, the calculated members we employ there, in later steps, to generate our parameter defaults.

4.  Leaving the Cube Designer open at the Calculations tab, return to the DBJ_OLAP_Report where we left it open in Layout view earlier.

5.  Click the Data tab of the report to expose the Query pane.

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

7.  Select the AS_DateYrParams_Support dataset (which we created in Support Parameterization from Analysis Services), as shown in Illustration 8.


Illustration 9: Select the AS_DateYrParams_Support Dataset ...

The MDX for the dataset, which we added as a part of our steps in aligning parameter support to the respective Analysis Services calculated members in our earlier article, appears as depicted in Illustration 10.


Illustration 10: MDX for the AS_DateYrParams_Support Dataset

We note that the query simply selects calculated members for parameter caption (what the information consumer sees in the parameter selector at runtime) and parameter value (the qualified MDX value that is passed to Analysis Services to query and retrieve data at runtime, based upon the selection of the information consumer). We can certainly use this logic to propel our existing solution - we need only extend the solution to include support for parameter defaults. To do so we will return to Analysis Services, and create a named set and additional calculated members within our cube calculations, whose mission will be to specify the default for this, and each of the other two, date-related parameters, as we shall see. Once we have created the additional calculated members, we will return to reference them in a separate dataset through which we align the newly created Analysis Services objects with the respective report parameters.

NOTE:

For more information about Named Sets, particularly from an MDX syntax perspective, please see Named Sets Revisited, a member of my Introduction to MSSQL Server Analysis Services series at Database Journal.

For an introduction to Calculated Members, particularly from an MDX syntax perspective, please see the following articles:

Each of these articles is a member of my MDX in Analysis Services series at Database Journal.

8.  Return to the Calculations tab of the Cube Designer.

9.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Named Set]”).

[Last Period Sales]

10.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

   'TAIL(
      FILTER(
         [Date].[Calendar].[Month].MEMBERS,
            NOT ISEMPTY ([Measures].[Internet Sales Amount])
          ),
      1
   )'

The new [Last Period Sales] calculation appears in the Calculations Expressions pane, as shown in Illustration 11.


Illustration 11: The [Last Period Sales] Named Set in the Calculations Expressions Pane

11.  Click the Check Syntax button atop the Calculations tab, as depicted in Illustration 12.


Illustration 12: Click the Check Syntax Button

The Check Syntax message box appears, indicating that the syntax check was successful, as shown in Illustration 13.


Illustration 13: Our Syntax Appears to Have Passed Checking ...

12.  Click OK to dismiss the message box.

We have successfully added support, via the named setLast Period Sales,” for what will become a row axis in a Reporting Services dataset, as we shall see in short order. We have, in effect, specified that Analysis Services use the logic within the named set to determine the “most recent period” (including Year, Quarter, and Month) for which our cube contains data. The logic accomplishes this by identifying the most recent Period for which the measure Internet Sales Amount is not empty. We use the MDX Tail() function to accomplish selection of “the last non-empty” period - by employing the MDX Filter() function to filter out any except periods that are “occupied” by an Internet Sales Amount.

Because we are retrieving data at the Month level, the corresponding row set returned by Reporting Services will also include, as we shall see, higher levels within the Date dimension (Calendar hierarchy) - a characteristic of Reporting Services that produces a dataset ideal for many purposes.

NOTE: For more details surrounding the MDX Tail() function, see Basic Set Functions: Subset Functions: The Tail() Function. For more information about the Filter() function, see Basic Set Functions: The Filter() Function. Examples of these popular and useful functions in action can also be found in numerous other member articles throughout my MDX Essentials series at Database Journal.

We next need to add three pairs of calculated members (for a total of six individual members) – one pair, consisting of a default caption and a default value, for each of our three date-related parameters. In addition to the named set we created earlier, we will also specify these calculated members within the SELECT clause of a new dataset in Reporting Services, for the purposes of supporting our parameter defaults. (We’ll see how all this aligns later). These calculated members will create the following six data columns within the returned dataset:

  • YearParamDefaultCaption
  • YearParamDefaultValue
  • QtrParamDefaultCaption
  • QtrParamDefaultValue
  • MonthParamDefaultCaption
  • MonthParamDefaultValue

The “default caption” columns will not be used within our parameter definitions, but they represent a possibly useful “label” that I would typically add into my design of datasets of this sort - possibly for use in labeling within the report, where it might be helpful to list our parameters so that “secondary” information consumers are made aware of the filters we have placed upon the data when looking at, say, a printed or exported version of the report. The “caption” version of the “default value,” (the value itself represents the actual qualified names that we pass as a filter to Analysis Services at runtime) would perhaps serve as a more understandable form of the values when used in this manner. We will see how the default values are used within the Report Parameters in the section that follows.

13.  Click the bottom entry in the Script Organizer pane, to anchor the cursor, as we did before creating our named set above.

NOTE: Once a calculation is created, we can always move it up or down within the Script Organizer pane using the Move Up or Move Down buttons, respectively. These buttons appear together atop the Calculations tab, as depicted in Illustration 14.


Illustration 14: Move Up and Move Down Buttons, atop the Calculations Tab

14.  Click the New Calculated Member button atop the Calculations tab, as shown in Illustration 15.


Illustration 15: Click the New Calculated Member Button

Clicking the New Calculated Member button adds a new calculation to the Script Organizer pane (by default named “[Calculated Member]”) and displays fields for its definition in the calculations form in the Calculations Expressions pane, just as we saw with the named set we created earlier.

15.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

[Year Param Default Caption]

16.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Calendar Year].CURRENTMEMBER.MEMBER_CAPTION

The new Year Param Default Caption calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 16.


Illustration 16: The Year Param Default Caption Calculation in the Calculations Expressions Pane

17.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the first calculated member we added.

18.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

We have successfully added support for the default caption of the Year Report Parameter. As stated earlier, we will not reference this value within our ultimate report layout. The default caption simply affords us an opportunity to do so at a later time if convenient, and might mean less adjustment to our working datasets, assuming we later meet the common client request to add this to our report for various reasons, instead of, or in addition to, the qualified name that we already have there. (The caption field in the dataset also makes clear the “English” name of the corresponding Default Value column in the dataset that is retrieved).

19.  Click the New Calculated Member button, once again.

20.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

[Year Param Default Value]

21.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME

The new Year Param Default Value calculation appears in the Calculations Expressions pane, with our input, as shown in Illustration 17.


Illustration 17: The Year Param Default Value Calculation in the Calculations Expressions Pane

22.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the first calculated member we added.

23.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

Now let’s repeat the calculated member creation process with the next pair of calculated members – for support of the Quarter parameter defaults.

24.  Click the New Calculated Member button, once again.

25.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”, as before).

[Qtr Param Default Caption]

26.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Calendar Quarter of Year].CURRENTMEMBER.MEMBER_CAPTION

The new Qtr Param Default Caption calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 18.


Illustration 18: The Qtr Param Default Caption Calculation in the Calculations Expressions Pane

27.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the calculated member we added earlier.

28.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

29.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”, as before).

[Qtr Param Default Value]

30.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Calendar Quarter of Year].UNIQUENAME

The new Qtr Param Default Value calculation appears in the Calculations Expressions pane, with our input, as shown in Illustration 19.


Illustration 19: The Qtr Param Default Value Calculation in the Calculations Expressions Pane

31.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the calculated member we added earlier.

32.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

33.  Click the New Calculated Member button, once again.

34.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”, as before).

[Month Param Default Caption]

35.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Month of Year].CURRENTMEMBER.MEMBER_CAPTION

The new Month Param Default Caption calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 20.


Illustration 20: The Month Param Default Caption Calculation in the Calculations Expressions Pane

36.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the other calculated members we added.

37.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

38.  Click the New Calculated Member button, once more.

39.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

[Month Param Default Value]

40.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Month of Year].CURRENTMEMBER.UNIQUENAME

The new Month Param Default Value calculation appears in the Calculations Expressions pane, with our input, as shown in Illustration 21.


Illustration 21: The Month Param Default Value Calculation in the Calculations Expressions Pane

41.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the first calculated member we added.

42.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

We have completed the addition of the Analysis Services objects required to support the targeted date-related Report Parameters in the Reporting layer.

Process the Analysis Services Database

Our next step will be to process the sample database, so as to make the new calculated members available to Reporting Services. To do so, we will take the following steps.

1.  Within the Solution Explorer, right-click the Adventure Works DW project.

2.  Select Process ... from the context menu that appears, as depicted in Illustration 22.


Illustration 22: Processing the Adventure Works DW Project

3.  Click the Yes button on the message box appearing next, asking if we wish to save all changes before processing, as shown in Illustration 23.


Illustration 23: Saving Changes First ...

The Process Database – Adventure Works DW dialog appears, as depicted in Illustration 24.


Illustration 24: The Process Database – Adventure Works DW Dialog

4.  Click Run ... on the Process Database – Adventure Works DW dialog, to begin processing.

The Process Progress viewer appears, and details the processing steps as they take place. Once processing is completed, a Process succeeded message appears in the Status bar at the bottom of the dialog, as shown in Illustration 25.


Illustration 25: Process Succeeded Message Appears ...

5.  Click Close to close the Process Progress viewer.

6.  Click Close on the Process Database – Adventure Works DW dialog.

We can now move back to our report to complete the steps required to access our new Analysis Services parameter support objects, and to reference them in our targeted Report Parameters.

Practice: Support Parameter Defaults in Reporting Services via Analysis Services Objects

We will next access and reference our new Analysis Services support objects from within our sample report.

Access Analysis Services Parameter Default Support via a New Dataset

We will return to the Data tab of our still open report within Reporting Services, where we will create a new dataset, designed to support parameter defaults for Year, Quarter, and Month, within the following steps. Our intent, as we have alluded earlier, is to create a dataset that juxtaposes our new named setLast Period Sales,” (as a row axis in the new Reporting Services dataset) and each of the Year, Quarter, and Month caption and value calculated members.

As we noted earlier, because we are retrieving data at the Month level, the corresponding row set returned by Reporting Services will also include, as we shall see, higher levels within the Date dimension (Calendar hierarchy) - a characteristic of Reporting Services that produces a dataset ideal for many purposes.

1.  Leaving the Cube Designer open at the present position, return to the Data tab of the report with which we are working in Reporting Services.

2.  Click the Refresh button on the Data tab toolbar, as depicted in Illustration 26.


Illustration 26: Refresh the Environment for the Newly Processed Data Source ...

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

4.  Select <New Dataset> within the selector, as shown in Illustration 27.


Illustration 27: Creating a New Dataset

The Dataset dialog opens.

5.  Type the following into the Name box of the dialog (replacing the default name of “DataSet1”).

AS_DateParamsDefault_Support

6.  Click OK to dismiss the dialog and to open the graphical query designer.

7.  Click the Design Mode button to shift to the generic query designer, as depicted in Illustration 28.


Illustration 28: Shifting to the Generic Query Designer ...

8.  Type (or cut and paste) the following into the Query pane of the generic query designer, replacing the default, shell syntax:

SELECT 
 
   {[Measures].[Year Param Default Caption], 
     [Measures].[Year Param Default Value],
 
       [Measures].[Qtr Param Default Caption], 
 
         [Measures].[Qtr Param Default Value], 
         
       [Measures].[Month Param Default Caption], 
 
     [Measures].[Month Param Default Value]}
 
   ON AXIS(0),
 
   [Last Period Sales]  ON AXIS(1)
 
FROM  
 
   [Adventure Works]

9.  Click the Execute Query (!) button to run the query against the Analysis Services data source, as shown in Illustration 29.


Illustration 29: Execute the New Query ...

The query runs, and the data is returned, as partially depicted in Illustration 30.


Illustration 30: The New Dataset Appears ... (Partial View)

As we can see, we have the workings of a support dataset for the parameter defaults – both a caption and a value for each of the Year, Quarter, and Month parameters. (Note the way that the single named set –defined axis row generates the default for all parameter levels of the Date dimension under consideration).

We are now ready to “hook up” the Default Value columns of the new dataset to the Report Parameters they will support. We will accomplish this within the next subsection.

Reference Analysis Services Support within Report Parameters

We will return to each of the three date-related Report Parameters at this juncture, where we will reference the supporting dataset we created in the previous subsection, within the respective Dataset and Value fields. This will align the dataset, which is itself aligned with our newly created support objects in Analysis Services, with the Report Parameters themselves. The ultimate objective, to provide intuitive, useful parameter defaults (“last activity periods for the Product Categories selected” within the Analysis Services data source, per our client colleagues) will be accomplished at the completion of this step.

1.  From the main menu, select Report ->Report Parameters, as shown in Illustration 31.


Illustration 31: Select Report ->Report Parameters

The Report Parameters dialog opens.

2.  Within the Parameters list box, on the left side of the dialog, click DateCalendarYear.

The Properties, Available values, and Default values settings for the selected Report Parameter appear.

3.  Within the Properties section on the right side of the dialog, within the Default values section, underneath the Available values section, ensure that the From query radio button is selected (click it to fill in the button).

4.  Select AS_DateParamsDefault_Support in the Dataset selector, as depicted in Illustration 32.


Illustration 32: Select AS_DateParamsDefault_Support as the Dataset ...

5.  Underneath the Dataset selector, select the following in the Value field selector:

Year_Param_Default_Value

The Default values settings for the DateCalendarYear Report Parameter appear, with our modifications, as shown in Illustration 33.


Illustration 33: DateCalendarYear Report Parameter Settings, with Modifications

6.  Within the Parameters list box, as before, click DateCalendarQuarterofYear.

The Properties, Available values, and Default values settings for the selected Report Parameter appear.

7.  Within the Properties section on the right side of the dialog, within the Default values section, underneath the Available values section, ensure that the From query radio button is selected, as we did for the previous Report Parameter.

8.  Select AS_DateParamsDefault_Support in the Dataset selector, as we did earlier.

9.  Underneath the Dataset selector, select the following in the Value field selector:

Qtr_Param_Default_Value

The Default values settings for the DateCalendarQuarterofYear Report Parameter appear, with our modifications, as depicted in Illustration 34.


Illustration 34: DateCalendarQuarterofYear Report Parameter Settings, with Modifications

10.  Within the Parameters list box, as before, click DateMonthofYear.

The Properties, Available values, and Default values settings for the selected Report Parameter appear.

11.  Within the Properties section on the right side of the dialog, within the Default values section, underneath the Available values section, ensure that the From query radio button is selected, once more.

12.  Select AS_DateParamsDefault_Support in the Dataset selector, as we did in the last Report Parameter.

13.  Underneath the Dataset selector, select the following in the Value field selector:

Month_Param_Default_Value

The Default values settings for the DateMonthofYear Report Parameter appear, with our modifications, as shown in Illustration 35.


Illustration 35: DateMonthofYear Report Parameter Settings, with Modifications

14.  Click OK to accept all our Report Parameter modifications, and to dismiss the Report Parameters dialog.

Having created the parameter default support structures within Analysis Services, we added a supporting dataset, based upon those structures, within our sample report. We then referenced the new dataset to the respective Report Parameters. We are now ready to verify the effectiveness of our solution, specifically that the date-related Report Parameters – whose Available and Default values are now wholly supported by objects within the Analysis Services layer of our integrated BI solution – perform as expected at runtime.

Verification: Preview the Report and Inspect the Effectiveness of Our Solution

Let’s preview the report to inspect the results of our handiwork.

1.  Click the Preview tab.

DBJ_OLAP_Report.rdl initializes, and the first prompt (based upon the pre-existing ProductCategory parameter), becomes enabled.

2.  Click the downward pointing arrow on the right side of the Product Category selector.

3.  Select All Products, while deselecting both Bikes and Components (the parameter defaults) within the Product Category parameter selector, as depicted in Illustration 36.


Illustration 36: Select All Products in the Product Category Parameter ...

4.  Click outside the Product Category selector (somewhere in the grey area containing the parameter selectors – but not upon any of the parameter selections themselves).

We notice at this stage that the date –related parameters contain defaults. These defaults represent, indeed, the last Year, Quarter and Month within the cube containing Internet Sales Amounts for any of the Product Categories sold by the Adventure Works organization. (This can be verified via alternate means, such as the Cube Browser within Analysis Services, among others.) These intelligent defaults give our consumers a better runtime experience – especially if this is a report that is executed normally at the end of each month, and so forth. In any event, the defaults meet the stated requirements of our client colleagues.

5.  Click the View Report button.

The report executes quickly and returns the data for the selections we have made within our parameter picklists, in a manner similar to that shown in Illustration 37.


Illustration 37: The Report Parameters Operate as Expected ...

Our verification process has demonstrated the effective support of Report Parameter defaults through the use of objects within the Analysis Services layer of our BI solution.

6.  Experiment further with the report, if desired.

7.  When finished with the report, click the Layout tab.

8.  Select File -> Save DBJ_OLAP_Report.rdl As ... to save our work, up to this point, to a location where it can be easily accessed for later reference, if that is useful..

9.  Select File -> Exit to leave the design environment, when ready.

Conclusion ...

In this article, we continued the extended examination of Parameters in Reporting Services that we began in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, and which we continued in Reporting Services: Customize Automatically Created Parameter Support Objects and Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, as well as in our most recent article prior to this one, Support Parameterization from Analysis Services – Cascading Picklists.

Working with the basic OLAP report we prepared in Support Parameterization from Analysis Services, we once more began within the scenario we established within Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, with an objective of meeting the need of the hypothetical client to support parameter defaults within the report. This time, however, we exposed the steps involved in offering a simple means of supporting our solution from within the Analysis Services layer of the integrated Microsoft BI solution.

In examining the requested addition of parameter defaults within a sample OLAP report, we began with a review of the concept of parameterization in general, and briefly overviewed options for supporting report parameterization among the three primary layers of the integrated Microsoft business intelligence solution. We then examined and took up the next requested refinement to our parameters that we were, once again, to support via objects that we created within Analysis Services. Specifically, we were asked to meet a business requirement to generate parameter selection defaults at runtime within the same OLAP report for which we have established cascading parameters supported within the Analysis Services layer.

In pursuing our objective of supporting parameter defaults via objects in the Analysis Services layer, we prepared for our practice session by reopening the sample Report Server project, AdventureWorks Sample Reports, and accessing the existing sample report we prepared in Support Parameterization from Analysis Services, with which we have been working through our most recent article prior to this one, Support Parameterization from Analysis Services – Cascading Picklists. We next generally discussed the utility of establishing default parameters within a report to make it more user-friendly and effective.

We then discussed and implemented an approach, from within Analysis Services, to meet the need of our hypothetical client to present simple parameter picklist defaults that represent the last period of data entry in our cube. We then added a single dataset into our report to incorporate parameter default support for all our date-related parameters. Moreover, we overviewed, at appropriate junctures, how the various components of the default support solution we proposed are tied together, as a part of a hands-on practice session where we create and align the necessary components to support our parameter defaults. Finally, we previewed the report to observe the effectiveness of our solution in runtime action.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved