Calculation efficiency in Impromptu 6.1 1

[DrSimon]

Hi Guys
I am writing some reports with multiple calculation using IF statements where the logic of the expressions is the same. Does anyone know if it's more efficient to stay like this or create another calculated data item (say called 'Q') which just tests the logic giving a simple answer and then use 'Q' as the simple logic test for all the other calculations?

I'm not sure that's clear so here's a couple of examples:

Either
Item 1If (A=B AND C=D AND E=F AND G<>H) then (S) else (T))
Item 2If (A=B AND C=D AND E=F AND G<>H) then (U) else (V))

Or
QIf (A=B AND C=D AND E=F AND G<>H) then ('Y') else NULL))
Item 1: (If Q is not missing then (S) else (T))
Item 2: (If Q is not missing then (U) else (V))

If it matters I'm using SQL.

Thanks in advance

Simon Rouse

 

[griffindm]

Simon,

It's certainly simpler in Impromptu to calculate a complex column once and then use it by reference throughout the report. However the SQL generated (assuming the calculation and platform combination allow the formula to be passed to the SQL) will still repeat the first calculation each time it is used, so it has little impact on report efficiency from a database perspective.

If you are on MS SQL Server or other SQL92 compatable RDBMS the If-Then-Else should be parsed as a Case When select, and the database will do the work. The extra step of using a NULL check is redundant. If you are on Oracle and have NOT turned off the automatic If-Then-Else to Decode translation, the statement will be parsed into a Decode statement, but possible a complex and ugly one. If you are on Oracle a better solution is to turn off the automatic Decode translation (discussed in other threads here - use the keyword search) and manually write the calculation with Decode. If you were using OR operators it would be a single Decode, but with AND it's more complex, as in:

Decode(A,&quot;B&quot;,Decode(C,&quot;D&quot;,Decode(E,&quot;F&quot;,Decode(G,&quot;H&quot;,&quot;T&quot;,&quot;S&quot,&quot;T&quot,&quot;T&quot,&quot;T&quot ...

It's still ugly, but I doubt the automatic function conversion would be a legible. Passing two calculations with the NULL check will only result in more effort by the database.


Hope this helps,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[DrSimon]

Thanks very much Dave, a very full answer. Your answer re If Then Else was much as I thought. It's rather a suck it and see whether the improvement in report legibility and robustness is offset too much by a drop in performance. I suspect in these days of enormous amounts of RAM and processor power it won't make much difference.

As regards Case Select, I'm working on a proprietary database and can't do anything at the SQL level. But thanks for the suggestion.

Simon Rouse