Reports and large data issue

[Dean D. Babic]

Hi, 

Thought I’d share some info about this issue.

When the data is quite large, Python is not the best way to 

generate reports. Meaning using set_where, copy(), rec_count, etc.

This approach can be seen on Northwind example (Reports\Yearly Sales). It works really well as an example on small data.

With large data, the better approach would be to use SQL as seen on Reports\Inventory List SQL.

The exact SQL, which does quarterly and yearly report:

SELECT YEAR(order_date) [year],

(MONTH(order_date) -1)/3 + 1 [quarterly],

SUM(total_price) gross_sales

FROM ORDER_DETAILS o

INNER JOIN ORDERS i ON i.order_id = o.order_id

--WHERE shipped_date IS NOT NULL

GROUP BY YEAR(order_date), (MONTH(order_date) -1)/3 + 1

order by [year];

In the report template, we just need to set a summary.

Again, pls see Inventory List.

Cheers

D.