Parameterization of MDX functions is
one of those things that cannot be delivered solely via graphical MDX Editor
but then, you really didnt think that you could avoid actually touching MDX at
all, did you? BI Architect Bill Pearson demonstrates a way to parameterize the
highly useful LastPeriods() function.
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.
About the Mastering OLAP Reporting Articles ...
One of the first things that become clear to early adopters of Reporting Services is that the knowledgebase for Analysis Services reporting with this tool is, to say the least, sparse. As I stated in my article, Mastering OLAP Reporting: Cascading Prompts (where I treated the subject of cascading parameters for Reporting Services 2000), the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of well-established, but expensive, solutions, such as Cognos PowerPlay, can be met in most respects by Reporting Services at a tiny fraction of the cost.
The vacuum of documentation in this arena, even taking into consideration the release of several books surrounding Reporting Services 2005 in recent months, continues to represent a serious undersell of Reporting Services, from an OLAP reporting perspective. I hope to contribute to making this arena more accessible for everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, we can rest assured that the OLAP potential in Reporting Services will contribute significantly to the inevitable commoditization of business intelligence, via the integrated Microsoft BI solution.
For more information about the Mastering OLAP Reporting articles, see the section entitled About the Mastering OLAP Reporting Articles in my article Ad Hoc TopCount and BottomCount Parameters.
Overview
In recent articles within this series, we have focused upon various aspects of parameterization within the Reporting Services environment. In some cases we have supported parameterization from structures completely contained within Reporting Services, and in others we have created parameter (predominantly picklist) support from within other layers of the integrated Microsoft business intelligence solution. As many of us are aware, enterprise reporting applications typically allow for parameterization (via what are sometimes known as prompts or parameter prompts) to enable information consumers to quickly find the information they need from a report. These parameters, whose values are physically passed to an axis specification or a slicer in the dataset query, often act to put filters into place on the fly; the filters are thus enacted when the consumer types or selects a value, or a series of values, at run time.
Because they allow information consumers to assume a role in guiding the delivery of information and add a self-serve component to the reporting experience - parameterization in general is a popular topic in the forums and newsgroups of most enterprise reporting applications. My first exposure to the concepts of parameterization was in working with very early versions of Cognos Impromptu. My continued application of those concepts over the succeeding years within Cognos, Crystal, Business Objects, MicroStrategy, and several more specialized applications, has given me a great appreciation for the opportunities that exist in the business environment for effective parameterization. Whether the reports are to be printed, displayed on screen, or any of the other options for production / deployment, its easy to see the value that parameterization can add in making the selection and delivery of enterprise data more focused and consumer-friendly.
While I have extended parameterization concepts into many arenas, none have captured my attention as much as their deployment within the integrated Analysis Services / Reporting Services pairing. These applications work together to provide business intelligence in a way that is powerful and highly flexible. Indeed, I often advise clients who are attempting to locate a consultant to implement the integrated Microsoft BI solution (composed of MSSQL Server, MSSQL Server Analysis Services, and Reporting Services) to seek a multidimensional architect - a practitioner who has a good working knowledge of each of the components, and who can determine where, among three or more possible logical layers, to place which components so as to optimize the system as a whole.
NOTE: For details surrounding hands-on approaches (as you will see, they are Legion) to the mechanics behind supporting parameterization, (including the generation of picklists) in Reporting Services, see these articles in MSSQL Server Reporting Services series here at Database Journal:
Throughout various articles of this series, we have generated simple lists to provide virtually all we need to support parameterization within Reporting Services and other enterprise reporting applications. In this article, we will perform a more detailed examination of the mechanics behind parameterizing an MDX function or more precisely, for parameterizing the variable argument within such a function. We will illustrate the process using the popular LastPeriods() function, which we introduced in MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions (a member of my MDX Essentials series at Database Journal), but the same logic can be extrapolated to many other similar MDX functions, as will become noticeable later in our article.
In this article, we will get hands-on exposure to parameterizing an MDX function, LastPeriods(), within a preexisting sample OLAP report. Beginning with the general concepts, we will continue into a practice session where we set up a scenario, within which we work with a basic OLAP report, to expose the steps involved. In examining the rudiments of specific function parameterization within an OLAP report containing a matrix data region, we will:
- 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 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 exercise session with parameters within a matrix data region;
- Perform a brief overview of the MDX LastPeriods() function, which we will use to support the stated reporting needs of a hypothetical client;
- Discuss the parameterization of MDX functions in general, and the LastPeriods() function specifically;
- Add the required query parameters to support date and function parameterization;
- Ensure the adequacy of automatically created datasets to support report parameters and meet business requirements (in Part II of this article);
- Add syntax to the TimeMonth dataset query to enforce cascading (in Part II of this article);
- Leverage the MDX LastPeriods() function, containing the month parameter placeholder(in Part II of this article);
- Discuss the interaction of the various components in supporting the runtime parameters that the end consumer sees (throughout Part I and Part II of this article);
- Discuss the results obtained with the development techniques that we exploit (throughout Part I and Part II of this article).
Parameterizing the LastPeriods() Function
Throughout many past
articles of the MSSQL Server Reporting Services series, we have leveraged
parameters within the context of MDX queries. Reporting Services
2000, initially intended as a component of the MSSQL Server 2005 BI
Release, was released to market early, as many of us are aware, with resulting
limitations in some of its capabilities (as most realistic practitioners would
expect). One of the challenges that faced many of us was the absence of an MDX
editor: while those of us who were comfortable with MDX syntax were not
impaired significantly (although we had to deal with circumstances that
accompanied parameterization in Reporting Services 2000, such as the
need to string our queries for passage from Reporting Services to the Analysis
Server, and the inability to test generate our datasets, once we
had parameters in place within the MDX queries), those who were already
challenged with MDX as a language almost certainly found no amusement in
dealing with the added mechanics. Reporting Services 2005 introduced
the MDX Query Builder, a tool that appeals, due to its on-off
flexibility, to most practitioners who are comfortable writing direct MDX
queries, as well as the MDX- challenged among those authors and developers who
need to work with OLAP data sources. This enhancement, unsurprisingly, changed
the way that many of us had become accustomed to writing the underlying MDX queries
for our Analysis Services reports.
Objective and Business Scenario
In this article, we will
perform an extended
examination of parameterization within
a copy of an existing sample Reporting Services 2005 report that we will create for this purpose. Our focus will be to parameterize a specific MDX function, LastPeriods(), to demonstrate the process behind supporting parameterization of an
index value we can supply in this and similar functions at runtime. We
will concentrate, in this article, upon the passage of our selection of a value
to the function, as well as with some of the general details of setting up parameters
and so forth in Reporting Services (we cover the latter in more detail
in various articles I have written for that specific purpose).
Our initial objective
will be to review the basics surrounding the LastPeriods() function, and then to perform an
exercise within which we put the function to work to meet the stated business
requirements of a hypothetical client.
A Quick Overview of the LastPeriods() Function
The LastPeriods() function,
according to the Analysis Services Books Online, returns a set of
members prior to and including a specified member. Almost as simple as
it sounds, the function returns (in its typical use within the context of a
time dimension) the set of periods beginning with the period that
follows (or lags) the specified member by a specified index
value, less one, up to and including the specified member. (If the
member is not specified in the function, [Time].[Time
Hierarchy].CurrentMember is defaulted.) Indeed, LastPeriods()
resembles PeriodsToDate(), which we explore in
MDX Time Series Functions, Part I:
PeriodsToDate() and Kindred Functions), and which returns all of
the members back to the beginning of a specified period, although the LastPeriods()
function returns the number of members that is specified by an index
value.
The index
(minus one) that is specified tells the function how far to look back for
purposes of the returned dataset. If the index is positive, the function
returns the set of members whose range is headed up by the member lagging
one less than the index value from the specified member, and that ends with
the member itself. If the index is negative, then LastPeriods()
returns the set of members that begins with the specified member itself, and
ends with the member leading (or ahead in time) by the negative
index value, minus one, from the specified member. Finally, an empty set is
returned if the index value is zero.
As we noted
in MDX Time
Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions, a member of my MDX Essentials series, the LastPeriods()
function allows us to meet numerous common business needs, including (and
especially) those that require that we return (individually or for
accumulation) values from, for example, several periods back, up to, and
including, the specified period. That specified member might be, for example,
a specific quarter for which we would like monthly values over the range of,
say, two quarters back through the current quarter. A calculation can be driven
for a several months activities, as another example, whose range is determined
by the beginning and ending points that result from the index we provide
(such a case will be the focus of our practice example in this article).
I have
found the LastPeriods() function to
be highly useful in both statistical and financial reporting. The capability
to specify number of periods to look back, for example, in patient readings
(both aggregated say by clinic or campus, etc. - and individual) can become
highly popular among, say, doctors who are tracking the effectiveness of new
treatments and the like, allowing them to extend the review range over a
variable number of months that they can specify at run time.
As we
discussed in MDX Time Series Functions, Part II: The OpeningPeriod
() and ClosingPeriod() Functions, balances are typically maintained up to the current
point in time / held at various points in time for many accounts whose
activity we might wish to analyze, such as Sales, Inventory, Purchases not to
mention various other accounts that contain values besides dollar amounts, such
as shares outstanding, headcount, various quantities, and so forth, in our systems.
The values most
likely to be of interest in typical uses of the LastPeriods() function will
often likely relate to deltas over the given periods. (As an
illustration, the total sales over each of a given group of months - itself the
delta of ending total sales between the point in time that we establish as
the specified member within the function, and the total sales value that
existed, in this example, at the earlier point in the year to which we are
looking back, and which is index 1 months back). LastPeriods()
is excellent for the derivation of total activity for the last (whatever
number) periods back, and, thus for a to date cumulative total of sorts.
Utilitarian beauty appears, again, in the use of the function with .CurrentMember,
etc. to gain a context sensitive calculation that can flex automatically as
time marches on. Running averages and other derivatives of the accumulated
totals are obvious easy byproducts, as well, given formulation within an
expression that includes LastPeriods().
Common
examples of scenarios where LastPeriods() might come in handy include
monthly totals / balances, over a few quarters, for the inventories of a group
of products we manufacture and sell, for perhaps comparison to the same amounts
for another group of our products, as an aid in determining whether to
discontinue production of a given item or items to allow us to compose a more
rapidly moving product mix. Another illustration might lie in meeting the need
to analyze activity, particularly over several summer months, in total monthly
quantity on hand, for a part that we stock in a Repair Parts account for use in
maintaining our air conditioning plant, to ascertain, perhaps, increased
demands for parts by an aging system.
MDX
provides an excellent means for accumulating these time-ranged activity values
over specified time frames. The LastPeriods() function affords us a
ready means to navigate to the first member of the range for which one endpoint
(the specified member) is defined and for which the opposite end of the range
is specified by the index. When we combine LastPeriods() with the
grouping capabilities found in the Reporting Services matrix data
region, we can extend its power into the flexible generation of number of
periods look back that we present within chart and summary reports
reports for which we can dictate the number of periods to look back
at runtime.
Lets
look at an illustration to further clarify the operation of LastPeriods().
Syntactically, the index and the specified member are placed
within the parentheses to the right of LastPeriods(), as shown in the
following illustration:
LastPeriods(«Index»[, «Member»])
The function returns the set
of members prior to (by «Index» - 1 member-places), and including, «Member». The following simple example
expression:
LastPeriods (11, [Time].[1998].[Q3].[8])
would return the
following set of month-members:
{[Time].[1997].[Q4].[10],
[Time].[1997].[Q4].[11],
[Time].[1997].[Q4].[12]
[Time].[1998].[Q1].[1]
[Time].[1998].[Q1].[2]
[Time].[1998].[Q1].[3]
[Time].[1998].[Q2].[4]
[Time].[1998].[Q2].[5]
[Time].[1998].[Q2].[6]
[Time].[1998].[Q3].[7]
[Time].[1998].[Q3].[8]}
As we
shall see, parameterization of the index within the LastPeriods() function
lies at the heart of the procedure we will undertake within our practice
session below. We will detail the steps within a sample report that we will
clone to save time, focusing largely upon setup of the parameter mechanism
within the report dataset, together with its linkage in the corresponding Report
Parameter.
Parameterizing LastPeriods()
I
often parameterize the LastPeriods() function within a Reporting
Services application I am developing as part of a larger implementation of
the integrated Microsoft Business Intelligence solution for a given client.
While this is only a tiny part of the overall structure we typically assemble
for a combined OLAP and relational reporting system, it will provide an
interesting glimpse of the much larger population of opportunities that I find
daily in working with these powerful analysis and reporting tools.
In the
following sections, we will perform the steps required to add parameterized number
of periods of look back to an OLAP report. To provide a report upon which
we can practice our exercises, we will begin with the Sales Reason
Comparisons sample report that, among other samples, accompanies the
installation of Reporting Services 2005, and which is based upon the
ubiquitous Adventure Works cube, contained within the Analysis
Services database named Adventure Works DW, that comes along with an
installation of Analysis Services 2005.
For
purposes of our practice procedure, we will assume that information consumers
within the Marketing office of the Adventure Works organization have
expressed the need for modifications to the existing Sales Reason
Comparisons report. The existing capabilities in the report meet some of
their analysis needs, but recent requirements for a more flexible presentation
of the data has resulted in our being called to assist with creating a custom
report that meets an immediate, specialized need in a user-friendly manner.
In discussing their
requirements in detail, representatives of the Marketing department state that
ongoing analysis operations would be dramatically enhanced if they could simply
specify, at report run time, the number of months displayed. That is, instead
of seeing the fixed number of months (thirty-seven total months data
is available, as we shall see), our client colleagues would like to be able to specify
the number of months for the report to look back from the most recent
operating month, July 2004.
Our colleagues inform
us that the current report contains a filter that limits the data
presented to a couple of operating years. In addition to the requirement that
we support flexible presentation of number of months to look back, they
request that we open the available periods for reporting - to the full range
of operating data that is physically available within the cube.
The consumers request some
additional changes to the report itself, primarily that we remove the existing report
parameter, as well as its underlying support mechanisms, which allows us to
filter products by Product Category.
The new report will
also be simpler with regard to other considerations: Instead of three measures
in the column axis, we will only display two, Internet Orders and Internet
Sales Amount. Moreover, as the report will apply to the Adventure Works
organization as a whole, and will not break out measures by Territory Group,
we will eliminate this existing grouping within the matrix data region.
Finally, we will group the measures by Month, instead, so as to present
both measures for a given month or a range of months, depending upon our
selection of number of months look back at runtime, which we have
discussed earlier. We will add Year and Month report parameters,
which will allow information consumers to run the report as of any month
not just the latest month for which values are stored within the Analysis
Services database.
These changes will
result in a multi-purpose report, the months presented by which can be dictated
on the fly by the easy, ad hoc input of varying ranges of months to
look back, as well as the year / month from which to look back. This
will allow the analysts to rapidly meet differing conditions and analysis
needs. As is often the case with the addition of parameterization, the
organization will ultimately be able to accomplish more with fewer reports.
As part of our typical
business requirements gathering process, we listen attentively to the details,
formulating, in the background, an idea of the steps we need to take in
modifying a copy of the report to produce the desired results. Then, having
grasped the stated need, and having confirmed our understanding with the
intended audience, we begin the process of modifying the Sales Reason
Comparisons report to satisfy the information consumers.
Practice
Our
first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement the
newly required parameter.
The focus of our
efforts will be the addition of this parameter into an OLAP report containing
a matrix data region (the mechanics behind adding the capability,
not the design of the report itself). To save time, we will be working with a
simple, pre-existing sample report in reality, the business environment will
typically require more sophistication. The process of setting up the basic
parameter is the same in real world scenarios, with perhaps a more complicated
set of underlying considerations. (I virtually
never encounter a client reporting requirement that does not involve at least
basic parameterization.)
We
will perform our practice session from inside the MSSQL Server Business
Intelligence Development Studio. For more exposure to the Business
Intelligence Development Studio itself, and the myriad design, development
and other evolutions we can perform within this powerful interface, see other
articles in this series, as well as within my Database
Journal series Introduction to MSSQL Server Analysis
Services. In
this article, we will be commenting only on the features relevant to our
immediate practice exercise, to allow us to keep to the focus of the article
more efficiently.
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 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 exercise. Creating a clone of the project 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, 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: 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
no parameters. Lets
open the report in Layout view (for those of us not already there) and
make the necessary settings to place it into a state upon which we can commence
our 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 1, as
necessary.
Illustration 1: Opening
the New Report ...
DBJ_OLAP_Report.rdl
opens in Layout
view.
We will
start with the Data tab.
We
enter the Data tab, where we will remove a handful of components that we
do not need for our practice session. We will accomplish this from the
perspective of the MDX Query Builder, the main components of which (in Design
mode) are labeled in Illustration 2 below.
Illustration 2: The MDX
Query Builder Design Mode
4.
With the Dataset
selector, select the ProductList dataset, as depicted in Illustration
3.
Illustration 3: Select
the ProductList Dataset ...
5.
Once the Dataset
loads, click the Delete button, as shown in Illustration 4.
Illustration 4: Deleting
the Unneeded Dataset ...
6.
Click Yes
on the Microsoft Report Designer warning message dialog that appears
next, as depicted in Illustration 5.
Illustration 5: Confirm
Intention to Delete ...
The
primary (and sole remaining) dataset, ProductData opens.
7.
Within the Query
pane of the MDX Query Builder, right-click the column heading for
Internet Total Product Cost.
8.
Click Delete
Internet Total Product Cost from the context menu that appears, as shown
in Illustration 6.
Illustration 6: Deleting
the Unwanted Measure from the Dataset
9.
Within the Calculated
Members pane (bottom left corner of the Data tab), right-click the
sole Calculated Member, Profit.
10.
Click Delete
on the context menu that appears, as depicted in Illustration 7.
Illustration 7: Deleting
the Calculated Member
11.
Click Yes,
when asked, Are you sure ....
12.
Right-click
the single entry in the Filter pane (directly atop the Query
pane).
13.
Click Delete
to eliminate the existing Product Category Parameter from the Filter
pane, as shown in Illustration 8.
Illustration 8: Deleting
the Product Parameter from the Filter Pane
We
will make an addition to the Dataset next.
14.
Within the Metadata
pane, expand the Date dimension, by clicking the + sign to
its immediate left.
15.
Expand the Fiscal
folder that appears within the Date dimension.
16.
Expand the Fiscal
hierarchy (labeled Date.Fiscal) within the Fiscal folder.
17.
Drag the Month
level into the Data pane, dropping it to the left of the Sales Reason
column, as depicted in Illustration 9.
Illustration 9: Adding
Months to the Dataset
The
new Month column appears, as desired. Having made the necessary changes on the Data tab, we are ready
to move to the Layout tab, where we can conclude our preparatory
modifications to the report file.
18.
Click the Layout
tab, as shown in Illustration 10.
Illustration 10: Click
the Layout Tab
19.
On the Layout
tab, within the matrix data region, select the value appearing
underneath the Internet Total column heading.
20.
Right-click
the value in the text box (the value appears as =Sum(Fields!Internet_Total_Product_Cost.Value)
).
21.
Select Delete
from the context menu that appears, as depicted in Illustration 11.
Illustration 11: Delete
the Value for Internet Total Product Cost
22.
Right-click
the upper left-hand corner of the Matrix Data Region (the gray square).
The
gray column and row bars disappear, as a light, patterned outline forms around
the matrix data region, and the context menu appears.
23.
Select Properties
from the context menu, as
shown in Illustration 12.
Illustration 12: Select
Properties from the Context Menu ...
The Matrix
Properties dialog appears, defaulted to the General tab.
24.
Click the
Groups tab.
25.
In the Columns
section of the Groups tab (in the lower half of the tab), select the
middle group, named matrix1_Sales_Territory_Group.
26.
Click the
Edit button, as depicted in Illustration 13.
Illustration 13: Editing
the Matrix1_Sales_Territory_Group Column Group ...
The Grouping
and Sorting Properties dialog appears, defaulted to its General tab.
27.
Atop the General
tab, change the existing Name to the following:
matrix1_Month
28.
Click the row
containing the Expression value (currently the expression is Fields!Sales_Territory_Group.Value),
within the Group on list, to enable the selector.
29.
Select =Fields!Month.Value
within the selector, as shown in Illustration 14.
Illustration 14:
Replacing the Existing Group Expression ...
30.
Click OK,
to accept our modifications, and to close the Grouping and Sorting Properties dialog.
31.
Click OK
on the Groups tab, to close the Matrix Properties dialog.
32.
Once again on
the Layout tab, within the Matrix Data Region, right-click the
value appearing underneath the Internet Orders column heading (the
leftmost of the two remaining value cells).
33.
Select Properties
on the context menu that appears, as depicted in Illustration 15.
Illustration 15: Modifying
Properties for the Count Value ...
34.
On the Textbox
Properties dialog that next appears, click the Format tab.
35.
Replace the
existing Format code setting (in the upper left corner of the Format
tab) with the following string:
#,###
Here
we are simply changing the existing format to one more appropriate for a count
value. The Format code appears on the Format tab of the Textbox
Properties dialog as shown
in Illustration 16.
Illustration 16: Replacing
the Existing Format Code ...
36.
Click OK
to accept our modifications, and to dismiss the Textbox Properties dialog.
We next
need to change the column heading associated with the Group we modified
earlier, so that it reflects Months, versus the previous grouping
criteria of Sales Territory Group.
37.
Right-click
the text box containing the column label (immediately above the two measure
values column labels) in the Layout tab (the text box currently
contains the expression =Fields!Sales_Territory_Group.Value).
38.
Select Expression...
from the context menu that appears, as depicted in Illustration 17.
Illustration 17:
Modifying the Primary Column Label
The Expression
Editor opens.
39.
Replace the
expression in the upper portion of the Editor with the following:
=Fields!Month.Value
Recall
that the intent is to label the column to reflect the nature of the Group we
have put in place: Months instead of Sales Territory Group.
40.
The Expression
Editor appears, with our modification, as shown in Illustration 18.
Illustration 18: The
Expression Editor with Our Substitution in Place
41.
Click OK
to accept our modification, and to dismiss the Expression Editor.
42.
Click the
column heading for which we have just changed the expression, if necessary, simply
to select the textbox.
43.
Change the Font
Size, in the Format toolbar atop the design environment, from 12pt
to 10pt, to make the presentation more compact, as depicted in Illustration
19.
Illustration 19: Change
the Font to 10pt ...
All that
remains to complete our preparatory steps is to remove the pre-existing Report
Parameter. Recall that one of our earlier steps was to remove the Product
entry from the Filter pane (the sample report from which our report was
cloned came equipped with a parameterized Product filter). This entry
had been established with the Parameter checkbox selected, a common way
to create a parameterized filter, as we have seen in various other
articles of the MSSQL Server Reporting
Services series. Because parameterization was selected for the
Product filter, it follows that a Report Parameter might well have
been created at the time the entry was made. We will now remove the
remaining Report Parameter.
44.
From the main
menu, select Report ->
Report Parameters...,
as shown in Illustration 20.
Illustration 20: Select
Report -> Report Parameters...
The Report
Parameters dialog opens, and we see a single Report Parameter
listed.
45.
Ensure that
the ProductCategory Parameter is selected.
46.
Click the Remove
button, as depicted in Illustration 21.
Illustration 21: Removing
the Residual Report Parameter ...
47.
Click OK
to accept our modifications, and to dismiss the (now empty) Report
Parameters dialog.
We will
execute the report to ascertain that our modifications are complete, and that
we have a working report for the practice session that follows.
48.
Click the Preview
tab, as shown in Illustration
22.
Illustration 22: Click
the Preview Tab to Execute the Report
The Report
is being generated message briefly appears, and then the report displays.
The modified report appears as partially depicted in Illustration 23.
Illustration 23: The
Modified Report (Partial View)
We note
that the fiscal months July 2001 through July 2004 appear
(representing a total of 37 months, the earliest of which are relatively
sparse with regard to data).
Our
report is now ready for the practice exercise, which we will begin in the next
section.
Procedure: Adding and Parameterizing the Date Dimension within the LastPeriods() Function in
Reporting Services 2005
As we
noted earlier, our intent within this article is to examine the detailed
mechanics involved in adding and parameterizing a single MDX
function, LastPeriods() within our report. Moreover, we will
parameterize the Date dimension itself (both Fiscal Year and Month)
so as to allow our information consumers to declare an as of date within the
report (from which to look back, as it were), rather than forcing them
to rely upon current month (or the last month with activity within the cube)
as the starting point from which they look back. We will accomplish the initial
steps from the Data tab, to which we will return in the following steps.
1.
Click the
Data tab, once again
We
return to the Data tab, where this time we will ultimately replace some
of the syntax that currently exists within the MDX query - syntax which
has, up until now, been assembled graphically via the MDX query designer
in Design mode.
As most of us know,
when we create a dataset from an Analysis Services data source, Report
Designer initially offers us the MDX query designer in Design mode.
We can work within Design mode to interactively build an MDX query using
graphical elements. Design mode affords us the capability to select a cube
and to drag numerous objects to the Data pane, including:
-
Dimensions
-
Dimension Attributes
-
Levels
-
Hierarchies
-
Measures
-
Key Performance Indicators
(KPIs).
We can also add calculated
members, set default values for variables (we will expose a
specific instance of this in our practice exercise, as part of the focus of our
article), and automatically see result sets returned for the queries we
build (particularly useful as we make changes within the Query pane) to
the Data pane. We can set filters (like the one we removed in our
earlier steps to prepare our OLAP report clone for use within our practice
session), to limit the data retrieved from the data source by the query,
and define parameters.
As many of us have no
doubt already learned, the need for dealing with direct MDX has not
disappeared. This can be particularly true when the need arises to tailor an
MDX query to meet (especially more sophisticated) business requirements within Reporting
Services. One example will be illustrated within the focus of our article
the use of any but the most basic (those that are implicit within the
arrangement of objects within the Data pane of the MDX functions within
the MDX query designer in Design mode) MDX functions. (We
illustrate other examples where we must use the MDX query designer in Query
mode in numerous other articles of the MSSQL Server Reporting Services series.)
To view
or edit MDX query syntax directly, we must switch to Query mode by clicking the Design Mode
toggle button on the toolbar. The Design
Mode toggle button appears within
the Report Designer as shown in Illustration 24.
Illustration 24: Design
Mode Toggle Button in Report Designer
2.
Click the Design Mode toggle button on the toolbar.
The syntax
for the underlying query appears, as depicted in Illustration 25.
Illustration 25: MDX
Syntax Appears within the Query Pane of the Query Mode
What we
need to do, to meet the requirements of our client colleagues, includes the
following (in the order specified):
-
Add a query
parameter for each of Year, Month, and Span of Lookback;
-
Ensure that
the datasets automatically created for each of the newly added query
parameters are appropriate, adjusting as necessary to support report parameters
in meeting the business requirement;
-
Add the syntax
to the query to leverage the MDX LastPeriods() function, which will
enclose the parameterized input, ultimately, of the three query
parameters, to achieve the retrieval / presentation dictated by the
selections of information consumers at runtime.
We will
accomplish these steps within the following subsections.
Add the
Required Query Parameters to Support Date and Function Parameterization
1.
Click the
Query Parameters
button on the toolbar, as shown in Illustration 26.
Illustration 26: Click
the Query Parameters Button atop the Query Pane ...
The Query
Parameters dialog appears as depicted in Illustration 27.
Illustration 27: The
Query Parameters Dialog Appears ...
Here we
add the three variables we will be associating, via the query syntax,
with the report.
NOTE: Simply typing the placeholders into the query syntax (preceded by an
@ sign in Reporting Services 2005) first does not automatically
create the query parameters or the associated report parameters
contrary to some of the documentation (and other sources) I have seen on the
Web. Simply typing in the placeholder(s) first in the query, and following
this with an attempt to run the query via the Execute Query button,
results in a message informing us of query preparation failure. The
message relates that the query contains the [term following the @ sign in
our placeholder] parameter, which is not declared. An example of this
message, for a parameter placeholder added to the query syntax as @LookBackSpan,
for which no entry has been made in the Query Parameters dialog, appears
as shown in Illustration
28.
Illustration 28: Message
Noting Query Preparation Failure, Due to an Undeclared Parameter ...
2.
In the single
row of the Query Parameters dialog, in the table underneath the caption
Specify details of the parameters defined in the query, click
the leftmost cell (containing the placeholder <Enter Parameter>)
to select it, as shown
in Illustration 29.
Illustration 29: Select
the Cell Marked <Enter Parameter> ...
3.
Type the
following into the selected cell:
TimeYear
4.
Within the
cell to the immediate right of the Parameter cell just populated,
labeled Dimension, select Date, as depicted in Illustration 30.
Illustration 30: Select
the Date Dimension ...
5.
Within the
cell to the immediate right of the Dimension cell just populated,
labeled Hierarchy, select Date.Fiscal, as shown in
Illustration 31.
Illustration 31: Select
the Date.Fiscal Hierarchy ...
6.
Leave the
Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy
cell just populated, unchecked.
7.
Using the
selector in the Default cell (the rightmost cell in the row within which
we have been working), expand the All Periods entry by clicking the +
sign to its immediate left.
8.
Select the FY
2005 member, as depicted
in Illustration 32.
Illustration 32: Select FY
2005 as the Default ...
NOTE: Assigning a Default is a requirement to allow us
to test the dataset query anytime we add query parameter
placeholders to the query syntax. Doing so also initially populates the default
of a subsequently created report parameter, as we shall see.
9.
Click OK
to accept our selection, and to dismiss the Default selector.
The row
within which we have been working in the Query Parameters dialog appears
as shown in
Illustration 33.
Illustration 33: The
Query Parameters Dialog with Our Input ...
Next,
lets add a query parameter for Month.
10.
Once again
within the Query Parameters dialog, in the newly appearing second row of
the table underneath the caption Specify details of the parameters defined
in the query, click the leftmost cell (containing the placeholder
<Enter Parameter>) to select it, as we did in the first row of the
table for the TimeYear parameter.
11.
Type the
following into the selected cell:
TimeMonth
12.
Within the Dimension
cell, again to the immediate right of the Parameter cell, select Date,
as we did in the first row.
13.
Within the Hierarchy
cell, once more to the immediate right of the Dimension cell, select Date.Fiscal,
as we did in the row above.
14.
Leave the
Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy
cell just populated, unchecked, once again.
15.
Using the
selector in the Default cell (the rightmost cell in the row within which
we have been working), expand the All Periods entry by clicking the +
sign to its immediate left.
16.
Expand the FY
2005 member.
17.
Expand the H1
FY 2005 member that appears underneath the expanded FY 2005 member.
18.
Expand the Q1
FY 2005 member that appears underneath the expanded H1 FY 2005
member.
19.
Select the July
2004 member, as depicted
in Illustration 34.
Illustration 34: Select July
2004 as the Default ...
20.
Click OK
to accept our selection, and to dismiss the Default selector.
Next, we
will add a query parameter for the span of desired "look back"
months (for purposes of our illustration, we will select six months
keeping in mind that the default here, in effect, serves only to
provide a value to substitute for the associated query parameter value
at run time (as well as for the associated report parameter default).
This parameter will have some different settings than the more typical
settings above including blanks in a couple of places (a fact that is not
well documented ...).
21.
Once again
within the Query Parameters dialog, in the newly appearing third row of
the table underneath the caption Specify details of the parameters defined
in the query, click the leftmost cell (containing the placeholder
<Enter Parameter>) to select it, as we did in the rows of the table
for the two parameters we have already added above.
22.
Type the following
into the selected cell:
LookBackSpan
23.
Leave the Dimension
cell (again to the immediate right of the Parameter cell) blank.
24.
Leave the Hierarchy
cell, (again, to the immediate right of the Dimension cell) blank.
25.
Leave the
Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy
cell just populated, unchecked, as we have done in the TimeYear and TimeMonth
rows above.
26.
Click within
the Default cell (the rightmost cell in the row, once again within which
we have been working), to place the cursor there.
27.
Type the
following into the selected cell:
6
as shown
in Illustration 35.
Illustration 35: Input 6 as the Default Span for
Look Back ...
28.
Click the Parameter
cell on the same row (where we typed LookBackSpan earlier), once
again, to set the 6 within the Default cell.
The third
row, within which we have been working in the Query Parameters dialog,
appears as depicted
in Illustration 36.
Illustration 36: The
Query Parameters Dialog with Our Input ...
29.
Click OK
to accept our selection, and to dismiss the Query Parameters dialog.
We are
returned to the Data tab, where we will make some modifications to the query
syntax in the section that follows. Before doing so, lets quickly confirm
some events that have transpired behind the scenes, as a part of our creating
the three query parameters in the preceding steps of this section.
30.
Select Report
-> Report Parameters ..., from the main menu, as shown in Illustration 37.
Illustration 37: Select
Report --> Report Parameters ...
We note
the appearance of the three new query parameters within the Parameters
pane of the Report Parameters dialog, which appears next, as depicted in Illustration 38.
Illustration 38: The
Report Parameters Dialog with New Entries ...
31.
Click OK
to dismiss the Report Parameters dialog for now.
32.
Click the
downward pointing arrow on the Dataset selector (currently displaying
the ProductData selection) within the Data tab.
We note
that only the ProductData selection appears within the selector, as shown
in Illustration 39.
Illustration 39: The
Single Existing Dataset within the Selector ...
33.
Click the Layout
tab in the Report Designer.
34.
Click the Data
tab immediately to return to our former position.
35.
Click the
downward pointing arrow on the Dataset selector, once again.
We note
that the TimeYear and TimeMonth datasets (automatically created
when we moved to the Layout tab) join the ProductData selection
within the selector, as depicted in Illustration 40.
Illustration 40: The
Newly Created Datasets Also Appear within the Selector ...
The
datasets we see have been automatically created to support parameters that we
have defined with simple datasets that we may or may not choose to use out of
the box. (See the various articles I have published surrounding parameters
in Reporting Services 2005, within my MSSQL Server
Reporting Services series at Database Journal.
NOTE: It is important to remember that, although the report
parameter and its underlying Dataset are created automatically
when we create the underlying query parameters within the Query
Parameters dialog of the MDX query designer (Query mode), removal of
a corresponding, supporting row from the Query Parameters dialog will
not produce an opposite effect. The Report Parameter and the Dataset
will remain until they are manually removed. Moreover, the disablement of
parameterization in this manner from within the Query Parameters dialog,
followed by re-creation of the affected row will, unless we intervene before we
recreate the Parameter, result in the creation of two Report
Parameters.
One might
wonder why the third query parameter we created, LookBackSpan, did not
trigger the creation of a third dataset. Because the simple parameter is based
upon a mere number, in this case, and not upon a dimensional construct, no
basis exists within the auto create capability upon which to build the
dataset. We will, of course, be able to manage this via another avenue, as we
shall see in a later section in Part II.
Our first step in Part II, will be
to overview the two datasets that were created, and ensure their adequacy to
support the associated date-related report parameters in meeting the
business requirements of our client colleagues. We will then conclude by
working with underlying dataset queries to enforce cascading, and to
support the parameterized look back capability that is the primary focus of
this article.
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.
36.
Experiment
further with the report, if desired.
37.
When finished
with the report, click the Layout tab.
38.
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.
39.
Select File
-> Exit to leave the design environment,
when ready.
Conclusion ...
In this article, we began another extended examination of Parameters
in Reporting Services 2005. This time, we set out to obtain some
hands-on exposure to parameterizing an MDX function, LastPeriods(), within
a preexisting sample OLAP report. Beginning with the general concepts, we continued
into a practice session where we set up a scenario within which we work with a
basic OLAP report, to expose the steps involved. In examining the rudiments of specific function
parameterization within an OLAP report containing a matrix data
region, we performed
a brief overview of the MDX LastPeriods() function, which we stated that
we would use to support a stated reporting need of a hypothetical client. We discussed the parameterization of MDX functions
in general, and the LastPeriods() function specifically.
We
next prepared a copy of a sample report sourced from Analysis Services, containing a Matrix data
region, with which to perform our practice exercise. We then added the required query parameters to support date
and function parameterization, which we stated we would complete
in Part II of this article. Throughout our various
preparation and other steps, we discussed the interaction of the various components in
supporting the runtime parameters that the end consumer sees, as well as
touching upon the results obtained within the development techniques that we
exploited.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.