I read here about executing a subquery in Kreports: https://community.sugarcrm.com/thread/24319. How does this work?
I’m trying to put together a report using the lead_audit table. We’re just interested in one field (a dropdown status field).
I’d like to be able to show for any given date: how long have leads been resting at the status on that respective date?
(I’m then interested of course in average, max, min per status over time ).
I’m not coming up with a way to do this in kreports. I can group by lead ID using min/max on the audit.create_date field, but then the corresponding field value (after_value_string) is taken randomly, or rather according to how the table is sorted but not according to the min/max of audit.create_date field. I suppose if I could choose the last entry (lowest in the table) that would work, is there a simple way to do that?
Otherwise I feel like I’d need to choose the after_value_string that belongs to respective max(audit.date_created) per ID by subquery. Just not sure how to do that…
Hope this isn’t too confusing. Thanks for your thoughts! Also if there’s some add-on that might be interesting for this kind of analysis.