Database Reference
In-Depth Information
It is worth noting that unlike the SQL programming language, the
Where
clause does not include conditional
expressions in MDX. Instead, simply include which member or members you want to return.
Listing 14-38.
he
Where
Clause
Select
{ [Measures].[SalesQuantity] } On Columns,
{ NonEmpty(
CrossJoin(
[DimDates].[Year].AllMembers
,[DimTitles].[TitlesByType].[Title].AllMembers
-- Since the Store hierarchy cannot be used twice in the same statement,
-- we have to comment this next line out of our code
//,[DimStores].[Store].AllMembers
)
)
} On Rows
From [CubePubsSales]
Where( [DimStores].[Store].[News & Brews] );
It's an odd rule, but you cannot include a dimension hierarchy more than once in an MDX query. However,
if you have two hierarchies that contain the same attribute, you can still use an attribute twice. For example, in
Listing 14-39, we refer to the Title attribute both in the
CrossJoin
function and in the
Where
clause. This works,
because we used two different hierarchies in the attributes path. The results of the query are shown in Figure
14-32
.
Figure 14-32.
The results of the example in Listing 14-39