KB5516: Attribute element name becomes 0 or null after a Transformation metric is added to a Report with MicroStrategy Developer

SYMPTOM
After adding a derived metric, the attribute element name diappears or becomes 0 when there is no value returned in the derived metric while the original metric still has a value for that attribute element.
 
STEPS TO REPRODUCE
Execute the following steps in Strategy Tutorial:

• Create two tables: Fact table to have MONTHID and REVENUE fields,
   
  

•  
  and Lookup (Transformation) Table to have MONTHID, LASTMONTHID, and DESCRIPTION fields.
   
  

• Create a Month attribute with ID form mapped to MONTHID of both tables, and DESC form mapped to DESCRIPTION of Transformation table.
• Create a fact mapped to REVENUE column of the fact table, and create a metric with an expression like SUM(REVENUE).
• Create a transformation object for Month to map last month with LASTMONTHID column.
   
  

• Create a report with Month and Revenue and notice that one row returned.
   
  

• Right click on "Revenue," then, Insert > Transformation > Last Month > Normal.
   
  

• Notice that a new metric is inserted, but no data returned. In addition to that, Month description, 'Feb 2011' disappear.
   
  

CAUSE
This is a known issue with Strategy 9.0.x and 9.2.x. Report SQL with 2nd metric is generated as follows:
Sql

with gopa1 as

(select a11.MONTHID MONTHID,

a11.REVENUE WJXBFS1

from MSU2U1.FACTTABLE a11

),



gopa2 as

(select a12.MONTHID MONTHID,

a12.DESCRIPTION DESCRIPTION,

a11.REVENUE WJXBFS1

from MSU2U1.FACTTABLE a11

join MSU2U1.TRANSFORMATIONTABLE a12

on (a11.MONTHID = a12.LASTMONTHID)

)



select pa11.MONTHID MONTHID,

pa12.DESCRIPTION DESCRIPTION,

pa11.WJXBFS1 WJXBFS1,

pa12.WJXBFS1 WJXBFS2

from gopa1 pa11

left outer join gopa2 pa12

on (pa11.MONTHID = pa12.MONTHID)

 
After that, the report SQL changed as follows, and the result comes back fine.
SQL

with gopa1 as

(select a11.MONTHID MONTHID,

a11.REVENUE WJXBFS1

from MSU2U1.FACTTABLE a11

),

gopa2 as

(select a12.MONTHID MONTHID,

a11.REVENUE WJXBFS1

from MSU2U1.FACTTABLE a11

join MSU2U1.TRANSFORMATIONTABLE a12

on (a11.MONTHID = a12.LASTMONTHID)

)select pa11.MONTHID MONTHID,

a13.DESCRIPTION DESCRIPTION,

pa11.WJXBFS1 WJXBFS1,

pa12.WJXBFS1 WJXBFS2

from gopa1 pa11

left outer join gopa2 pa12

on (pa11.MONTHID = pa12.MONTHID)

left outer join MSU2U1.TRANSFORMATIONTABLE a13

on (pa11.MONTHID = a13.MONTHID)

 
Article Reference Number: 
KB5516