Procedure: Use the MEMBER_VALUE property in another
example to Provide Parameter Picklist Support within the Reporting Layer
Lets
look at an example that expands upon our first, this time 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.
In general, 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 Postal Code for a given filter, say, for one of the Customer
Geography Cities in the list we created earlier. (Cities routinely have
multiple postal codes.)
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, even with a list like we generated
above with the Unique Names mapped to the English names for various
filter selections, 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.
The list we have generated above
provides virtually all we need to support parameterization within Reporting
Services and other enterprise reporting applications. Lets do another
example, this time with the primary objective of picklist support. We
will construct a dataset upon which the picklist selections can be
based, and then overview 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, in contacting
us to say that they are happy with the index we have provided for the developer
as outlined in our previous example, the Reporting department with which we
worked earlier asks for further assistance of a similar nature. Their next
request is a common one: they want to provide picklist support within
an OLAP report, which they have constructed using MSSQL Server Reporting
Services. The data source is, once again, the Adventure Works sample
cube that accompanies an installation of MSSQL Server Analysis Services 2005
(and with which most of us are familiar). The consumers want the selector for
the parameter picklist to display the Postal Codes for the organizations
Customer list each time an information consumer runs the report while
the Report Parameter is to reference (and thus pass) the unique, MDX
- qualified, name that corresponds to the selected Postal Code to Reporting
Services for purposes of filtering the report.
While the focus of our article is the MDX required in
meeting this request, and specifically upon the use of the MEMBER_VALUE
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 list, and then take a look at how
we might use the data returned within the reporting layer.
Our initial approach is quite similar to the previous
example its in the intended end use of the returned data where we do
something different. We again have a need that we can readily answer using
the MEMBER_VALUE property in conjunction with a relative function, .CurrentMember.
The solution also includes the MEMBER_UNIQUE_NAME property. We will be
targeting the Postal Code column in the resulting dataset (well call it
Customer Postal Code Postal Code) for the name that is displayed in
the selector for the parameter picklist. The Unique Name
column of the returned dataset (the qualified MDX name for each Postal
Code attribute member of the Customer dimension, Customer
Geography attribute hierarchy), which we call Customer Postal Code - MDX
Qual Name in the query we construct, will serve as the value that is
actually passed to the cube in the MDX of the query. The happy result is that
we insulate report consumers from the MDX altogether, while providing them ad
hoc selection of a simple Postal Code upon which to filter the report
data.
Our first step is to construct a query to return the
requested Postal Code list, presenting the selector Codes and Unique
Names in two side-by-side columns. The corresponding Postal Code
attribute members of the Customer dimension (Customer Geography attribute
hierarchy) will inhabit the row axis, as we shall see.
1.
Select File
--> New from the main menu, once again.
2.
Select Query
with Current Connection from the cascading menu that appears next, as shown
in Illustration 18.
Illustration 18: Create a New Query with the Current
Connection ...
A new
tab, with a connection to the Adventure Works cube (we can see it listed
in the selector of the Metadata pane, as expected) appears in the
Query pane.
3.
Type (or cut
and paste) the following query into the Query pane:
-- MDX070-02 Using MEMBER_VALUE and MEMBER_UNIQUE_NAME
-- to generate a postal code / unique name list within the data grid
WITH
MEMBER
[Measures].[Customer Postal Code - Postal Code]
AS
'[Customer].[Customer Geography].CurrentMember.MEMBER_VALUE'
MEMBER
[Measures].[Customer Postal Code - MDX Qual Name]
AS
'[Customer].[Customer Geography].CurrentMember.MEMBER_UNIQUE_NAME'
SELECT
{[Measures].[ Customer Postal Code - Postal Code],
[Measures].[Customer Postal Code - MDX Qual Name]}
ON AXIS(0),
{DESCENDANTS(
[Customer].[Customer Geography].[Country].&[United States],
[Customer].[Customer Geography].[Postal Code]
)}
ON AXIS(1)
FROM
[Adventure Works]
The Query pane appears,
with our input, as depicted in Illustration 19.
Illustration 19: Our Second Query in the Query Pane ...
4.
Execute the
query by clicking the Execute (!) button in the toolbar.
The Results pane is populated by Analysis
Services, and the dataset shown in Illustration 20 appears.
Illustration 20: Results Dataset Another Use of MEMBER_VALUE
and MEMBER_UNIQUE_NAME with .CurrentMember
We see Postal Code for the
individual Customers, the output of the Customer Postal Code Postal
Code calculated member, populating the first data column. The respective Postal
Code Unique Names (again, the qualified MDX name for the associated Postal
Code attribute members, which can be used within a query against the Adventure
Works cube) - the output of the Customer Postal Code MDX Qual Name
calculated member - occupy the second data column. The Postal Code attribute
hierarchy members themselves occupy the row axis, as our client colleagues have
requested (the row axis will not be used in the reporting environment). The
calculated members Customer Postal Code Postal Code and Customer
Postal Code MDX Qual Name employ the MEMBER_VALUE property
and MEMBER_UNIQUE_NAME property, respectively, in conjunction (again)
with the relative .CurrentMember function, which, as we can easily see
from our practical example, results in a combination list of the Postal Code
values / Qualified Names of the members that we specify in our row
axis.
5.
Select File
> Save As, name the file MDX070-02,
and place it in a meaningful location.