PDA

View Full Version : Conditionally evaluate TotaFormula calculations



Pepe
9-Jul-2020, 06:14 AM
Hi,



In a MSSQL based report we have different columns and some of them are Total Formula calculations.


We need an extra columna whose vale is conditional to any of those Total Formula calculations. Something like


iif ( ([[Adicional]]>5.0 ), 1, 2)


In this case the result is always zero (0).


Do conditional sentences evaluate Total Formula columns correctly? It does work if it's an SQL column


How can we solve this?


Regards


Pepe

Bob Cergol
10-Jul-2020, 05:15 PM
The short answer is no, you can't use conditional expressions in a total formula.

The solution would be to make the data source of the report an SQL view that does the aggregation you need. It would involve one query to aggregate the needed values, and then another one that uses that query as a derived table. That "outer" query could have a column defined by a conditional expression using the "total" column from the "inner" query.

Regards,
Bob

Pepe
14-Jul-2020, 08:44 AM
Thnaks Bob.

We are aware of that solution but it would simpler to be able to use conditions on Total Formulas....

Perhaps we can have that as a suggestion for V 7? :-)

Salu2

Pepe

Bob Cergol
20-Jul-2020, 10:16 AM
Hi Pepe,

Don't forget about drill-down reports. So you could do the conditions-based aggregation(s) you need in a view, make a report on it, and then drill down from any row into a related report, that would show all that your current report is showing, minus those conditions-based totals. There is precisely such an example available in the Dynamic AI Live Examples accessible from the standard Web Order entry example, report menu that ships with the DF Studio, or direclty via this link:
http://www.dataaccess.com/LiveDynamicAI

The ranking report uses a dyn-view that does the totaling. The full details how this was built are in this blog:
https://www.dataaccess.com/blog/dynamic-ai/how-to-make-dynamic-variable-top-bottom-ranked-drill-down-report-using-dynamic-ai-916


13843

Also, remember, a report package/dashboard is often a solution for solving these kinds of challenges. One report shows one set of totals, another shows a different drill down path, and both are syncronized using common report filters.

Regards,
Bob

(http://www.dataaccess.com/LiveDynamicAI)

Pepe
20-Jul-2020, 11:58 AM
Hi Bib,

We initially gave them the drill down solution to another report but the possibility of conditionally evaluate a Total Formula calculation seemed so much simpler that he wasn't very happy with the drill down solution.

As I said, perhaps Carsten would consider it for V 7 :-)

Salu2

Pepe