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, lets 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 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.
(Well 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 lets 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 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.
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
Lets
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.