For this example, the VMall demo project running against SQL Server is used. The requirements outline that the following report is needed:
This report shows the sum of sales for each month, for the previous row and the % change between them. Note that for the first row, the data from the previous row is 0 since the analysis is limited to the data retrieved by the report. Even if the warehouse contains data for December, 1997 (199712) it is shown as 0.
NOTE: This example explains how to perform calculations on previous rows. The report can also be achieved using transformations.
|
This report shows the sum of sales for each month, for the previous row and the % change between them. Note that for the first row, the data from the previous row is 0 since the analysis is limited to the data retrieved by the report. Even if the warehouse contains data for December, 1997 (199712) it is shown as 0.
NOTE: This example explains how to perform calculations on previous rows. The report can also be achieved using transformations.
|
To create the previous report, the following objects are needed:
Filters:
F1
Year = 1998
Metrics:
A - NORMAL SUM
Sum([revenue]) {~}
B - MOVING SUM
MovingSum(Sum([revenue]) {~} , 2)
This means that the Strategy internal OLAP function 'MovingSum' will be performed on the Sum() aggregation, sorted by the month ID with a window size of 2 (2 last values).
To access the parameters for the function, in the metric editor, right-click on the 'MovingSum' word:
C - DATA PREVIOUS ROW (B - A)
([B - MOVING SUM (A)] - [A - NORMAL SUM])
This is a compound metric that will get the data for the previous row.
D - % CHANGE ((A-C)/C)
(([A - NORMAL SUM] - [C - DATA PREVIOUS ROW (B - A)]) / [C - DATA PREVIOUS ROW (B - A)])
This is a compound metric that calculates the percentage change between the previous row and the current row.
On the template, the month attribute, A - NORMAL SUM, C - DATA PREVIOUS ROW (B - A) and D - % CHANGE ((A-C)/C) are displayed using the F1 filter:
GridSQL
|
GridSQL
|
Report: R1
Job: 677
Data Rows: 12
Data Columns: 3
select a11.MONTH_ID MONTH_ID,
sum(a11.TOT_DOLLAR_SALES) ANORMALSUM,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1,
sum(a11.TOT_DOLLAR_SALES) WJXBFS2,
sum(a11.TOT_DOLLAR_SALES) WJXBFS3,
sum(a11.TOT_DOLLAR_SALES) WJXBFS4,
sum(a11.TOT_DOLLAR_SALES) WJXBFS5,
sum(a11.TOT_DOLLAR_SALES) WJXBFS6,
sum(a11.TOT_DOLLAR_SALES) WJXBFS7
from MNTH_CATEGORY_SLS a11
join LU_MONTH a12
on (a11.MONTH_ID = a12.MONTH_ID)
where a12.YEAR_ID in (1998)
group by a11.MONTH_ID
|
Report: R1
Job: 677
Data Rows: 12
Data Columns: 3
select a11.MONTH_ID MONTH_ID,
sum(a11.TOT_DOLLAR_SALES) ANORMALSUM,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1,
sum(a11.TOT_DOLLAR_SALES) WJXBFS2,
sum(a11.TOT_DOLLAR_SALES) WJXBFS3,
sum(a11.TOT_DOLLAR_SALES) WJXBFS4,
sum(a11.TOT_DOLLAR_SALES) WJXBFS5,
sum(a11.TOT_DOLLAR_SALES) WJXBFS6,
sum(a11.TOT_DOLLAR_SALES) WJXBFS7
from MNTH_CATEGORY_SLS a11
join LU_MONTH a12
on (a11.MONTH_ID = a12.MONTH_ID)
where a12.YEAR_ID in (1998)
group by a11.MONTH_ID
|
Note that only the Sum([Dollar Sales]) is performed on the database side. All the other calculations are performed by the Analytical Engine in Strategy.
For more information regarding Analytical Engine Functions please refer to the Strategy product manuals.
