Frustrated with standard reports (no DISTINCT clause), how can we use Enterprise (SQL) reports in dashlets?

Damien Pochon 

I don't know the complexities of the reports you are trying to generate but the upcoming Sugar 25 release does include a new option to show distinct results only in Reports so that might solve that specific problem for you. It depends on the specific requirements but often you can find that with some custom logic hooks and schedulers you can provide data points in the system that remove the requirement for the DISTINCT or HAVING clauses as you then have a filterable value to allow normal Reports to work.

A common example that comes to mind is a Report to show Accounts not called in the last X months which in Reports is fiddly. For this we use custom fields to record those dates on the Account and then the reporting becomes easy. Historically for that example I would point you towards a SugarOutfitters package but again that is something that is coming build in for Sugar 25.

Does any of that help at all?

Hi Liam Hastings 

That's great news :) For this particular use case we'll stick to the DISTINCT option in reports then. 

Overall we're used to having formulas, BPMs and hooks to compensate, but with a complex data model and dozens of automations the CRM is starting to feel sluggish. 

Regarding reports we will probably need more of that in the future: ways to filter data based on users assigned in distant objects, calculations that are hard to do with formulas, etc. Example: there are "levels" in the sales team. Depending on the customer level and user level, there are targets for th enumber of meetings per year. 

A gold customer will require 3 meetings per year, of which 3 needs to be attended to by the junior sales, 1 for the most senior. SO We need to calculate how many meetigns a users still need to attend to in total and per customer. That's quite hard when the team assignments are not made on the account for territory reasons :)

Cheers,

Damien