Variable xxx is invalid in the SELECT list/HAVING clause because it is not contained in an aggregate function and there is no GROUP BY clause

[Former Member]

Experts need your help.
 
We are using BO 4.1 SP04 FixPack4.4. We have a multi-source universe and single source universe. database is SQL server 2012
 
We have a scenario, where for one object we have to use case statement based on a condition and give value accordingly.
 
my objects are defined as below;
 
object => A - Measure - sum(bill_amt) - projection none
object => B - Measure - Max(adv_amt) - projection none
object => C - Dimension - Trn_Code
object => D - Measure - IfElse(C = 'A', B, A)
 
object D is is not getting validated by giving error
 
Variable 'Table__1.Trn_Code' is invalid in the SELECT list/HAVING clause because it is not contained in an aggregate function and there is no GROUP BY clause
 
How do we resolve this. Is any of the object being defined wrongly? When I remove 'sum' and 'max' from the object it works fine but result are not correct.
 
Please advice how do we fix this.
 
Thanks in advance

[Former Member]

Just an update. Based on advice from one of the kind soul I tried below as well;

Object definition of D = sum(IfElse ( @Select(C)= 'L0001' , @Select(B), @Select(A) ) )
  

However I am getting error as "Cannot perform an aggregate function on an expression containing an aggregate" since 

object A= sum(Bill_amt)
and object B = Max(adv_amt)

So adding sum is creating this error if I give it without sum then error as given in original post is coming.

Regards

[Former Member]

Hello,

I think, the problem is, that both Measures are from the same table.

Try to derive the Measure A and Measure B form different tables (use Alias Table).

Then modify the definition of the measure and it think, than it works.

Regards

Andreas

[Former Member]

Hi Andreas,

Thanks for reply, no luck even with this problem remains same.

Regards

[Former Member]

Hi,

try this: create a derived table like this:

select Trn_Code, case when Trn_Code= 'L0001'  then Max(adv_amt) else sum(bill_amt) end measure

from table

group by Trn_Code

Than define your attribute as derived_table.measure

Regards

Andreas

[Former Member]

Dear Andreas,

This suggestion works If I have one or two such scenario. I have approx 97 such condition which are selected based on specific reports. Having so many derived table will impact the IDT performance.

Regards