KB14243: The report does not group by an attribute ID form with an ApplySimple statement in its expression in MicroStrategy Developer

SYMPTOM:
In the SQL view of a report in Strategy Developer, an attribute ID form has the following expression:

ApplySimple ("substring (CUST_CITY_NAME, 1, 10)",0)

 

The attribute ID form does not appear in the group by section of the report, as shown below:
 

select a12. AS region_id,

max(a13.) AS REGION_NAME,

substring(CUST_CITY_NAME, 1, 10) CustCol 3,

sum(a11.) AS WJXBFS1

from a11,

a12,

a13,

a14

where a11. = a12. and

a12. = a13.

and a12. in (11)

group by a12.

 

 
CAUSE:
The syntax for the ApplySimple expression is incorrect. The correct syntax for ApplySimple is as follows:

ApplySimple("expression_with_placeholders", parameter_1, …, parameter_n)

 

ACTION:
In the example above, the ApplySimple statement for the attribute ID form should contain placeholders for column name as follows:

ApplySimple("substring(#0, 1, 10)",)

 

After changing the ApplySimple syntax for the attribute form CustCol 3, when the report SQL is viewed, the report will group by the attribute form CustCol 3 as shown below:

select a12. AS region_id,

max(a13.) AS REGION_NAME,

substring(CUST_CITY_NAME, 1, 10) CustCol 3,

sum(a11.) AS WJXBFS1

from a11,

a12,

a13,

a14

where a11. = a12. and

a12. = a13.

and a12. in (11)

group by a12.

a14.CustCol 3