BI Architect Bill Pearson
demonstrates a straightforward solution from the Analysis Services layer -
for supporting parameterization within Reporting Services.
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
As we learned in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, a common enterprise reporting requirement is the capability to filter reports at run time for specific information. This is typically managed via parameterization, also known 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 reviewed type-in and picklist parameters in general, and then 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. Picklist parameters add value in several ways, including the inherent elimination of typing errors, as well as the enforcement of standard selections for report execution. We noted that the design of the picklist is important in making runtime selections easy, and that picklists presenting long scrolling processes or other cumbersome characteristics can negatively affect consumer perceptions, particularly when they are components within frequently accessed reports. We then focused upon the effective use of parameter defaults in making reports that contain parameters even more user-friendly and efficient at runtime.
In Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, we discussed the fact that Parameter defaults are important to consider for multiple reasons. Primary among these reasons, we learned, are the elimination of confusion (especially among less experienced users, who may be startled by simple blank boxes for input, and the like), and the provision for more likely, dynamically intelligent inputs (such as most recent period containing data within the cube) that is, the more likely desired runtime selections, considering the nature of the report and its typical usage by information consumers. We then continued working with the basic OLAP report we created in Pt. I, establishing a scenario within which we exposed the steps involved in meeting a basic need of a hypothetical client for the automatic display of default selections within the parameter picklists of the report at runtime.
In this article, we will work with an identical copy of the basic OLAP report we created in Customize Automatically Created Parameter Support Objects Pt. I. We will continue working within the scenario we established within that article, again with an objective of meeting the need of the hypothetical client to support parameterization, including picklists, within the report. This time, however, we will expose 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 will:
- Review the concept of parameterization in general, and briefly overview options for supporting parameterization from among the three primary layers of the integrated Microsoft business intelligence solution;
- Open the sample Report Server project, AdventureWorks Sample Reports, and ascertain connectivity of its shared Analysis Services data source;
- Create a clone of an existing sample OLAP report, containing a Matrix data region, with which to perform our practice exercise;
- Make structural modifications to the clone report, to prepare for our practice session;
- Discuss and implement 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;
- Access and employ the new Analysis Services parameter support components from within Reporting Services;
- Overview how the various components of the parameter support solution we propose are tied together, as a part of a hands-on practice session where we create and align the necessary components within Analysis Services and Reporting Services to support report parameterization;
- Preview the report to observe the effectiveness of our solution in runtime action.
Support Parameterization from Analysis Services
Objective and Business Scenario
As I have stated within numerous articles of this series, I have implemented parameters in many ways to fit client business needs, ranging from the most basic picklist prompts to sophisticated prompts that push the envelope, with regard to going beyond out-of-the-box uses for these components. 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 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 noted 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) in other layers of the solution. For example, I have often supported parameter picklists with support objects I have created within the Analysis Services cube that 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 across multiple reports, and other benefits.
We will undertake the steps to provide an example of similar support within our practice session that follows. (For another example of implementing support for a hierarchical picklist in this manner, see my Database Journal article Create a Cube-Based Hierarchical Picklist.)
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.
As most of us realize, the reporting / presentation layer of the integrated Microsoft business intelligence solution is often the only point of interaction with corporate information stores for organizational information consumers. Parameterization not only makes this interface more user-friendly, but it affords the tandem benefit of enabling knowledgeable report authors to leverage single reports for multiple uses, delivering more for less from a development and maintenance perspective.
Many of the reports we author for our employers or clients depend upon time / date parameters that are based upon the current date in some regard. As an illustration, a sales report might typically be executed at multiple period cutoffs (weekly, monthly, quarterly, annually, and so forth) to present data from the previous period. Because Reporting Services supports dynamic default parameters, as we shall see, the tasks involved in providing information consumers with user-friendly, intuitive parameters, by which timely, relevant results can be retrieved become far easier for knowledgeable authors.
In this 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 first create a basic sample OLAP report containing a matrix data region, to which we will focus upon adding parameterization to meet additional business requirements of a hypothetical client, the AdventureWorks organization. The requirements will be largely identical to those which we met completely within the Reporting Layer in Reporting Services: Customize Automatically Created Parameter Support Objects.
To detail the specifics, a client team with which we have consulted for some time, the Adventure Works Customer Care department, has made a request for our support in accomplishing its reporting objectives. Our client colleagues inform us that information consumers have asked that they refine an existing report to add user-friendly parameterization. They tell us that the vast majority of information consumers accessing this report seek to return results based upon date parameters (including Year, Quarter, and Month). This would, of course, allow consumers with different requirements the capability of specifying their own specific needs at given runtimes.
While this is a relatively common desire on the part of information consumers, there are, as I have implied, multiple ways to approach the support of parameterization within the integrated Microsoft business intelligence solution, examples of which include the procedures we took within Reporting Services: Customize Automatically Created Parameter Support Objects, where we demonstrated an option that is completely contained within Reporting Services. In this article we will expose a means for supporting parameterization 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 runtime picklists 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 (we address this is our next article), should the consumers request them after road testing the initial picklists, and so forth.
Preparation
Preparation: Create a Clone Report within the Reporting Services Development Environment
For purposes of our
practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples
that are available with (and installable separately from) the MSSQL Server
2005 integrated business intelligence suite. Making preparatory
modifications, and then making the enhancements to the report to add the
functionality that forms the subject of our lesson, can be done easily within
the Business Intelligence Development Studio environment.
Working with a copy of the report will allow us the luxury of freely exploring
our options, and will leave us with a working example of the specific approach
we took, to which we can refer in our individual business environments.
Open
the Sample Report Server Project
For purposes of our
practice session, we will open the AdventureWorks Sample Reports
project, which contains the sample reports that ship with the Reporting
Services component of the MSSQL Server 2005 suite. We will complete
our practice session within the sample project so as to save the time required
to set up a development environment from scratch within the Business
Intelligence Development Studio.
To open the AdventureWorks
Sample Reports project, please see the following procedure in the References
section of my articles index:
Ascertain Connectivity of the Shared Data Source
Lets ensure we have a
working data source. Many of us will be running side-by-side
installations of MSSQL Server 2000 and MSSQL Server 2005. This
means that our installation of the latter will need to be referenced as a server
/ instance combination, versus a server name alone. (The default for
the Adventure Works DW project samples connection is localhost,
which will not work correctly in such a side-by-side installation, as MSSQL
Server 2000 will have assumed the identity of the local PC by default.)
If you do not know how to ascertain or
modify connectivity of the Analysis Services data source, please perform
the steps of the following procedure in the References section of my
articles index:
Create a Copy of the Sales Reason Comparisons Report
We will begin with a copy
of the Reporting Services 2005 Sales Reason Comparisons OLAP report,
which we will use for our practice exercises. Creating a clone of the report means we can make
changes to select contents (perhaps as a part of later exploration with our
independent solution), while retaining the original sample in a pristine state
for other purposes, such as using it to accompany relevant sections of the Books
Online, and other documentation. We can, therefore, use the original as a
part of learning more about Reporting Services (particularly an OLAP
report using an Analysis Services data source), and other
components of the Microsoft integrated business intelligence solution, in
general.
If you do not know how
to create a copy of an existing report, please perform the steps of the
following procedure in the References section of my articles index:
We now
have a clone OLAP report file within our Reporting Services 2005 Project,
with which we can proceed, in the next section, to make modifications for our
subsequent practice session.
Preparation: Add the Analysis Services Database to the Project
We will
continue our preparation by adding the Analysis Services database with
which we ascertained connectivity in the Ascertain Connectivity
of the Shared Data Source section above - within
our newly created project in the Business Intelligence Development Studio.
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.
Continuing
within our newly created project in the Business Intelligence Development
Studio, take the following steps:
1.
Select File
-> Open from the main menu.
2.
Click Analysis
Services Database ... from the cascading menu, as shown in Illustration 1.
Illustration 1:
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 ascertained connectivity of our report clone above),
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 2.
Illustration 2: 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 3.
Illustration
3: 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
4 (with Dimensions folder collapsed).
Illustration
4: 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.
Preparation: Modify the OLAP Report for Use within Our Practice Session
We will
next make a few modifications to prepare the report for our practice session.
Our objective will be to begin the session with a simple OLAP report that
contains basic parameterized filters as required by our client
colleagues (just as we did in preparing the report to support its own
parameters within the articles we cited earlier). This time, however, we will
only be creating the parameters within Reporting Services to serve as
skeletal placeholders from which we will reference support objects that
we will add to our Analysis Services data source in the respective
procedural section below. Some of the support structure that is automatically
created within Reporting Services as part of this process will be
discarded / ignored. While there are other ways of approaching the creation of
the parameters in Reporting Services, we are simply taking a short cut
to finalize preparation and get to our focus subject matter, the creation and
alignment of the parameter support objects in Analysis Services, more
rapidly.
Lets
open the report (for those of us not already there) and make the necessary
settings to place it into a state from which we can commence our from scratch
practice steps.
1.
Right-click DBJ_OLAP_Report.rdl
(or your own choice of a similar report) in the Solution Explorer.
2.
Select Open
from the context menu that appears, as shown in Illustration 5, as
necessary.
Illustration 5: Opening the New Report ...
We will
begin by adding additional parameters to our sample report. These parameters
will initially be of a standard, basic picklist variety. We will then work
with these parameters to align them with support objects we later construct
within the reports underlying Analysis Services data source.
Add Additional Parameters through the Query Builder Interface and Review the
Automatic Creation of Support Objects
As we
stated in Mastering
OLAP Reports: Parameters for Analysis Services Reporting, Pt. I,
and elsewhere within this, and other of my, series,
the Design Mode button in the toolbar of the Data tab allows us
to easily shift between Design view and Query view, when working
with our OLAP queries. The idea, obviously, is to provide those who are not at
ease with direct MDX a means of creating queries within a graphical interface.
We
have begun with a basic report with core datasets, and are ready to add three
rudimentary, date-related prompts that will meet our objectives. We will create these prompts
within Design view, by taking the following steps:
We
enter the Data view, which defaults to the presentation of the first of
two pre-existing datasets, ProductData. To state what is probably
obvious, we can tell that we are in Design view, primarily because we see the column headings for our
query components in the Data grid, and we do not see MDX syntax. We
also see that the Design Mode button is depressed, as depicted in Illustration
6.
Illustration 6: MDX
Query Builder in Design Mode (Compressed View)
We have accessed
the Data tab to add three date-related filters (each of which we shall
parameterize): one each for Calendar Year, Calendar Quarter and Calendar
Month. Lets first run the existing query to ascertain that all is in order
with regard to the retrieval of data.
2.
Click the Execute
Query button atop the Data tab, as shown in Illustration 7.
Illustration 7: Click
Execute Query to Retrieve Data with the Existing Query
...
Data
populates the Data pane, and appears similar to that depicted in Illustration 8.
Illustration 8: Initial
Query Operates Successfully in a Quick Test
Having
ascertained that the core query is in working order, we will now add the
date-related filters, as well as the vanilla parameters that we intend for
them to support.
3.
In the Metadata
pane, expand the Date dimension, as required.
4.
Within the
expanded Date dimension, expand the Calendar folder.
5.
Right-click
the Date.Calendar Year attribute hierarchy that appears within the expanded Calendar folder.
6.
Select Add
to Filter within the context menu that appears, as shown in Illustration
9.
Illustration 9: Select
Add to Filter in the Context Menu
The Date
dimension populates the Dimension column, within a newly created row
of the Filter pane. The Calendar Year attribute hierarchy
appears within
the Hierarchy column, as well.
7.
Leave the Operator
setting (to the immediate right of the Hierarchy column) at its default
of Equal.
8.
Place a
checkmark in the checkbox that appears in the Parameters setting (to the
immediate right of the Filter Expression setting), the rightmost column
in the new row of the Filter pane.
Our
completed filter-with-parameter entry appears in the new row of the Filter
pane, as depicted in Illustration 10.
Illustration 10: The
Filter Pane Reflects Our Addition of the Calendar
Year Attribute Hierarchy
We have
successfully added a new parameter, in conjunction with a filter,
within the primary dataset underlying our report. The addition of the parameter
within the graphical interface, as we have seen in previous articles, has
already begun a sequence of events that will trigger the automatic creation of
a corresponding Report Parameter. A
supporting dataset will also be created, as soon as we move to the Layout
tab. But first, we need to add entries within the Filter pane for
the additional Date filters we intend to add.
9.
Click the
leftmost box (which contains the placeholder <Select dimension>, as
before), within the Dimension column, in the bottom row of the Filter
pane (this time, beneath the box containing Date, which we added in
creating the first entry in the Filter pane earlier).
The dropdown selector is again enabled.
10.
Select Date
from the list that appears in the selector.
11.
Select Date.Calendar
Quarter of Year within the Hierarchy box to the immediate right,
using the selector that is built in.
12.
Leave the Operator
setting (to the immediate right of the Hierarchy setting) at its default
of Equal (checking the box will make it appear), as we did within the
first entry.
13.
Place a
checkmark in the checkbox that appears in the Parameters setting, once
again.
We
will now create the final parameterized filter within the Filter pane.
14.
In the bottom
row of the Filter pane, click the leftmost box within the Dimension
column, once again.
15.
Select Date
from the list that appears in the selector, as we did before.
16.
Select Date.Month
of Year within the Hierarchy box to the immediate right, using the
selector that is built in.
17.
Leave the Operator
setting (to the immediate right of the Hierarchy setting) at its default
of Equal, as we did within the first entry.
18.
Place a
checkmark in the checkbox that appears in the Parameters column, as we
have done with the previous row entries we have made in the Filter pane.
The Filter
pane, with all
four entries, appears
as shown in Illustration 11.
Illustration 11: The Four
Parameterized Filters in the Filter Pane
19.
Select Report
->Report Parameters ... from the main menu.
The Report
Parameters dialog opens, this time revealing four Report Parameters,
as depicted in Illustration 12.
Illustration 12: The Four
Report Parameters, including the Three New Date Parameters
We will
make adjustments to each of the new Report Parameter entries in later
steps, where it will be most efficient to accomplish all at one time.
20.
Click OK to
dismiss the Report Parameters dialog.
21.
Click the Layout
tab to transit temporarily away from the Data tab.
22.
Click the Data
tab, once more, to return immediately.
23.
Click the
downward selector arrow on the right side of the Dataset selector.
Based
upon our discussions in earlier articles, we see the behavior we have expected.
All five Datasets, including the originally existing two Datasets
and the three we have added via the enactment of parameterized filters
within the Filter pane (and subsequent departure from the Data
tab), now appear, as shown in Illustration 13.
Illustration 13: All
Datasets Appear as Expected ...
We have
successfully added three rudimentary parameters, based upon filters we
added within the Filter pane of the primary Dataset underlying
our report. The addition of the parameters within the graphical interface, as
we have verified, triggered the automatic creation of corresponding Report
Parameters. Supporting datasets were also created, as soon as we
moved away from the Data tab. The timing of the automatic creation of
objects can thus be understood; the sequencing becomes very important in the
event that we need to modify or remove any of the objects after they are
initially created and aligned.
Because
we will be referencing parameter support objects outside Reporting Services,
which we will create later in Analysis Services, we will not need the
automatically created parameter support datasets. We will remove these later
we can thus reference their structures, in the meantime, which are already aligned
to the existing Report Parameters, as a part of quick construction of
the Analysis Services replacements.
24.
Leave the
report open at the Data tab for later steps.
We are
now ready to transit to the Analysis Services Cube Designer to add
parameter support to the Adventure Works cube.
Practice
Practice:
Add Parameter Support Objects to the Analysis Services Layer
We will
next add parameter 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 depicted in Illustration 14.
Illustration 14: Opening
the Cube Designer ...
Add Calculated
Members in Analysis Services to Support Parameters 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, such as those we have examined in our preparation steps above,
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 earlier, we not only have the Report Parameters
in place, ready to be pointed to the calculated members we will create
in Analysis Services, but we also have a useful set of calculated
member definitions already in place upon which we can easily model our Analysis
Services calculated members. Lets take a look at the DateCalendarYear
dataset for starters, as we create the calculated members we will need
in Analysis Services. In our practice session, we will only create
those we actually intend to use in the associated parameters of our report,
ignoring the additional calculated members that are provided in the automatically
created datasets. We could, of course, include all, or create custom calculated
members that are entirely different, should the need arise in our local
business environments.
We review the 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 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 to different reports.
From within the now open Cube
Designer, we will begin creating calculated members to support
parameterization in our practice report.
1.
Select the Calculations
tab within the Cube Designer.
The Calculations tab opens.
2.
Click the
bottom entry in the Script Organizer pane, to anchor the cursor, as shown
(the name of the bottom item will likely differ in your own environment) in Illustration
15.
Illustration 15: Click
the Bottom Entry in the Script Organizer to Position the New Calculation
3.
Click the New
Calculated Member button atop the Calculations tab, as depicted in Illustration
16.
Illustration 16: 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.
4.
Leaving the Cube
Designer open at the Calculations tab, return to the Data tab
of the report with which we are working in Reporting Services.
5.
Click the
downward selector arrow on the right side of the Dataset selector.
6.
Select the DateCalendarYear
dataset within the selector.
The MDX for the dataset, which was
automatically generated as one of a chain of events triggered by our creation
of the parameterized filter for Year in the reports primary
dataset (ProductData), appears (rearranged, so as to make examination of
the various sections easier), as shown in Illustration 17.
Illustration 17: MDX for
the DateCalendarYear Dataset
Many will recognize the WITH MEMBER clauses as
representing the definition of various calculated members, which we also
specify within our SELECT clause for the purposes of supporting our parameter
defaults. These work the same as the calculated members that
appeared within the automatically generated dataset queries that we examined in Mastering
OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Pt. II.
NOTE: For and 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.
In this dataset, the following calculated members
are created:
-
ParameterCaption
-
ParameterValue
-
ParameterLevel
We will, as we have already noted, not be referencing
these fields in the DateCalendarYear Report Parameter
definitions, but the underlying MDX for the parameter caption and value
can be used as a template to create a calculated member in Analysis
Services, as we shall see. The following portion of the MDX is of
immediate use to us in our next steps.
WITH
MEMBER
[Measures].[ParameterCaption]
AS
'[Date].[Calendar Year]. CURRENTMEMBER.MEMBER_CAPTION'
MEMBER
[Measures].[ParameterValue]
AS
'[Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME'
7.
Return to the Calculations
tab of the Cube Designer.
8.
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]).
YearParamCaption
9.
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 YearParamCaption calculation
appears in the Calculations
Expressions pane,
with our input, as depicted in Illustration 18.
Illustration 18: The YearParamCaption
Calculation in the Calculations Expressions Pane
10.
Click the Check
Syntax button atop the Calculations tab, as shown in Illustration
19.
Illustration 19: Click
the Check Syntax Button
The Check Syntax message
box appears, indicating that the syntax check was successful, as depicted in Illustration
20.
Illustration 20: Our
Syntax Appears to Have Passed Checking ...
11.
Click OK to
dismiss the message box.
We
have successfully added support for the caption display of the Year
Report Parameter. We will now add a calculated member to house the
logic behind the value (the qualified MDX name) we wish to pass for
the same Report Parameter, based upon our caption selection at runtime.
12.
From our
current position within the Calculations tab, once again, click the
bottom entry in the Script Organizer pane, to anchor the cursor.
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 shown in Illustration 21.
Illustration 21: Move Up
and Move Down Buttons, atop the Calculations Tab
13.
Click the New
Calculated Member button, once again.
14.
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]).
YearParamValue
15.
Type (or cut
and paste) the following into the Expression box of the expanded Expression
section below:
[Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME
The new YearParamValue calculation
appears in the Calculations
Expressions pane,
with our input, as depicted in Illustration 22.
Illustration 22: The
YearParamValueCalculation in the Calculations Expressions Pane
16.
Click the Check
Syntax button atop the Calculations tab to ascertain syntactically
correct input, as we did for the first calculated member we added.
17.
Click OK to
dismiss the message box indicating the successful syntax check has occurred.
We
have successfully added support for the value display of the Year
Report Parameter. This means we have now added support for both the caption
and value fields of the Report Parameter within our report for Year.
We will next add, while we are under the hood within Analysis Services,
a similar pair of calculated members for each of the two remaining,
date-related Report Parameters we have selected for our practice
exercise, DateCalendarQuarterofYear and DateMonthofYear.
18.
Click the New
Calculated Member button, once again.
19.
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]).
QtrParamCaption
20.
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 QtrParamCaption calculation appears in the Calculations Expressions
pane, with our input,
as shown in Illustration 23.
Illustration 23: The QtrParamCaption
Calculation in the Calculations Expressions Pane
21.
Click the Check
Syntax button atop the Calculations tab to ascertain syntactically
correct input, as we did for the first pair of calculated members we
added.
22.
Click OK to
dismiss the message box indicating the successful syntax check has occurred.
23.
Click the New
Calculated Member button, once again.
24.
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]).
QtrParamValue
25.
Type (or cut
and paste) the following into the Expression box of the expanded Expression
section below:
[Date].[Calendar Quarter of Year].CURRENTMEMBER.UNIQUENAME
The new QtrParamValue calculation appears in the Calculations Expressions
pane, with our input,
as depicted in Illustration 24.
Illustration 24: The
QtrParamValue Calculation in the Calculations Expressions Pane
26.
Click the Check
Syntax button atop the Calculations tab to ascertain syntactically
correct input, as we did for the calculated member we added earlier.
27.
Click OK to
dismiss the message box indicating the successful syntax check has occurred.
28.
Click the New
Calculated Member button, once again.
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]).
MoParamCaption
30.
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 MoParamCaption calculation appears in the Calculations Expressions
pane, with our input,
as shown in Illustration 25.
Illustration 25: The
MoParamCaption 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 other calculated members we added.
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]).
MoParamValue
35.
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 MoParamValue calculation appears in the Calculations Expressions
pane, with our input,
as depicted in Illustration 26.
Illustration 26: The MoParamValue
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 first calculated member we added.
37.
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 shown in Illustration 27.
Illustration 27: 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 depicted in Illustration 28.
Illustration 28: Saving
Changes First ...
The Process
Database Adventure Works DW dialog appears, as shown in Illustration 29.
Illustration 29: 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 depicted in Illustration
30.
Illustration 30: 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 Parameters 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 Parameterization Support via New Datasets
We will
return to the Data tab of our still open report within Reporting
Services, where we will create three new datasets, one for each of
the Report Parameters for Year, Quarter, and Month,
within the following steps. (While we might achieve our ends through other
arrangements, we will provide a straightforward solution example in this
practice session, which we will build upon in subsequent articles).
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
downward selector arrow on the right side of the Dataset selector.
3.
Select <New
Dataset> within the selector, as shown in Illustration 31.
Illustration 31: Creating
a New Dataset
The Dataset dialog opens.
4.
Type the
following into the Name box of the dialog (replacing the default name of DataSet1).
AS_DateYrParams_Support
5.
Click OK
to dismiss the dialog and to open the graphical
query designer.
6.
Click the Design Mode
button to shift to the generic query designer, as depicted in Illustration 32.
Illustration 32: Shifting
to the Generic Query Designer ...
7.
Click the Refresh
button on the Data tab toolbar, as shown in Illustration 33.
Illustration 33: Refresh
the Environment for the Newly Processed Data Source ...
8.
Type (or cut
and paste) the following into the Query pane of the generic query designer, replacing the default, shell syntax:
SELECT
{[Measures].[YearParamCaption], [Measures].[YearParamValue] } ON COLUMNS,
{[Date].[Calendar Year].MEMBERS} ON ROWS
FROM
[Adventure Works]
9.
Click the Execute Query
(!) button to run the query against the Analysis Services data
source, as depicted
in Illustration 34.
Illustration 34: Execute
the New Query ...
The query
runs, and the data is returned, as shown in Illustration 35.
Illustration 35: The New
Dataset Appears ...
As we can
see, we have the workings of a good support dataset for the Year parameter
picklist both a caption and a value. Lets assume we will
want to eliminate the All Periods option within this (and the other)
datasets. One way to accomplish this can be handled with a query filter.
10.
Click the ellipses
(...) button to the immediate right of the Dataset selector
(currently showing our new AS_DateYrParams_Support dataset), as depicted
in Illustration 36.
Illustration 36: Click
the Ellipses Button to the Right of the Dataset Selector ...
The
tabbed Dataset dialog opens.
11.
Click the Filters
tab.
12.
Type the following syntax into
the Expression column of the Filters table, top row:
=CSTR(Fields!Calendar_Year.Value)
13.
Select the > (greater
than) sign in the adjacent Operator box of the top row.
14.
Type the following into the Value
column to the immediate right of the Operator column, in the top
row:
=0
The Filters
tab, with our input, appears as shown in Illustration 37.
Illustration 37: Our
Newly Added Filter ...
15.
Click OK to accept our
input, and to dismiss the Dataset dialog.
In
like manner, we will add a filtered dataset to support each of the two
remaining Report Parameters we have selected for our practice exercise, DateCalendarQuarterofYear
and DateMonthofYear.
16.
Click the
downward selector arrow on the right side of the Dataset selector, once
again.
17.
Select <New
Dataset> within the selector, as we did earlier.
The Dataset dialog opens.
18.
Type the
following into the Name box of the dialog (replacing the default name of DataSet1).
AS_DateQtrParams_Support
19.
Click OK
to dismiss the dialog and to open the graphical query designer.
20.
Click the Design
Mode button to shift to the generic query designer, as before.
21.
Type (or cut
and paste) the following into the Query pane of the generic query designer, replacing the default, shell
syntax:
SELECT
{[Measures].[QtrParamCaption], [Measures].[QtrParamValue] } ON COLUMNS,
{[Date].[Calendar Quarter of Year].MEMBERS} ON ROWS
FROM
[Adventure Works]
The query
appears in the Query pane of the query designer, as depicted in Illustration
38.
Illustration 38: The
Query within the Query Pane
22.
Click the Execute
Query (!) button to run the query against the Analysis Services
data source, as we
did for the earlier dataset.
The query
runs, and the data is returned, as shown in Illustration 39.
Illustration 39: The New
Dataset Appears ...
We will
again eliminate the All Periods selection from our parameter picklist
at runtime by adding a query filter entry.
23.
Click the ellipses
(...) button to the immediate right of the Dataset selector
(currently showing our new AS_DateQtrParams_Support dataset), as we did
before.
24.
Click the Filters
tab on the Dataset
dialog that opens
next.
25.
Type the
following syntax into the Expression column of the Filters table,
top row:
=CSTR(Fields!Calendar_Quarter_of_Year.Value)
26.
Select the >
(greater than) sign in the adjacent Operator box of the top
row.
27.
Type the
following into the Value column to the immediate right of the Operator
column, in the top row:
=0
The Filters
tab, with our input, appears as depicted in Illustration 40.
Illustration 40: Our
Newly Added Filter ...
28.
Click OK
to accept our input, and to dismiss the Dataset dialog.
29.
Click the
downward selector arrow on the right side of the Dataset selector, once
again.
30.
Select <New
Dataset> within the selector, as we did earlier.
The Dataset dialog opens.
31.
Type the
following onto the Name box of the dialog (replacing the default name of DataSet1).
AS_DateMoParams_Support
32.
Click OK
to dismiss the dialog and to open the graphical query designer.
33.
Click the Design
Mode button to shift to the generic query designer, as before.
34.
Type (or cut
and paste) the following into the Query pane of the generic query designer, replacing the default, shell
syntax:
SELECT
{[Measures].[MoParamCaption], [Measures].[MoParamValue] } ON COLUMNS,
{[Date].[Month of Year].MEMBERS} ON ROWS
FROM
[Adventure Works]
The query
appears in the Query pane of the query designer, as shown in Illustration
41.
Illustration 41: The
Query within the Query Pane
35.
Click the Execute
Query (!) button to run the query against the Analysis Services
data source, as we
did for the earlier two datasets.
The query
runs, and the data is returned, as depicted in Illustration 42.
Illustration 42: The New
Dataset Appears ...
We will
again eliminate the All Periods selection from our parameter picklist
at runtime by adding a query filter entry.
36.
Click the ellipses
(...) button to the immediate right of the Dataset selector
(currently showing our new AS_DateMoParams_Support dataset), as we did
before.
37.
Click the Filters
tab on the Dataset
dialog that opens
next.
38.
Type the following
syntax into the Expression column of the Filters table, top row:
=CSTR(Fields!Month_of_Year.Value)
39.
Select the >
(greater than) sign in the adjacent Operator box of the top
row.
40.
Type the
following into the Value column to the immediate right of the Operator
column, in the top row:
=0
The Filters
tab, with our input, appears as shown in Illustration 43.
Illustration 43: Our
Newly Added Filter ...
41.
Click OK
to accept our input, and to dismiss the Dataset dialog for the final
time.
We are now ready to hook up our
new datasets 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 Report Parameters at this juncture, where we
will reference each of the supporting datasets we created in the previous
subsection within the respective Dataset, Value field, and Label
field settings. We will also make a few additional
changes to the pre-existing settings to make the Report Parameters a bit
more user-friendly at runtime.
1.
From the main
menu, select Report ->Report
Parameters, as
depicted in Illustration 44.
Illustration 44: 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, change the Prompt setting
from Date.Calendar Year to the following:
Year
4.
Ensure that
all five checkboxes underneath the Prompt setting are unchecked.
5.
Within the Available
values section, underneath the Properties section, ensure that the From
query radio button is selected.
6.
Select AS_DateYrParams_Support
in the Dataset selector, as shown in Illustration 45.
Illustration 45: Select
AS_DateYrParams_Support as the Dataset ...
7.
Underneath the
Dataset selector, select the following in the Value field
selector:
YearParamValue
8.
Underneath the
Value field selector, select the following in the Label field
selector:
YearParamCaption
9.
Within the Default
values section, underneath the Available values section, ensure that
the radio button to the immediate left of Null is selected.
The
Properties, Available values, and Default values settings
for the DateCalendarYear Report Parameter appear, with our
modifications, as depicted in Illustration 46.
Illustration 46: DateCalendarYear
Report Parameter Settings, with Modifications
10.
Within the Parameters
list box, as before, click DateCalendarQuarterofYear.
The settings
for the selected Report Parameter appear.
11.
Within the Properties
section on the right side of the dialog, change the Prompt setting
from Date.Calendar Quarter of Year to the following:
Quarter
12.
Ensure that
all five checkboxes underneath the Prompt setting are unchecked.
13.
Within the Available
values section, underneath the Properties section, ensure that the From
query radio button is selected.
14.
In the Dataset
selector, select the following:
AS_DateQtrParams_Support
15.
Underneath the
Dataset selector, select the following in the Value field
selector:
QtrParamValue
16.
Underneath the
Value field selector, select the following in the Label field
selector:
QtrParamCaption
17.
Within the Default
values section, underneath the Available values section, ensure that
the radio button to the immediate left of Null is selected.
The
Properties, Available values, and Default values
settings for the DateCalendarQuarterofYear Report Parameter appear,
with our modifications, as shown in Illustration 47.
Illustration 47:
DateCalendarQuarterofYear Report Parameter Settings, with Modifications
18.
Within the Parameters
list box, once more, click DateMonthofYear.
The settings
for the selected Report Parameter appear.
19.
Within the Properties
section on the right side of the dialog, change the Prompt setting
from Date.Month of Year to the following:
Month
20.
Ensure that
all five checkboxes underneath the Prompt setting are unchecked.
21.
Within the Available
values section, underneath the Properties section, ensure that the From
query radio button is selected.
22.
In the Dataset
selector, select the following:
AS_DateMoParams_Support
23.
Underneath the
Dataset selector, select the following in the Value field
selector:
MoParamValue
24.
Underneath the
Value field selector, select the following in the Label field
selector:
MoParamCaption
25.
Within the Default
values section, underneath the Available values section, ensure that
the radio button to the immediate left of Null is selected.
The
Properties, Available values, and Default values
settings for the DateMonthofYear Report Parameter appear, with
our modifications, as depicted in Illustration 48.
Illustration 48: DateMonthofYear
Report Parameter Settings, with Modifications
26.
Click OK
to accept all our Report Parameter modifications, and to dismiss the Report
Parameters dialog.
As a
final cleanup item, we will remove the datasets created automatically by Reporting
Services to support the parameters we created as a part of preparation for
our practice session (when we added the parameterized filters to the
report). We can accomplish this by taking the following steps within the Data
tab.
27.
Click the
downward selector arrow on the right side of the Dataset selector.
We
note that the three automatically created datasets appear, including DateCalendarYear,
DateCalendarQuarterofYear, and DateMonthofYear.
28.
Select the DateCalendarYear
dataset within the selector.
29.
Once the
dataset is selected, click the Delete Selected Dataset button to the
right of the selector, as shown in Illustration 49.
Illustration 49: Deleting
an Unwanted Dataset ...
30.
Click Yes
on the message box that appears, asking if we are sure we want to perform the
deletion, as depicted in Illustration 50.
Illustration 50: Confirm
Wishes to Delete with a Yes ...
The
automatically created dataset DateCalendarYear disappears.
31.
Delete the DateCalendarQuarterofYear
and DateMonthofYear datasets in the same manner.
Having
created the parameter support structures within Analysis Services, we added
supporting datasets, based upon those structures, within our
sample report. We then referenced those datasets to the respective Report
Parameters, and, as a cleanup step, removed the datasets that Reporting
Services had automatically created for each parameterized filter we
set up as a part of our preparation for the practice session. We are now ready
to verify the effectiveness of our solution. We will verify that the
date-related Report Parameters now 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.
Leave the Product
Category prompt selection at its default of Bikes, Components.
3.
Click the
downward pointing arrow on the right side of the Year selector.
4.
Select CY
2004 within the Year parameter picklist.
We
notice the absence of an All Periods selection within the Year parameters
picklist as we might have expected, because we filtered out that row of the
respective dataset (as we did for the Quarter and Month parameter
supporting datasets).
5.
Select CY
Q2 from the Quarter parameter picklist.
6.
Select the
month of June in the Month parameter picklist.
We
notice at this stage that the parameter picklists do not cascade.
Moreover, it might occur to us that intelligent defaults would give our consumers
a better runtime experience. We will address these two items in an article
devoted specifically to these subjects.
7.
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
51.
Illustration 51: The Report
Parameters Operate Largely as Expected ...
Our
verification process has demonstrated the effective support of Report
Parameters through the creation and use of objects within the Analysis
Services layer of our BI solution. We will extend our examination of Analysis Services
supported parameterization yet further in a subsequent article, where we will
generate a solution to provide cascading picklists, and to meet the need
of our colleagues to provide intuitive parameter defaults to information
consumers at report runtime.
NOTE: Please consider saving the .rdl file we have created to this point
for use in the article that follows, so as
to avoid the need to repeat the preparation process we have
undertaken above.
8.
Experiment
further with the report, if desired.
9.
When finished
with the report, click the Layout tab.
10.
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 the next article within
our series.
11.
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.
Working
with the basic OLAP report we created in Reporting Services: Customize Automatically Created
Parameter Support Objects, 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 parameterization, including picklists,
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 opened the sample Report
Server project, AdventureWorks Sample Reports, and ascertained connectivity
of its shared Analysis Services data source. We continued our
preparation for the practice session by creating a clone of an existing sample OLAP
report, containing a Matrix data region, with which to perform our
practice exercises. We then made structural modifications to the clone report, to prepare
for our practice exercise session.
We 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. We then accessed
and employed the new Analysis Services parameter support components from
within Reporting Services. Throughout our article, we overviewed how the
various components of the parameter support solution we proposed are
tied together, as a part of our hands-on practice session, where we created and
aligned the necessary components within Analysis Services and Reporting
Services to support our runtime parameters. Finally, we previewed the report to observe the
effectiveness of our solution in runtime action, and looked ahead to our next
article, where we will examine the establishment of cascading picklists
into our current solution, together with the addition of intuitive parameter
defaults at runtime, for greater user-friendliness and overall operational
efficiencies.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.