## MDX Clauses and Keywords: Use HAVING to Filter an Axis - Page 3March 5, 2007 We now turn to the second example that we have discussed
with the client representatives; it involves an additional case where they wish
to contrast filtering methods, which they outline as follows. The authors
group tells us that the value under consideration this time is the Calendar
Year 2003, for those Customers of Geography .
This time they wish to cross join the respective AustraliaMonths with the measure
within the column axis, presenting the total Reseller Average Sales Amount
for each of the children of Product Model Line , which
will inhabit the row axis.ComponentsOur client colleagues further tell us that they wish to see
only those Reseller
Average Sales Amount value for the Month of May 2003 exceeded
zero units – this is initially a little confusing, but further discussion
clarifies that we are indeed to present the value for all months of Calendar
Year 2003, for only the Components with Reseller Average Sales
Amounts for the Month of May 2003 greater than zero
units. We note, once again, that the final part of the specification
represents a need to filter the members of the row axis – this time supplying a
filter condition that represents a subset of a part of a pre-existing axis
specification. This example will therefore serve as another good opportunity
for demonstrating the use of HAVING as a substitute for the FILTER()
function.Because
we have pointed it out to them in passing, the client report authors group have
a new appreciation for the fact that, given the current queries, the capability
to support ad hoc runtime requirements, based upon, say, the “minimum
Database Journal MSSQL Server Reporting Services series.We will begin, as before, with the construction of a simple
core query to support the business requirements in all except the row-axis
filtering requirement we have outlined. Moreover (also after the manner of our
first practice example), once the core query is in place, we will apply
filtering via the 25.
Select 26.
Select Illustration 9: Create a New Query with the Current Connection ... A new
tab, with a connection to the 27.
Type (or cut
and paste) the following query into the
The
The above core query is crafted to return the Reseller Average Sales Amount value
for the Month of May 2003 exceeded zero units.28.
Execute the
query by clicking the The
In the partial view of the returned dataset, we see that the
core query we have constructed accomplishes the intended purpose, and generates
a Calendar
Year 2003, for those Customers of Geography .
As requested, the columns are formed by a crossjoin of each respective AustraliaMonth
with the Reseller Average Sales Amount measure, while the row axis
presents each of the children of Product Model Line . (We
note, in passing, that the effect of the ComponentsNON EMPTY keyword, too, is
evident: While we do see nulls at the intersections of some of the Component
/ Months, no Component appears with nulls in every column.)29.
Select Our
client colleagues once again express satisfaction with the contextual backdrop
we have established, and state that they are ready to examine the contrasts in
filtering that we have proposed to precisely tune the query to their
specifications. As before, we will filter the core dataset with the 30.
Replace the
comment line in query
31.
Select 32.
Place the cursor
to the right of the 33.
Press the
34.
Add the
following syntax from the currently cursor position (in the new space created
by pressing
35. On what is now the tenth row of the query (including the comment line at top), place the cursor to the immediate right of the following:
36.
Add a comma (
“ 37.
Press the
38. Type the following syntax into the new row:
39.
Press the 40. Type the following syntax into the new row:
Ensuring placement of the cursor to the right of the “ 41.
Type a right
parenthesis ( “ The effect is to enclose the
specified set, Once
we have incorporated the
42.
Execute the
query by clicking the The
The client representatives agree, once again, that the 43.
Select 44.
Replace the
comment line in query
45.
Select 46.
Remove the
line of the query containing 47.
Remove the
line of the query containing 48.
Remove the
comma ( “ 49.
Place the
cursor to the immediate left of the syntax in the row immediately below ( “ 50.
Type the
keyword
51.
Ensure that
the syntax in the line just below our replacement remains intact (that is, The modified query should be substantially the same as the following:
As was
the case within the last query of our first practice example, our objective is
to filter the row axis, and to deliver the same end results dataset as that
delivered by the previous version of the query, via the Once
we have substituted the
52.
Execute the
query by clicking the The
Our client colleagues concur that the 53.
Select 54.
Select The
client representatives inform us that their immediate goals have been met, and
that the examples we have shared have illustrated the principles of operation
behind ## Summary ...In this session, we continued our examination of MDX to
concentrate upon the We
next undertook a couple of illustrative examples whereby we put
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum. MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property Further Combination of BottomCount() with Other MDX Functions Combine BottomCount() with Other MDX Functions to Add Sophistication Basic Set Functions: The BottomCount() Function, Part I Intrinsic Member Properties: The MEMBER_VALUE Property Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property Intrinsic Member Properties: The MEMBER_NAME Property Intrinsic Member Properties: The MEMBER_KEY Property Intrinsic Member Properties: The MEMBER_CAPTION Property Set Functions: The StripCalculatedMembers() Function Set Functions: The AddCalculatedMembers() Function MDX Numeric Functions: The Min() Function MDX Numeric Functions: The Max() Function Set Functions: The .AllMembers Function MDX Essentials: Set Functions: The MeasureGroupMeasures() Function String Functions: The .Properties Function, Part II String Functions: The .Properties Function Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions MDX Scripting Statements: Introducing the Simple CASE Statement Logical Functions: IsGeneration(): Conditional Logic within Calculations Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions MDX Clauses and Keywords: Use HAVING to Filter an Axis Logical Functions: IsAncestor(): Conditional Logic within Calculations Logical Functions: IsSibling(): Conditional Logic within Filter Expressions Logical Functions: IsSibling(): Conditional Logic within Calculations MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations MDX Numeric Functions: The .Ordinal Function Other MDX Entities: Perspectives MDX Operators: The IS Operator MDX Set Functions: The Distinct() Function MDX Set Functions: The ToggleDrillState() Function Set Functions: The DrillUpLevel() Function Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions MDX Set Functions: DrillDownLevel() MDX Set Functions: The DRILLUPMEMBER() Function MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement MDX Essentials: String Functions: The .UniqueName Function MDX Essentials: String Functions: The .Name Function MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function MDX Essentials: Basic Set Functions: The TopCount() Function, Part II MDX Essentials: Basic Set Functions: The TopCount() Function, Part I MDX Essentials: Enhancing CROSSJOIN() with Calculated Members MDX Essentials: Set and String Functions: The GENERATE() Function MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks MDX Essentials: String / Numeric Functions: More on the IIF() Function MDX Essentials: String / Numeric Functions: Introducing the IIF() Function MDX Essentials: Logical Functions: The IsEmpty() Function MDX Essentials: Basic Set Functions: The EXTRACT() Function MDX Essentials: Numeric Functions: Introduction to the AVG() Function MDX Essentials: Basic Member Functions: The .Item() Function MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function MDX Essentials: Basic Set Functions: The CrossJoin() Function MDX Essentials: Basic Numeric Functions: The Count() Function MDX Essentials: Basic Set Functions: The Filter() Function MDX Essentials: Basic Set Functions: The EXCEPT() Function MDX Essentials: Basic Set Functions: The Intersect() Function MDX Essentials: Basic Set Functions: The Union() Function MDX Essentials: Basic Set Functions: The Order() Function MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions MDX Essentials: MDX Member Functions: "Relative" Member Functions MDX Member Functions: The Cousin () Function MDX Essentials: Member Functions: More "Family" Functions MDX Member Functions: The "Family" Functions MDX Essentials: MDX Members: Introducing Members and Member MDX Essentials : MDX Operators: The Basics MDX Essentials: Structure of the MDX Data Model MDX at First Glance: Introduction to SQL Server MDX Essentials |