Slow process when using Total Function

[reginoilano]

Hi,

Im using the Total function to get the amount per region "Total([amount] for [Region])" but it really takes time to generate the report where in the database it only runs for a few seconds..

do you have any techniques in using this function more efficiently?

Thanks!

[imts]

FOR clause is interpreted as "PARTITIONED BY" clause in SQL which is a heavy operation.
Alternatively, you can group the report on REGION and Set the Aggregate function as TOTAL for AMOUNT.

[reginoilano]

How can I group the region on a calculation? do I need to create a separate query or just create a logic condition within the calculation?

Thanks!

[blom0344]

You simply group on region and set the aggregate (should be there already for a true fact). This leads to a regular 'group by' in the SQL instead of a partition phrase

[MFGF]

You simply group on region and set the aggregate (should be there already for a true fact). This leads to a regular 'group by' in the SQL instead of a partition phrase

This is specific to Oracle, though, if memory serves.  What database are you using?

MF.

[blom0344]

 ???

Just checked, but in my case on SQL server it generates nothing more than an ordinary group by.
(regardless whether I add a grouping in the list or not)
Partitioned by would be generated for a subset (as triggered by the for clause) within a set.
There is no distinction here between RDBMS AFAIK

[MFGF]

Ah, OK - thanks for clarifying.  I guess senility must be setting in faster than I realised!  :D

MF.

[blom0344]

+60/-0

I'd say: far from senile

[MFGF]

Ah - you're too kind!  It's amazing how a run of lucky guesses can can be misinterpreted to make people think I know what I'm talking about!  ;D