Reports and large data issue

24 views
Skip to first unread message

Dean D. Babic

unread,
May 8, 2024, 2:20:54 AMMay 8
to Jam.py Users Mailing List
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.
Reply all
Reply to author
Forward
0 new messages