Post date: 24-Apr-2014 03:50:08
Set Time Interval Using MDX
Desirable Result:
Expected Result:
NOTE:
Column C and E are retrieve from Essbase.
Column F is calculated using formula.
Template:
Workbook Script
A: Procedure : OnAfterWorkbookOpen
1. Add Token for time interval
=ROUND( (DATEVALUE(RIGHT(“@TVal([T.To])”,3)&” 15, 20″&IF(MONTH(DATEVALUE(“01/”&RIGHT(“@TVal([T.To])”,3)))<7,MID(“@TVal([T.To])”,6,2),MID(“@TVal([T.To])”,3,2))) – DATEVALUE(RIGHT(“@TVal([T.From])”,3)&” 15, 20″ & IF(MONTH(DATEVALUE(“01/”&RIGHT(“@TVal([T.From])”,3)))<7,MID(“@TVal([T.From])”,6,2),MID(“@TVal([T.From])”,3,2))))/30,0)+1
- Method : AddToken
2. Build Grid using MDX
WITH MEMBER [KPI].[FROMTOSUM]
AS
‘CASE WHEN @TVal([T.MonthCount]) < 1 THEN [@TVal([T.KPI])] ELSE SUM(LastPeriods(@TVal([T.MonthCount])),[@TVal([T.KPI])]) END’
SELECT CROSSJOIN( { [KPI].[FROMTOSUM] } , CROSSJOIN( { [MTD]}, { [A] , [LY A], [vs. LY %], [A],[vs. B]} ) ) ON COLUMNS,
NON EMPTY HIERARCHIZE( DESCENDANTS([@TVal([T.Geography])], 2, BEFORE) , POST ) ON ROWS FROM Outlet_ItemG
WHERE ( [@TVal([T.To])] , [@TVal([T.Commercial Org] )], [@TVal([T.Channel])] , [@TVal([T.Outlet Image])] , [@TVal([T.Product POV] )], [@TVal([T.Unit] )], [@TVal([T.Product])], [Total Customers] )
- Method: EssbaseMdxQuery
B: Procedure: OnAfterBuild (To copy Format on Row.Template and formula on cell F7)
Method
1. CopyRange – Formula (Cell F7)
2. CopyRange – Format (Row.Template)
Paste Range
=ADDRESS(ROW(Ess.DataRange.6),6)&”:”&ADDRESS((ROW(Ess.DataRange.6)+ROWS(Ess.DataRange.6)-1),6)
Ess.Retrieve.Range.6
C: Procedure: OnAfterBuild (To define token name for Title)
- Method: SetEntry for Tokens. (T.To, T.From, T.KPI, T.Unit)
Refer to PRC Dodeca – MDX report.
– Volume Summary
– Outlet Monitoring