Practice
Preparation
To reinforce our understanding of the basics we have covered
so far, we will use the MEMBER_KEY property in a hands-on example that
illustrates its operation. We will do so in a simple scenario that places MEMBER_KEY
within the context of meeting business requirements similar to those we
might encounter in our respective daily environments. The intent, of course, is
to demonstrate the operation of the MEMBER_KEY property in a
straightforward, memorable manner.
We will turn to the SQL Server Management Studio as a
platform from which to construct and execute the MDX we examine, and to view
the results datasets we obtain. If you do not know how to access the SQL
Server Management Studio in preparation for using it to query an Analysis
Services cube (we will be using the sample Adventure Works cube in
the Adventure Works DW Analysis Services database), please
perform the steps of the following procedure, located in the References
section of my articles index:
This procedure will take us through opening a new Query
pane, upon which we can create our first query within the section that follows.
Procedure: Satisfy Business Requirements with MDX
As a basis for our practice example, we will assume that we
have received another call from the Reporting department of our client, the Adventure
Works organization, requesting our assistance in meeting a specific report
presentation need. The client has implemented the integrated Microsoft BI
solution, and, in addition to using Analysis Services as an OLAP data
source, they use Reporting Services as an enterprise reporting
solution. The MDX we explore together, we are told, will thus be adapted and
extended for ultimate use within Reporting Services, in multiple
parameterized reports. Finally, we learn that the requirements will very
closely parallel those of a previous visit, documented in Intrinsic
Member Properties: The MEMBER_CAPTION Property , an earlier article in this series.
Per the original requirement, we are told, our client wishes
to meet a mechanical need within the reporting layer of an integrated BI
application. 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.
As we have noted numerous times
in articles within my MSSQL
Server Reporting Services series and elsewhere, there are two
primary types of parameters, type-in and picklist, which can be
mechanized through various means. Type-in parameters accept directly
typed user input for the value upon which the report is based. An example of
input might, for a report based upon an Analysis Services cube, consist
of the Unique Name for a given filter, say, for one of the Customer
Geography Cities within the Adventure Works sample cube.
The trouble with type-in
parameters is that they are subject to input error, and thus can fail to
produce the desired results if they are not precisely correct. This can be
particularly cumbersome for information consumers when the report is based upon
an Analysis Services cube, because the precise MDX qualified name
might present a typing challenge for some.
For this reason, the alternative
parameter type, the picklist, provides a more user-friendly experience.
A picklist presents a selection of choices to a consumer, based upon a
static file, a dataset from a larger data source, or through other means. The
picklist is often the tool of choice, because of its inherent elimination of
typing errors. A well-constructed picklist makes selection easy for the
consumer (who is not often pleased with a long scrolling process, or other
cumbersome method, as the initial step in generating a commonly requested
report). An investment in developing a good picklist often pays great
dividends in consumer satisfaction.
It is simple to generate a list
that provides all we need to support parameterization within Reporting
Services and other enterprise reporting applications. Lets get some
hands-on exposure to assembling a list whose primary objective is picklist
support. We will construct a dataset upon which the picklist
selections can be based, and then examine briefly an illustration of the
use of this dataset in MSSQL Server Reporting Services.
NOTE:
For details surrounding hands-on approaches (as you will see, they are Legion)
to constructing picklists in Reporting Services, see these articles in my MSSQL Server
Reporting Services series
here at Database Journal:
Lets assume, as a background scenario, that, our client
colleagues extend a request that we documented in Intrinsic
Member Properties: The MEMBER_CAPTION Property. As we recall, the request was a common one:
they wanted to provide picklist support within an OLAP report, which
they had constructed using MSSQL Server Reporting Services. The data source was to be the Adventure Works
sample cube, which accompanies an installation of MSSQL Server Analysis
Services 2005 (and with which most of us are familiar. Our colleagues
originally asked that the selector for a parameter picklist for
the Product Category display, within the selector, only the regular Name
for each Product Category each time an information consumer executed the
report. The only change, they tell us this time around, is within the Caption
for the parameter picklist.
The client representatives tell us that they want the new Caption
for the Product Category picklist to consist of a combination of the
Product Category Key, a numeral that is familiar to many
(particularly old school) Adventure Works information consumers, and
the regular Name for their Product Categories. The new Caption,
seen at report runtime, for the Product Category picklist will therefore
be a concatenation of the Key and the Caption / Name (with
a separator) for the Product Category. An illustration
would be a Caption for the Components Product Category, which
would appear as 2 Components.
While the focus of our article is the MDX required to meet
this request, and specifically upon the use of the MEMBER_KEY property
within an MDX query, the dataset that this query generates would be
added in Reporting Services Report Designer, among other steps, to meet
the requirement for parameterization within the designated OLAP report.
Lets create a query to generate the needed picklist support elements, and then
take a look at how we might use the data returned within the reporting layer.