The universe does not allow using a complex expression in a GROUP BY

[astudillo]

Dear All,

Does Anybody know the answer to my question?

We have designed a universe with IDT 4.2 which is installed in a windows 7 Enterprise. We use Web Intelligence 4.2 Support Pack 6 and RDBMS as Mysql 5.3 , BO Server is UNIX

When we try to run a report in query panel in webi or in Bussiness layer in IDT selecting one measure object or indicator and one dimension object , we get this error below:

"The universe does not allow using a complex expression in a GROUP BY statement. You cannot run this query. (IES 00010)

1. Kpi numero cliente (number of clients) ->count(d60_agg_bo.edw_50_cliente_tramo_servicio.cliente_id),
2. Mes Hasta (Date in format CHAR using a function very complicated) ->{fn concat({fn concat({fn right(CONVERT (Table__2.mes_id , CHAR),2)},"-")}, {fn left(CONVERT (Table__2.mes_id , CHAR),4)})}

"Cause

A query contains a GROUP BY clause that uses formulas or aliases. The universe does not allow these expressions in GROUP BY clauses. The behavior is determined by the parameter

<Parameter Name="GROUPBY_EXCLUDE_COMPLEX">Y</Parameter>

in the PRM file for the target RDBMS.

Action

• Modify the query so that objects using formulas or aliases are not included in the query.
• If your RDBMS supports complex GROUP BY expressions, ask your universe designer to change the value of the GROUPBY_EXCLUDE_COMPLEX parameter to N."

The query which produces the error :"The universe does not allow using a complex expression in a GROUP BY statement. You cannot run this query. (IES 00010) " is this query , it is a custom query script because of the error

I could not open the view script in the Query panel in Webi

SELECT

count(d60_agg_bo.edw_50_cliente_tramo_servicio.cliente_id),

{fn concat({fn concat({fn right(CONVERT (Table__2.mes_id , CHAR),2)},"-")}, {fn left(CONVERT (Table__2.mes_id , CHAR),4)})}

FROM

d60_agg_bo.edw_50_cliente_tramo_servicio,

d60_agg_bo.dim_fecha Table__2

WHERE ( d60_agg_bo.edw_50_cliente_tramo_servicio.hasta_id=Table__2.dia_id )

GROUP BY {fn concat({fn concat({fn right(CONVERT (Table__2.mes_id , CHAR),2)},"-")}, {fn left(CONVERT (Table__2.mes_id , CHAR),4)})} -> complex expression in a group by

This query works in MySQL but in BO WEBI produces the error -> The universe does not allow using a complex expression in a GROUP BY statement. You cannot run this query. (IES 00010)

We have checked that MySQL 5 supports complex GROUP BY expresions as -> GROUP BY {fn concat({fn concat({fn right(CONVERT (Table__2.mes_id , CHAR),2)},"-")}, {fn left(CONVERT (Table__2.mes_id , CHAR),4)})}

We have followed this below:

in the PRM file for the target RDBMS.

• If your RDBMS supports complex GROUP BY expressions, ask your universe designer to change the value of the GROUPBY_EXCLUDE_COMPLEX parameter to N."

But we do not know if we have changed the accurate PRM FILE for Mysql

Where is the accurate PRM file for our target RDBMS Mysql located ?

We have changed one mysql.PRM adding GROUPBY_EXCLUDE_COMPLEX parameter to N.but the error is still there

Another thing very strange is: If we design the query in Webi using custom query script, the query with the

complex GROUP BY expressions works but if we do not use the custom query script

the report shows the previous error as you know -> The universe does not allow using a complex expression in a GROUP BY statement. You cannot run this query. (IES 00010

We thing that the custom query script should be the same query that the motor of webi produces showing us the error : The universe does not allow using a complex expression in a GROUP BY statement. You cannot run this query. (IES 00010) , because the custom query is built with the same objects that were projected into the query panel

It is the custom query script written before:

SELECT

count(d60_agg_bo.edw_50_cliente_tramo_servicio.cliente_id),

{fn concat({fn concat({fn right(CONVERT (Table__2.mes_id , CHAR),2)},"-")}, {fn left(CONVERT (Table__2.mes_id , CHAR),4)})}

FROM

d60_agg_bo.edw_50_cliente_tramo_servicio,

d60_agg_bo.dim_fecha Table__2

WHERE ( d60_agg_bo.edw_50_cliente_tramo_servicio.hasta_id=Table__2.dia_id )

GROUP BY {fn concat({fn concat({fn right(CONVERT (Table__2.mes_id , CHAR),2)},"-")}, {fn left(CONVERT (Table__2.mes_id , CHAR),4)})} -> complex expression in a group by

Thanks in advance

Best Regards

Antonio

.1.jpg2.jpg3.jpg