Procedure: Use the LEVEL_NUMBER property (among others) in
another example to Provide Parameter Picklist Support in Reporting Services
Let’s
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. Let’s 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:
Let’s 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 number and
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 numbers and 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_NUMBER
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. Let’s 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 – it’s 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_NUMBER and the LEVEL_UNIQUE_NAME properties, each again
in conjunction with the .CurrentMember relative function. 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 (we’ll 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. (As
we have mentioned earlier, we might use either or both of the values returned
by the Product Category - Level No and the Product Category - Level
Name calculated members, which rely upon the LEVEL_NUMBER and LEVEL_UNIQUE_NAME
property, respectively, as a label (or labels) somewhere within our report).
Our first step is to construct a query to return the
requested Product Category list, presenting the level number and level
name, together with the respective selector member names and unique
names in four 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:
-- MDX077-02 Using .LEVEL_NUMBER, .LEVEL_UNIQUE_NAME, .MEMBER_NAME and
-- .MEMBER_UNIQUE_NAME to generate a picklist selection
WITH
MEMBER
[Measures].[Product Category - Level No]
AS
'[Product].[Product Categories].CurrentMember.LEVEL_NUMBER'
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 No],
[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_NUMBER, LEVEL_UNIQUE_NAME, MEMBER_NAME and MEMBER_UNIQUE_NAME with .CurrentMember
We see the level number, the
output of the Product Category – Level No calculated member, populating
the first data column, alongside the unique level name, the output of
the Product Category – Level Name calculated member, populating the second
data column. Additionally, the respective member names for the
individual Product Categories, the output of the Product Category - Name
calculated member, appear within the third 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 fourth 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 No., Product Category – Level Name, Product
Category - Name and Product Category - MDX Qual Name employ the
LEVEL_NUMBER property, 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 number and 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 MDX077-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, let’s 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. Here, 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_NUMBER,
LEVEL_UNIQUE_NAME, MEMBER_NAME and MEMBER_UNIQUE_NAME intrinsic
member properties - in conjunction with the “relative” .CurrentMember
function - to present the hierarchical level numbers, level names,
member names and member unique names for the Product
Categories in four side-by-side columns, can be readily used to support a picklist
for a runtime parameter within the reporting layer of the business
intelligence solution of our client. (As we noted earlier, we might use the
value returned by either or both of the Product Category - Level No and
the Product Category - Level Name calculated members, which rely upon each
of the LEVEL_NUMBER and LEVEL_UNIQUE_NAME properties,
respectively, to act as a label (or labels), to support yet another parameter,
or perhaps to perform some other function, somewhere within our report).
Having demonstrated the workings of the LEVEL_NUMBER,
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_NUMBER
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_NUMBER 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. In
examining the function, we commented upon its operation and touched upon the
datasets we can deliver using LEVEL_NUMBER.
We
examined the syntax involved with LEVEL_NUMBER, 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 LEVEL_UNIQUE_NAME
property (in
The
LEVEL_UNIQUE_NAME Intrinsic Member Property ), 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_NUMBER 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
» 
Comment and Contribute
