Support Parameterization from Analysis Services - Parameter Defaults

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.

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.

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

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:

The Connect to Database dialog appears.

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

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

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:

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.

The Calculations tab opens.

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.

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.

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.

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

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

We have successfully added support, via the named set “Last 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.

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.

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.

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

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

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

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

[Qtr Param Default Caption]

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

[Qtr Param Default Value]

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

[Month Param Default Caption]

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

[Month Param Default Value]

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

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.

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

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.

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 set “Last 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.

The Dataset dialog opens.

AS_DateParamsDefault_Support

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]

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

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.

The Report Parameters dialog opens.

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

Year_Param_Default_Value

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

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

Qtr_Param_Default_Value

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

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

Month_Param_Default_Value

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

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.

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

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.

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.

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.

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.