Finding records based on summary values for charting

[michael3785]

I am trying to make some charts to show revenue by client over the last year or so. In principle, I have a working solution with the relevant summary fields, layouts, etc. The problem is that I have a lot of data and the way I was doing this is not working any more - it just takes too long.

In the last year I have about 60,000 invoice records and 2000 client records. I am trying to show revenue and profit for these clients based on totals from their related invoices. I have a layout to show all this data but basically I need to filter out data for a load of the clients who are less important. Ideally, I want to be able to show the data by client for all clients who represent 1% or more of renevue (probably about 30 or so) and just put the rest into an "Other" column.

My previous solution used old charting techniques using flash files, etc. I would generate the data values by literally looping through all the invoices, using GetSummary (breaking on the client id, each time I got to a new client in the list of invoices I would get the summary and carry on) to get the data I needed and as soon as the GetSummary value was below 1% of the total, I could just put the rest of the data into the Other column.

I would now like to get to something similar using the new FM11 charting function. I have the chart working fine but of course with 2000 clients, it is a bit useless as you don't see any meaningful content in the chart as it is so bunched up along the X-axis.

I was thinking maybe to exclude Invoices that belonged to a client that was in the less-than-1% category as a start, but you can do a find based on a calculated GetSummary field. The GetSummary data seems to disappear when you execute a find (not suprisingly I guess).

Any suggestions on how I could make some progress on this type of problem (ideally without a loop through my invoices in there!) would be much appreciated.

Many thanks

Michael

Edited June 28, 2010 by Guest