Hi all
I would welcome advice from anyone who has calculated percentages for charting or summary reporting in v8. I will explain the problem and what I have tried…
I have a JOBS table view in MySQL and each job either meets or fails to meet the Service Level expected. This is indicated by a YES/NO field in the view, called WithinSLA.
I am creating a report to show how many jobs are within service level and how many are not. The client also wants to see the percentage of jobs which are within SLA.
I lookup the total number of jobs in the OnApplicationInit event - let’s call that value “RecordsInView”. I then use a calculated field to divide by this number in the OnRecord event.
PercWithinSLA = WithinSLA x 100 / RecordsInView
Sum the whole lot up in the summary and this works fine, when you first open the grid.
However, if the user filters the grid using Refined Search, the total number of jobs (RecordsInView) will change. So if there are 100 jobs in view to start with, each job is worth
PercWithinSLA = WithinSLA x 100 / RecordsInView = 1%.
But if I filter for Client XYZ, and that gives me RecordsInView = 50, then my calc should be
PercWithinSLA = WithinSLA x 100 / RecordsInView = 0.5%.
My method to handle this is to lookup the total number of records using the {sc_where_current} field, which holds the current WHERE clause being used by the grid. I can then use this to get the current total number of records in the grid and I can do my calc.as above.
BUT… on what event can I lookup the RecordsInView again. The Refined Search doesn’t seem to have any events. The only place I can find that seems to work is OnRecord. But that means doing the total lookup thousands of times, which is way too slow given the thousands of records.
Anyone got any ideas please?
Many thanks
Jeff