SYMPTOM:
Create a report in Strategy Developer. Define a report limit, as shown in the screen shot below:
View the report Structured Query Language (SQL). Observe, that the limit is not applied in the SQL generated for the report:
select a13.REGION_ID REGION_ID,
max(a14.REGION_NAME) REGION_NAME,
a11.EMP_ID EMP_ID,
max(a12.EMP_LAST_NAME) EMP_LAST_NAME,
max(a12.EMP_FIRST_NAME) EMP_FIRST_NAME,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1,
sum(a11.TOT_COST) WJXBFS2,
sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) WJXBFS3
from ITEM_EMP_SLS a11
join LU_EMPLOYEE a12
on (a11.EMP_ID = a12.EMP_ID)
join LU_CALL_CTR a13
on (a12.CALL_CTR_ID = a13.CALL_CTR_ID)
join LU_REGION a14
on (a13.REGION_ID = a14.REGION_ID)
group by a13.REGION_ID,
a11.EMP_ID
CAUSE:
One or more smart metrics exist on the report template. When a report includes smart metrics (i.e., metrics with the "Allow Smart Metrics" option enabled), any defined report limits will be evaluated in the Analytical Engine.
WORKAROUND:
If it is critical to evaluate the report limit in SQL, disable the "Allow Smart Metrics" options in the applicable metric(s).
Save the changes to the affected metric(s) and the report. View the report SQL:
select a13.REGION_ID REGION_ID,
max(a14.REGION_NAME) REGION_NAME,
a11.EMP_ID EMP_ID,
max(a12.EMP_LAST_NAME) EMP_LAST_NAME,
max(a12.EMP_FIRST_NAME) EMP_FIRST_NAME,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1,
sum(a11.TOT_COST) WJXBFS2,
sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) WJXBFS3,
(sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) /
NULLIF(sum(a11.TOT_DOLLAR_SALES), 0)) WJXBFS4
from ITEM_EMP_SLS a11
join LU_EMPLOYEE a12
on (a11.EMP_ID = a12.EMP_ID)
join LU_CALL_CTR a13
on (a12.CALL_CTR_ID = a13.CALL_CTR_ID)
join LU_REGION a14
on (a13.REGION_ID = a14.REGION_ID)
group by a13.REGION_ID,
a11.EMP_ID
having sum(a11.TOT_DOLLAR_SALES) < 350000.0
Observe that the limit is now applied to the last line of SQL.
Note: Report designers should be aware that subtotals for smart metrics may be evaluated differently with the smart metric option turned off.
