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!
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.
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!
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
Quote from: blom0344 on 03 Feb 2010 04:44:23 AM
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.
???
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
Ah, OK - thanks for clarifying. I guess senility must be setting in faster than I realised! :D
MF.
+60/-0
I'd say: far from senile
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