Practice: Add Parameter Default Support Objects to the Analysis Services Layer
next add parameter default support within the Analysis Services database upon which our sample report is
based. To do so, we will access the Cube Designer within the Business Intelligence Development
the following steps:
the Adventure Works cube within the Solution Explorer.
from the context menu that appears, as shown in Illustration 6.
Illustration 6: Opening
the Cube Designer ...
Calculated Members in Analysis Services to Support Parameter Defaults within
As we learned in Reporting
Services: Customize Automatically Created Parameter Support Objects and Parameter
Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets,
as well as in other recent articles within our series, when we
create parameterized filters within our dataset(s), Reporting
Services automatically creates support objects for self-contained
parameterization at the reporting layer. These support objects include Report
Parameters, as we have seen, as well as datasets to support each of
those Report Parameters. The datasets use the WITH MEMBER
keywords to create calculated members at runtime, when the datasets are
instantiated in Reporting Services, which are then referenced, by
default, in the Value and Label fields within the
associated Report Parameter settings page.
Because we took the shortcut approach of having Reporting
Services generate the support objects automatically in our creation of the
date-related parameters in Reporting
Services: Customize Automatically Created Parameter Support Objects we had the Report Parameters in place,
ready to then be pointed to the calculated members we created in Analysis
Services in Support Parameterization from Analysis Services. In
this article, we will save time in like manner, creating support objects in Analysis
Services which we will then reference in a dataset, and then tie to the
appropriate Report Parameters in Reporting Services, as we shall
see. We will continue with the somewhat intuitive date-related parameters we
have already established, as we create the calculated members we will
need in Analysis Services to support parameter defaults. We
could, of course, include additional custom calculated members that are
entirely different, should the need arise in our local business environments.
We review the parameter default
requirements with the client representatives, reiterating that, while
parameterization can certainly be managed using the automatically created
objects provided within Reporting Services, several benefits accrue to
us in creating the support objects within the Analysis Services layer.
Positing the intelligence within the cube will, we assert, mean that the same
logic can be carried forward to multiple reports by simply referencing the
calculations in their respective data sources, versus performing the
calculations for every affected measure, as an early example, in the report
(and adding to processing time, etc.). Moreover, we emphasize that maintaining
the logic in a single location in the Analysis Services layer means a single
point of maintenance: we can modify the logic in one place and rely upon
the changes to ripple through to all reports that reference that logic,
versus having to mechanically modify each individual report. This also ensures
consistency of application of the logic to all affected reports: none will be
overlooked for prospective modifications in the logic, for instance, when modifications
become necessary, nor will different logic be accidentally applied across
From within the now open Cube
Designer, we will begin creating Analysis Services objects, specifically a named
set and several calculated members, to support parameter defaults
in our practice report. Keep in mind that there are multiple ways to
accomplish this effort: we will focus upon the creation of simple calculated
members that we can easily pull into our existing report datasets to
illustrate a straightforward approach that might be handled in more
Select the Calculations
tab within the Cube Designer.
The Calculations tab opens.
bottom entry in the Script Organizer pane (the name of the bottom item
will likely differ in your own environment) to anchor the cursor, as depicted
in Illustration 7.
Illustration 7: Click
the Bottom Entry in the Script Organizer to Position the New Calculation
Click the New
Named Set button atop the Calculations tab, as shown in Illustration
Illustration 8: Click
the New Calculated Member Button
the New Named Set button adds a new named set to
the Script Organizer pane (by default named [Named Set]) and
displays fields for its definition in the calculations form in the Calculations
stage, lets review what we already have in place in the report datasets. The
focus is to build upon what we have previously put in place, and leverage the
existing datasets to simply include, as part of the data they entrain from Analysis
Services, the calculated members we employ there, in later steps, to
generate our parameter defaults.
Leaving the Cube
Designer open at the Calculations tab, return to the DBJ_OLAP_Report where we left it open in Layout
Click the Data
tab of the report to expose the Query pane.
downward selector arrow on the right side of the Dataset selector.
Select the AS_DateYrParams_Support
dataset (which we created in Support Parameterization from Analysis
Services), as shown in Illustration 8.
Illustration 9: Select
the AS_DateYrParams_Support Dataset ...
The MDX for the dataset, which we
added as a part of our steps in aligning parameter support to the respective Analysis
Services calculated members in our earlier article, appears as
depicted in Illustration 10.
Illustration 10: MDX for
the AS_DateYrParams_Support Dataset
We note that the query simply selects calculated
members for parameter caption (what the information consumer sees in
the parameter selector at runtime) and parameter value (the qualified
MDX value that is passed to Analysis Services to query and retrieve data
at runtime, based upon the selection of the information consumer). We can
certainly use this logic to propel our existing solution - we need only extend
the solution to include support for parameter defaults. To do so we
will return to Analysis Services, and create a named set and additional
calculated members within our cube calculations, whose mission
will be to specify the default for this, and each of the other two,
date-related parameters, as we shall see. Once we have created the additional calculated
members, we will return to reference them in a separate dataset through
which we align the newly created Analysis Services objects with the
respective report parameters.
For more information about Named
Sets, particularly from an MDX syntax perspective, please see Named
Sets Revisited, a member of my Introduction to MSSQL Server
Analysis Services series at Database Journal.
For an introduction to Calculated
Members, particularly from an MDX syntax perspective, please see the
Each of these articles is a member
of my MDX in Analysis Services
series at Database Journal.
Return to the Calculations
tab of the Cube Designer.
following into the Name box of the currently open calculations form in the Calculations
Expressions pane (replacing the default name of [Named Set]).
[Last Period Sales]
Type (or cut
and paste) the following into the Expression box of the expanded Expression
NOT ISEMPTY ([Measures].[Internet Sales Amount])
new [Last Period Sales] calculation appears in the Calculations Expressions pane, as shown in Illustration 11.
Illustration 11: The
[Last Period Sales] Named Set in the Calculations Expressions Pane
Click the Check
Syntax button atop the Calculations tab, as depicted in Illustration
Illustration 12: Click
the Check Syntax Button
The Check Syntax message
box appears, indicating that the syntax check was successful, as shown in Illustration
Illustration 13: Our
Syntax Appears to Have Passed Checking ...
Click OK to
dismiss the message box.
have successfully added support, via the named set Last Period Sales,
for what will become a row axis in a Reporting Services
dataset, as we shall see in short order. We have, in effect, specified that Analysis
Services use the logic within the named set to determine the most
recent period (including Year, Quarter, and Month) for
which our cube contains data. The logic accomplishes this by identifying the
most recent Period for which the measure Internet Sales Amount is
not empty. We use the MDX Tail() function to accomplish selection of the
last non-empty period - by employing the MDX Filter() function to
filter out any except periods that are occupied by an Internet Sales
we are retrieving data at the Month level, the corresponding row set
returned by Reporting Services will also include, as we shall see,
higher levels within the Date dimension (Calendar hierarchy) - a
characteristic of Reporting Services that produces a dataset ideal for