Database Reference
In-Depth Information
Figure 6.4
An example of View dimension. (From Oracle Essbase Administration Services. With permission.)
as a Load rule header; you will now be able to load data to the database and everything
will look the same with the exception of this new dimension. Any query of [Periodic]
(or [view] for that matter) will return the same results you would have gotten before the
view dimension was added. now add a new member [ytD] as a sibling of [Periodic]
(Figure 6.4).
now add the following member formula to ytD.
SUM(PeriodsToDate([Time].Generations(2), [Time].CurrentMember),
[View].[Periodic])
Looking closer at this formula, you will first see that we are using the
PeriodstoDate() function. The PeriodstoDate() function is a very useful member set
function. The name is a little misleading as it is not restricted to working with time.
It is simply a variation of the memberrange() function; note that it can be used with
nontime-related dimensions. It is obviously very helpful with time calculations and,
in this case, it will return the member range we are looking for, dynamically based on
the generation layer we specify. In many cases, generation-2 will be appropriate for
ytD, but depending on how your time dimension is structured, it may be necessary
to change the ordinal to a different generation layer. The next thing to notice is the
use of the Sum() function. A lot of people miss this and assume PeriodtoDate() will
return the number they are looking for. It is important to distinguish between func-
tions that return numbers and functions that return members. PeriodtoDate() is a set
function and returns members. Therefore, it is necessary to wrap PeriodtoDate() in
a Sum() function to get the summation value. The next and most important piece is
where we specify [view].[Periodic] as the numeric value expression parameter of the
Sum() function. When the function evaluates, every member of the time dimension
that is returned from the PeriodtoDate() function will be summed against [view].
[Periodic] and because all data is loaded to [view].[Periodic] we are able to get the
results we are seeking.
SELECT [View].children ON AXIS(0),
Hierarchize({[Time].members}, POST) ON AXIS(1)
FROM ASOsamp.Sample
WHERE ([Measures].[Original Price], [Years].[Curr Year])
Axis-1 (Periodic) (YTD)
+-------------------+-------------------+------------
(Jan)
7498269.25
7498269.25
(Feb)
3543554.00
11041823.25
(Mar)
5625366.50
16667189.75
(Qtr1)
16667189.75
16667189.75
(Apr)
3678482.25
20345672.00
(May)
7394460.00
27740132.00
(Jun)
5400841.00
33140973.00
