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

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.

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

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 “All” parameter 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.

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

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