Hi,
I use Oracle PBCS.
I have a dimension 'View' with member 'MTD' that I load data into.
I then have member 'YTD' and member 'TTM_Avg' that are a dynamic calc using MTD.
'TTM_Avg' is a calculation of the trailing 12-mth average (my definition is to include the current month)
A snapshot of member formula for 'TTM_Avg' is below (the full formula continues beyond Oct for every period). The member formula works, however when I use 'TTM_Avg' in a report, it is very slow to load.
I am considering adding this code to the start of the formula @CALCMODE(BLOCK); @CALCMODE(BOTTOMUP);
My testing indicates the report is much faster to load with this included, and the resulting value is the same but I was wondering if any other users can suggest a reason not to do this??
IF (@ISMBR("FY13") OR @ISMBR("Jun"))
/* When year is FY13 (the first year) or period is Jun, the 12-mth avg is calculated over the prior 11 periods and the current period */
@AVGRANGE(SKIPMISSING,"MTD", @CURRMBRRANGE("Period", LEV, 0,-11,0));
ELSE IF (@ISMBR("Jul"))
/* When period is Jul, the 12-mth avg is calculated over Aug to Jun of the prior year plus the current period (July) */
(@SUMRANGE("MTD",@RANGE("MTD"->@MEMBER(@PREVSIBLING(@CURRMBR("Years"))),"Aug":"Jun"))+@SUMRANGE("MTD", @CURRMBRRANGE("Period", LEV, 0,0,0)))
/
(@COUNT(SKIPMISSING,@RANGE("MTD"->@MEMBER(@PREVSIBLING(@CURRMBR("Years"))),"Aug":"Jun"))+@COUNT(SKIPMISSING,"MTD", @CURRMBRRANGE("Period", LEV, 0,0,0)));
ELSE IF (@ISMBR("Aug"))
/* When period is Aug, the 12-mth avg is calculated over Sep to Jun of the prior year plus the prior 1 period (Jul) of the current year plus the current period (Aug) */
(@SUMRANGE("MTD",@RANGE("MTD"->@MEMBER(@PREVSIBLING(@CURRMBR("Years"))),"Sep":"Jun"))+@SUMRANGE("MTD", @CURRMBRRANGE("Period", LEV, 0,-1,0)))
/
(@COUNT(SKIPMISSING,@RANGE("MTD"->@MEMBER(@PREVSIBLING(@CURRMBR("Years"))),"Sep":"Jun"))+@COUNT(SKIPMISSING,"MTD", @CURRMBRRANGE("Period", LEV, 0,-1,0)));
ELSE IF (@ISMBR("Sep"))
/* When period is Sep, the 12-mth avg is calculated over Oct to Jun of the prior year plus the prior 2 periods (Jul to Aug) of the current year plus the current period (Sep) */
(@SUMRANGE("MTD",@RANGE("MTD"->@MEMBER(@PREVSIBLING(@CURRMBR("Years"))),"Oct":"Jun"))+@SUMRANGE("MTD", @CURRMBRRANGE("Period", LEV, 0,-2,0)))
/
(@COUNT(SKIPMISSING,@RANGE("MTD"->@MEMBER(@PREVSIBLING(@CURRMBR("Years"))),"Oct":"Jun"))+@COUNT(SKIPMISSING,"MTD", @CURRMBRRANGE("Period", LEV, 0,-2,0)));
ELSE IF (@ISMBR("Oct"))
/* When period is Oct, the 12-mth avg is calculated over Nov to Jun of the prior year plus the prior 3 periods (Jul to Sep) of the current year plus the current period (Oct) */
(@SUMRANGE("MTD",@RANGE("MTD"->@MEMBER(@PREVSIBLING(@CURRMBR("Years"))),"Nov":"Jun"))+@SUMRANGE("MTD", @CURRMBRRANGE("Period", LEV, 0,-3,0)))
/
(@COUNT(SKIPMISSING,@RANGE("MTD"->@MEMBER(@PREVSIBLING(@CURRMBR("Years"))),"Nov":"Jun"))+@COUNT(SKIPMISSING,"MTD", @CURRMBRRANGE("Period", LEV, 0,-3,0)));