I don't speak portugese and google translate can only do so much...
Anyways from what I can gather you seem to be having an issue with pre/post aggregation. Aka physical column formulas vs derived columns.
Thank you for your attention.
The problem it happens just in the total line when is used the column with (CASE WHEN).
I put a new image in the attachment
Agregação_English.png 303.8 K
You've let the totals aggregation rule to "Default" - Isn't it an AVERAGE that you want?
The aggregation rule is Default. This is correct.
The problem are in the total line. I think the problem is in the SQL (GROUP BY) when calculating the total row.
The aggregation rule can´t be SUM or AVERAGE because the rule is not that.
Just in the total line is necessary perform the calculation:
% Exc Parcial/Vendas = (Exclusão Parcial / Vl Implantacão) * 100
Example: % Exc Parcial/Vendas = (1.835,19 / 23.145,01) * 100 RESULT: 7,93%
The result for the example above should be 7,93% but the reports show me 0,00%
Ok basic question I should have asked in the beginning: why not push the logic to the RPD and let the RPD take care of aggregation?!
Because the value of the column "Exclusão Parcial" depends on the period selected by user in the prompt.
As I do not know what time the user will tell , I can only calculate at runtime.
Thank for your attention but this problem is not easy to solve. I tested all aggregation options.
I opened an SR with Oracle and now i am waiting the return of them.
I hope to help me because i don´t have more idea for to find the solution.
It's something that's quite hard to reproduce without your data set etc.
It's strange that the "server complex agg" doesn't yield the desired results.
It works but just when i use one simple column (without CASE WHEN)
Well yeah for other columns that's the minimum I expect
The issue was solved using the solution below.
Example: CASE WHEN sum(A)> 0 THEN sum(B)/sum(A) * 100 ELSE 0 END
CASE WHEN "- Fato Desemp Unidades Venda"."Vl Implantação" > 0
THEN ((SUM(CASE WHEN "- Fato Desemp Unidades Venda"."Dt Implantação" BETWEEN Min("- Dimensão Data Movimento"."Data") AND Max("- Dimensão Data Movimento"."Data")
THEN "- Fato Desemp Unidades Venda"."Vl Exclusão"
ELSE 0 END
) / SUM("- Fato Desemp Unidades Venda"."Vl Implantação")
) * 100
ELSE 0 END
and the aggregation rule: Server Complex Aggregate