Hi ,
Let me try to explain weird behavior of one report with the below example.
A list report has been developed with SQL. The SQL is "select id,salary from EMP"
The list has detailed filter with after auto aggregation that is applied to filter some of the records, which is not working as expected.
After reviewing the generated SQL, I see strange behavior for aggregate function "minimum" at having clause even though i specified Rollup aggregate function as Total.
is it possible to change the aggregation method as "Sum"?
select "T0"."C0" "ID", "T0"."C1" "SALARY"
from (
select "SQL1"."ID" "C0", sum("SQL1"."SALARY") "C1", min("SQL1"."SALARY") "C2"
from (
select id,salary
from EMP
order by id) "SQL1"
group by "SQL1"."ID"
having min("SQL1"."SALARY")>=500) "T0"
order by "SALARY" asc nulls last
Regards
RK
Hi,
I would check the data item aggregate function and see what is it set to first.
Quote from: CognosBIUser1 on 19 Oct 2017 05:14:34 PM
Hi,
I would check the data item aggregate function and see what is it set to first.
The Data item aggregation and Rollup aggregate function has been set as "Total", but I don't understand why the report is still using minimum function. Is it because SQL used in the report?
On the other hand, I created summary filter instead of detail filter with after auto aggregation. It worked as expected, but I don't know why detail filter with after auto aggregation didn't work.
Regards
RK
Do you have a determinant set in FM Model ?