Database Reference
In-Depth Information
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 33823.65
here is the same example using a CASE statement:
WITH MEMBER [Scenario].[What If] AS
'CASE WHEN [Year].CurrentMember IS [Jan] THEN
([Sales], [Actual]) * 1.1
ELSE ([Sales], [Actual]) * 1.05 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 33823.65
I personally find the CASE statement to be easier to read, and, although in this exam-
ple it results in more lines of code, you can quickly see where it has its advantages.
Assume the requirement changes yet again. Is this starting to sound more and more like
the real world? The requirement now is to have Jan * 10%, Feb * 5%, and mar to stay flat.
The IIF statement now has to be nested and starts to get a bit ugly.
WITH MEMBER [Scenario].[What If] AS
'IIF([Year].CurrentMember IS [Jan],
([Sales], [Actual]) * 1.1,
IIF([Year].CurrentMember IS [Feb],
([Sales], [Actual]) * 1.05,
IIF([Year].CurrentMember IS [Mar],
([Sales], [Actual]) * 1,
MISSING)))'
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
