Database Reference
In-Depth Information
(Qtr3) 105215.00 47343.00 57872.00
(Qtr4) 98141.00 43754.00 54387.00
note that on AxIS(1), I enclosed [year]. Children in curly braces { }. As mentioned
above, if the set is being returned via a function, the braces are not necessary. however,
as a best practice you should always enclose Sets in curly braces, which will reduce syn-
tax errors. I will briefly mention that Essbase is not great at explaining mDx errors, so
it can be challenging sometimes trying to determine the origin of the syntax issue. By
using best practices and standards, such as always using brackets, qualifying the mem-
ber names with an ancestor member, etc., you can more easily weed out the problem.
6.7.2 Where Slicer
The Where Slicer is very useful in mDx queries for limiting the scope of the query.
however, I have also found it is one of the more misunderstood aspects of the mDx
language, particularly for users of the SQL language. In a SQL query, the Where clause
is used to filter results. For example you might have a SQL query that looks like the
following:
SELECT Year, Product, Market, Scenario, Measures
FROM Fact_Table
WHERE Year = 'Jan'
This query will return all records from the Fact_table for the month of January. In
mDx, it is tempting to think the use of the WhErE clause is similar to SQL, and it is,
with one major exception, you cannot specify a dimension in the WhErE slicer that has
been placed on an axis. you might expect to be able to do something like the following,
but it would be invalid:
SELECT
{[Sales]} ON COLUMNS,
{[Year].members} ON ROWS
FROM Sample.Basic
WHERE [Year].CurrentMember IS [Jan]
This is invalid because the year dimension was already placed on the roWS axis. A
valid way to write this would be:
SELECT
{[Sales]} ON COLUMNS,
{[Jan]} ON ROWS
FROM Sample.Basic
note there is no need for the WhErE slicer in this case. you would only use the
WhErE slicer if year was not on an axis. Let's change the query to show members of
the market dimension on the roWS axis.
SELECT
{[Sales]} ON COLUMNS,
{[Market].[East].children} ON ROWS
FROM Sample.Basic
WHERE ([Year].[Jan])
