SQL Sum Needed for Summary Report but Slow to Update

Discussion created by bradleyboggs on May 14, 2014
Latest reply on May 14, 2014 by erolst

Thank you in advance for any help you can provide!


Currently, I have a Daily "PO Log" report, which lists the PO's we've received on a daily basis. This report is called through a find of "Enter Date" = "Get(CurrentDate)", then sorts by region and gives a sub-summary total for each region, and then provides 2 totals at the bottom - a Daily Total which is a summary total of all the orders listed, and then a Monthly total.


When i first made this report as I was just starting out, the only way I could get this to work was by making a calculation field called "MonthExt" with this "If (Month (EntDate) = Month(Get(CurrentDate)) and Year (EntDate) = Year(Get(CurrentDate)); ExtPOCost; "" )", then having a separate calculation field called "MonthlyTotal" with this: "ExecuteSQL ( "SELECT Sum ( MonthExt ) FROM Orders" ; ""; "")"


Referring to this post as I did it:


I had to do it this way, because if I made the "MonthlyTotal" field a summary field or a calculation field with a SUM in it, it would give me the total for the newest record, but not the total for all the month's records.


When I first made this using the SQL method, it worked fine. The math continues to add up correctly. The only caveat, is that now that the database is growing since we put it into place in October, this SQL sum is a bit sluggish and doesn't always update immediately (often fairly quickly, sometimes up to 5 or 10 minutes after the final order is entered it still hasn't updated).


I know I could set this to evaluate manually with scripts/triggers, but that doesn't seem like a great solution either.


Fool hardedly I tried to "filter" the records at the relationship level with different TO's (as you would with filtering portals at the relationship level) so that it wouldn't have to evaluate the full record set, but I'm not using portals for this data since it's a report and it doesn't work without portals.


If you have any tips, I'm all ears! Thank you again if you made it this far!