KB213821: A cross join is done on a report that has a non-aggregatable transformation metric in MicroStrategy 9.x

SUMMARY
This document outlines an issue where a cross join  is done on report which has a non-aggregatable transformation metric. This issue has been marked as a defect in Strategy 9.x. and is assigned to be fixed on a future Strategy release.  
 
SYMPTOM
In Strategy 9.x, a cross join is done on a report that has a non-aggregatable transformation level metric.
STEPS TO REPRODUCE
The following steps are based on the Strategy Tutorial project: 
 

• Create a filter F01 defined as: Year=2010
• Create a metric called M01 with the formula of Sum(Revenue); add F01 and set dimension to any other attribute such as Region and change filter=none, group=ending(lookup); add transformation ‘Last’s year’s’ (need to be related to F01) as shown below:
  

• Create a report using the metric M01.
• Execute the report and check the SQL. The unnecessary cross join and filtering appears as highlighted in the SQL below.

SQL Statements:
create table ZZOP00 (
                CALL_CTR_ID        SHORT)

insert into ZZOP00 
select      max(c11.[CALL_CTR_ID]) AS CALL_CTR_ID
from         [LU_CALL_CTR]     c11, 
                [LU_YEAR]              c12
where      c12.[YEAR_ID] in (2010)

select      sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
from         [DAY_CTR_SLS]    a11, 
                [LU_DAY]                a12, 
                [LU_YEAR]              a13, 
                [ZZOP00]                pa14
where      a11.[DAY_DATE] = a12.[DAY_DATE] and 
                a12.[YEAR_ID] = a13.[PREV_YEAR_ID] and 
                a11.[CALL_CTR_ID] = pa14.[CALL_CTR_ID]
and         a13.[YEAR_ID] in (2010)

drop table ZZOP00