Database Reference
In-Depth Information
is to type out the individual members. Some sets could contain hundreds of members,
in which case the only practical solution is to use a function. however, be clear on the
requirement. If the requirement is children of Colas, then by all means use the function,
but if the requirement is specifically {[100-10], [100-20], [100-30]}, be careful using the
Children() function as a short cut because if a new product member were to be added to
the database under Colas, the Children() function will return the new member as part
of the set. In many ways this is another benefit of using functions because it keeps the set
definitions in sync with the outline. Be aware, however, of what you are doing because,
if your requirement was to pull back those three products specifically, then using a set
function when a new member is added would return incorrect results.
Data value functions are helpful when looking for mathematical results in a query.
Sticking with the example above, assume you are looking for the Average Sales for the
children of Colas. you could leverage the Avg() function.
Avg([Colas].children, [Measures].[Sales], INCLUDEEMPTY)
note that the InCLuDEEmPty keyword enhances the power of the Avg() function
by including null members in the denominator count. If you leave this key word out,
you will get a weighted average where the denominator will be based on the count of
members with nonnull values.
Essbase has a robust list of functions for mDx that span the following categories:
members, Sets, tuples, numbers, Dimensions, Layers, Booleans, Dates, and Strings.
once again, I will suggest reviewing the technical reference for specific function defi-
nitions and examples of how to utilize them in queries and member formulas.
6.6.1 Crossjoin
one function that deserves special mention is the Crossjoin. The Crossjoin is used to
return the cross product of two sets from different dimensions. note that I said two and
only two. If you want to Crossjoin more than two sets, you will need to nest Crossjoins.
I like to think of the Crossjoin function as a way of generating dynamic tuples.
For instance, assume you wanted to have on your rows both the children of [East]
and the children of [Colas]. you could long-hand it like {([Cola], [new york]), ([Cola],
[Florida]),,,([Diet Cola], [new york), ([Diet Cola], [Florida]),,,EtC}, or you could use a
Crossjoin().
SELECT
{} ON AXIS(0),
Crossjoin([Colas].children, [East].children) ON AXIS(1)
FROM [Sample.Basic];
Axis-1
+-------------------------------------------------
(Cola, New York)
(Cola, Massachusetts)
(Cola, Florida)
(Cola, Connecticut)
(Cola, New Hampshire)
(Diet Cola, New York)
(Diet Cola, Massachusetts)
(Diet Cola, Florida)
Search WWH ::




Custom Search