Database Reference
In-Depth Information
This is obviously more complicated, but as the logic gets more complicated it
becomes necessary. Let us say you wanted to filter for periods that begin with the
let ter “J ”:
SELECT
{[Sales]} ON COLUMNS,
{Filter([Year].members,
Substring([Year].CurrentMember.Member_Name, 1, 1) =
"J")}ON ROWS
FROM Sample.Basic
Axis-1 (Sales)
+-------------------+-------
(Jan)
32538
(Jun)
35088
(Jul)
36134
or, going back to our issue of only returning Sales that are greater than 100,000:
SELECT
{[Sales]} ON COLUMNS,
{Filter([Year].members, [Sales] > 100000)} ON ROWS
FROM Sample.Basic
Axis-1 (Sales)
+-------------------+-------
(Year)
401855
(Qtr2)
101679
(Qtr3)
105215
Note:
This is one of the few times when you can make reference to the same dimension
on more than one axis.
I will present more ways to use the Filter() function later in the chapter. For now,
understand the WhErE slicer is not a filter. It is a dimensional slice most similar to the
concept of dimensional headers or Pov in a spreadsheet.
6.7.2.1 Creating Calculated Members in a Query Using “With” Specification
Essbase devel-
opers are very comfortable creating calculated members with member formulas in the
Essbase outline. This is partially due to the ease of maintenance a developer has with an
Essbase outline using a tool like Essbase Administration Services (EAS). other oLAP
(online analytical processing) products are not as flexible, and even with Essbase, there
are times when it is not convenient to add a member to an outline. Adding members can
result in database restructures, which can be time-consuming operations. In addition,
sometimes the calculation that is needed is only needed by one or a few users, or only for
a limited time. mDx queries can help by using something called the “WIth mEmBEr”
specification. using WIth mEmBEr, a query writer can define a calculated member
for use in their query that does not need to exist in the database.
For example, let us assume we have a requirement to calculate margin Percent and
this member does not exist in the database. We have [Sales] and we have [margin]. We
