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 Apr 29, 2008

Support Parameterization from Analysis Services - Parameter Defaults

By William Pearson

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.



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