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 Jan 31, 2008

Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets - Page 2

By William Pearson

Support OLAP Parameter Defaults Using Datasets

Objective and Business Scenario

As I have stated within numerous articles of this series, I have implemented parameters 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. As I discussed in detail in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, 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) we need to quickly add basic parameterized filters to our reports. While the automatically created objects serve us well with minimal modification in many cases, we often have to “tweak” the components supporting parameterization a bit to obtain more sophisticated features. Our practice session within this article contains an example of the sort of customization to which I refer.

Another important consideration during the design phase of any implementation effort, too, 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) in other layers of the solution. For example, I have often supported parameter picklists with support objects I have created within the Analysis Services cube that is used as a data source for the reports under consideration, and so forth. (For an example of implementing support for a hierarchical picklist in this manner, see my Database Journal article Create a Cube-Based Hierarchical Picklist.) 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.

As we have emphasized in earlier articles, the reporting / presentation layer of the integrated Microsoft business intelligence solution is often the only point of interaction with corporate information stores for organizational information consumers. Parameterization not only makes this interface more user-friendly, but it affords the tandem benefit of enabling knowledgeable report authors to leverage single reports for multiple uses, delivering “more for less” from a development and maintenance perspective.

Reporting Services offers a robust Report Parameter interface that is exposed through the Report Designer, through its Web service interface, through the Report Manager, and via the reports we schedule. Many of the reports we author for our employers or clients depend upon time / date parameters that are based upon the “current” date in some regard. As an illustration, a sales report might typically be executed at multiple period “cutoffs” (weekly, monthly, quarterly, annually, and so forth) to present data from the previous period. Because Reporting Services supports dynamic default parameters, as we shall see, the tasks involved in providing information consumers with user-friendly, intuitive parameters, by which timely, relevant results can be retrieved become far easier for knowledgeable authors.

In this, the second half of a two-part 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. Having created a basic sample OLAP report containing a matrix data region, to which we added simple filter / parameter combinations, in Pt. I, and having reviewed, as part of our preparation of the sample report for our practice session, the objects that Reporting Services automatically creates, in conjunction with our additions, to support runtime parameterization, we will next delve into the modifications we need to make to meet additional business requirements of a hypothetical client, the AdventureWorks organization.

To detail the specifics, the same client team with which we consulted in Pt. I, the Adventure Works Customer Care department, has made an additional request for our support in accomplishing its objectives. Our client colleagues inform us that information consumers have asked that they refine the report we created together in Pt. I 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, 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 specifying their own specific needs.

While this is a 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. In this article we will demonstrate a means for a accomplishing our ends that is completely contained within Reporting Services. But it is important to remember that we might also add objects to support dynamic parameter defaults within the Analysis Services layer (via calculated members / named sets, etc.) or even from the relational layer (where we might support the needs via tables constructed for that purpose, User Defined Functions (“UDFs”), or via other mechanisms). One of the numerous advantages of “placing the intelligence” into layers outside Reporting Services lies within the fact that we would thus avoid adding the intelligence to support our needs within every report where it is useful; it also means a central point of maintenance, the enforcement of consistent application of the logic across multiple reports, and other benefits. (We provide demonstrations of these approaches within other articles of this series.)

Because our client colleagues tell us that they want a solution which is contained completely within Reporting Services, we decide to take a relatively straightforward path, the creation of a dataset within our sample report, from which we will populate the targeted Report Parameter default settings. The client team requests a brief overview of the MDX involved in the creation of such a dataset, as well as help in understanding the relationships between the components involved in the overall solution, to allow for ongoing maintenance, as well is for the extrapolation of the concepts to meet other reporting needs.

NOTE: There are, of course, multiple other options for producing the necessary default values, even from within the Reporting Services layer. For instance, we might have generated the default values in each of the respective, already existing parameter support datasets. Moreover, we might have used calculated fields, or even embedded code, within Reporting Services to accomplish similar ends.

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 – examples, we add, that can be modified to specify different defaults, should the consumers request them after “road testing” the initial labels.


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

As a part of preparation for our practice session in Pt. I, 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.

In conjunction with the creation of the parameterized filters, we inspected the automatically created Report Parameters and their settings, as well as the subsequently created datasets underlying the new Report Parameters. We then focused upon the modification of the respective datasets to filter the “Allparameter level from the data rows retrieved and presented within the parameter picklists at runtime. Throughout the steps we undertook, we discussed how the various components were tied together, and the potential challenges we face in modifying these objects without consideration of the resulting dependencies. Finally, we previewed the report to observe the effectiveness of our solution in eliminating the “All” selection in the parameter picklists, as well as the added benefit of the general cascading nature of the parameters at runtime.

In this half of the practice session, we will resume where we left off at the end of Pt. I, modifying our newly created Report Parameters further to support the dynamic parameter picklist 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 Pt. I.

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 Pt. I.

4.  Reopen the report with which we conducted our practice session in Pt. I, DBJ_OLAP_Report, by double-clicking the report within the Solution Explorer.

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

Illustration 1: Our Sample Report in Layout View

We are now ready to begin working with the existing report to add support for dynamic parameter picklist defaults.

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