Database Reference
In-Depth Information
keeping track of the parenthesis with nested IIF statements can be a bit difficult. By
comparison, the CASE statement now looks nicer.
WITH MEMBER [Scenario].[What If] AS
'CASE
WHEN [Year].CurrentMember IS [Jan] THEN
([Sales], [Actual]) * 1.1
WHEN [Year].CurrentMember IS [Feb] THEN
([Sales], [Actual]) * 1.05
WHEN [Year].CurrentMember IS [Mar] THEN
([Sales], [Actual]) * 1
ELSE MISSING
END'
SELECT
{[Scenario].[Actual], [Scenario].[What If]} ON COLUMNS,
{[Qtr1].Children} ON ROWS
FROM Sample.Basic
WHERE ([Measures].[Sales])
Axis-1 (Actual) (What If)
+---------+---------+---------
(Jan)
32538.00 35791.80
(Feb)
32069.00 33672.45
(Mar)
32213.00 32213.00
note that when in a member formula you want to return a nuLL value, you use the
string mISSIng with no quotes, no brackets, and no punctuation marks. And, for all of
you who learned on Essbase BSo, it is just missing, not #missing.
6.8.1.1 Nesting versus AND
When performing conditional logic, the developer has the
ability to use additional logic terms like “AnD” or “or.” While there are many applica-
tions of these logical constructs, one particular issue that I would like to share has to do
with optimization. An extremely common request is when someone says they want to
do one thing if all the members are level-0, but something else when any of the members
are nonlevel-0. Consider the following example and note that I have already explained
my preference toward CASE opposed to IIF and will be using CASE in the examples.
WITH MEMBER [Measures].[Test1] AS
'CASE WHEN IsLevel([Time].CurrentMember, 0)
AND IsLevel([Years].CurrentMember, 0)
AND IsLevel([Transaction Type].CurrentMember, 0)
AND IsLevel([Payment Type].CurrentMember, 0)
AND IsLevel([Promotions].CurrentMember, 0)
AND IsLevel([Age].CurrentMember, 0)
AND IsLevel([Income Level].CurrentMember, 0)
AND IsLevel([Products].CurrentMember, 0)
AND IsLevel([Stores].CurrentMember, 0)
AND IsLevel([Geography].CurrentMember, 0)
THEN SUM([Geography].members, [Transactions]) ELSE MISSING END'
SELECT
{[Measures].[Units], [Measures].[Transactions], [Measures].[Test1]}
ON AXIS(0),
{[Jan]} ON AXIS(1),
