Procedure: Use the LEVEL_UNIQUE_NAME property (among
others) in another example to Provide Parameter Picklist Support in Reporting
Services
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 unique name for a given filter, say, for one of the Customer
Geography Cities in the list we created earlier.
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 the one 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 various hands-on approaches (the options are legion) to
constructing picklists in Reporting Services, see these articles in 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 regular name for the Product Categories
each time an information consumer runs the report while the Report
Parameter is to reference (and thus pass) the unique (MDX -
qualified) name to Reporting Services for purposes of filtering
the report. Moreover, they wish to add the hierarchical level name into
the dataset, for inclusion primarily as a report label although we
inform them that, at a later date, we will expand upon the use of level
names in the dataset to support cascading parameters that will allow us,
among other options, to extend report parameterization to allow us to make
choices to vary the levels upon which we want to perform ad hoc prompts
at runtime, and thus to provide an even greater capacity for the report to do
more with less.
While the focus of our article is the MDX required in
meeting this request, and specifically upon the use of the LEVEL_UNIQUE_NAME
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 a
simple example of 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 LEVEL_UNIQUE_NAME property in conjunction with a relative function, .CurrentMember.
The solution also includes the MEMBER_NAME and MEMBER_UNIQUE_NAME
properties, as did our predecessor solution. We will be targeting the member
name column in the resulting dataset (well call it Product Category -
Name) for the name that is displayed in the selector for the parameter
picklist. The member unique name column of the returned dataset (the
qualified MDX name for each Category member of the Product dimension,
Product Categories attribute hierarchy), which we call Product
Category 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 the capability to perform ad hoc selection of a Product Category
upon which to filter the report data. (We might use the value returned by the Product
Category - Level Name calculated member, which relies upon the LEVEL_UNIQUE_NAME
property, as a label somewhere within our report).
Our first step is to construct a query to return the
requested Product Category list, presenting the level name,
together with the respective selector member names and unique names in
three side-by-side columns. The corresponding Product Category
members of the Product dimension (Product Categories 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
depicted in Illustration 3.
Illustration 3: 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:
-- MDX076-02 Using .LEVEL_UNIQUE_NAME, .MEMBER_NAME and
-- .MEMBER_UNIQUE_NAME to generate a picklist selection
WITH
MEMBER
[Measures].[Product Category - Level Name]
AS
'[Product].[Product Categories].CurrentMember.LEVEL_UNIQUE_NAME'
MEMBER
[Measures].[Product Category - Name]
AS
'[Product].[Product Categories].CurrentMember.MEMBER_NAME'
MEMBER
[Measures].[Product Category - MDX Qual Name]
AS
'[Product].[Product Categories].CurrentMember.MEMBER_UNIQUE_NAME'
SELECT
{[Measures].[Product Category - Level Name],
[Measures].[Product Category - Name],
[Measures].[Product Category - MDX Qual Name]}
ON AXIS(0),
{[Product].[Product Categories].[Category].MEMBERS}
ON AXIS(1)
FROM
[Adventure Works]
The Query pane appears,
with our input, as shown in Illustration 4.
Illustration 4: 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 depicted in Illustration 5 appears.
Illustration 5: Results Dataset Another Use of LEVEL_UNIQUE_NAME, MEMBER_NAME and MEMBER_UNIQUE_NAME with .CurrentMember
We see the unique level name, the
output of the Product Category Level Name calculated member,
populating the first data column. Additionally, the respective member names
for the individual Product Categories, the output of the Product
Category - Name calculated member, appear within the second data column. Finally,
the associated Product Category member unique names (again, the
qualified MDX name that can be used within a query against the Adventure
Works cube) - the output of the Product Category MDX Qual Name
calculated member - occupy the third data column. The Product Category 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 Product
Category Level Name, Product Category - Name and Product
Category - MDX Qual Name employ the LEVEL_UNIQUE_NAME property,
the MEMBER_NAME 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 hierarchical level
name, and the member names
and qualified names of the members that we specify in our row axis.
5.
Select File
> Save As, name the file MDX076-02,
and place it in a meaningful location.
We
will not take the steps (which occur inside the reporting layer) to construct
the picklist apparatus within this article. However, lets take a look at one
approach to assembling the parts in Reporting Services (or, similarly,
in another OLAP reporting application). First, we would transfer the query to Reporting
Services own Data tab to generate a dataset within the
report under consideration. This query, together with the dataset it
generates, would look something similar to that which is partially shown in Illustration
6.
Illustration 6: Constructing a Dataset in Reporting Services to Support a Parameter Picklist
NOTE:
This is only one approach to creating the dataset perhaps the more
obvious of several. Another might be more optimal, depending upon the
reporting environment under consideration. Other options, the components of
which might occupy different layers of the Microsoft integrated business
intelligence solution, might include installation of the calculated members at
the UDM / cube level, and then calling (versus defining and
building) them from the reporting layer.
For a
step-by-step procedure that demonstrates the construction of such a cube-based
solution to support a picklist in Reporting Services, see Create a Cube-Based Hierarchical Picklist in my MDX in Analysis Services series, or Parameterization from Analysis Services Cascading
Picklists in my MSSQL Server Reporting
Services series
here at Database Journal.
Once
we have created the dataset, the next step is to add a parameter
to the report. Inside the Report Parameter definition, we would
reference the new dataset (in the example I created for my illustrations
I left it named at the default of Dataset1), as shown, and then select Product Category - MDX Qual Name and Product Category - Name within the Value and Label fields respectively. Illustration
7 presents a view of the way all this would tie together in the Report
Parameter dialog inside Reporting Services.
Illustration 7: Pulling It All Together inside the Report Parameter ...
At this point all that remains is to return to the primary dataset
underneath the report and to insert the parameter variable within an axis
specification or a slicer, where it acts as a filter (there are examples of
this, and many other relevant steps, in the articles I have cited above).
Executing the query then triggers the prompting action of the new Product
Category parameter.
The selection list, displaying the regular Product
Category name, is manifested in the parameter dropdown when we preview or execute
the report, as depicted in Illustration 8.
Illustration 8: The Product Category Parameter Selector in Action ...
And so we see that our query, using the LEVEL_UNIQUE_NAME,
MEMBER_NAME and MEMBER_UNIQUE_NAME intrinsic member properties
- in conjunction with the relative .CurrentMember function - to
present the hierarchical level names, member names and member
unique names for the Product Categories in three side-by-side
columns, can be readily used to support a picklist for a parameter
within the reporting layer of the business intelligence solution of our
client. (As we noted earlier, we might use the value returned by the Product
Category - Level Name calculated member, which relies upon the LEVEL_UNIQUE_NAME
property, to act as a label, to support yet another parameter, or
perhaps to perform some other function, somewhere within our report).
Having demonstrated the workings of the LEVEL_UNIQUE_NAME,
MEMBER_NAME and MEMBER_UNIQUE_NAME properties in this fashion has
helped us to show our client colleagues that we have, within the current dataset
query, established support for parameterization based upon
underlying cube data. Our client colleagues express satisfaction with the
results, and confirm their understanding of the operation of the LEVEL_UNIQUE_NAME
property within the contexts we have presented in the practice exercises.
As always, we reiterate to the Reporting team that knowing where to put the
intelligence within the various layers of the Microsoft integrated BI solution
can mean highly tuned performance and effective solutions for consumers
throughout our organizations.
6.
Select File
-> Exit to leave the SQL Server Management Studio, when ready.
Summary ...
In
this article, we introduced the MDX LEVEL_UNIQUE_NAME property, which
can be called upon in activities that range from generating simple lists to
supporting parameters in the reporting layer, as well as more sophisticated
uses. We introduced the function, commenting upon its operation and touching
upon the datasets we can deliver using LEVEL_UNIQUE_NAME.
We
examined the syntax involved with LEVEL_UNIQUE_NAME, and then undertook
a couple of illustrative practice examples of business uses for the function,
generating queries that capitalized on its primary features. Our exercises
included examples that drew upon our earlier examinations of the MEMBER_NAME
property (in Intrinsic Member Properties: The MEMBER_NAME Property), and the MEMBER_UNIQUE_NAME property
(in Intrinsic Member Properties: The MEMBER_UNIQUE_NAME
Property), which we used in combination with LEVEL_UNIQUE_NAME to
create a results dataset. We then illustrated the use of a similar dataset to
support a parameter picklist in a report that queried an Analysis
Services data source. Throughout our practice session, we briefly discussed
the results datasets we obtained from each of the queries we constructed.
About the MDX Essentials Series ...
This article is a member of the MDX Essentials series, a monthly
column designed to provide hands-on application of the fundamentals of the
Multidimensional Expressions (MDX) language, with each article
progressively adding features designed to meet specific real-world needs.
For more information about the column in general, as well as
the software and systems requirements for getting the most out of the lessons
included, please see my first article, MDX at First Glance: Introduction to MDX Essentials, among others.
»
See All Articles by Columnist William E. Pearson, III