How to force your Sugar data into a particular shape for reporting

HomeHow to force your Sugar data into a particular shape for reporting

SugarCRM has an excellent reporting engine. A lot of people don’t realise quite how powerful it can be. (The ‘Oh, no way!’ moments are still as rewarding as ever when people see how they can flex their data.)

You can dice your data up in so many ways that pretty much your imagination is the limit. Want to see which salesperson holds the most calls on any given day/week/month/year? It’s right there. Want to see that split by inbound/outbound? Change your grouping; it’s that simple.

One of the things we’ve found is that there’s no way of combining multiple dropdown options (for example) into something that I’ll broadly call a ‘This or That’ report.

You know the classics: PlayStation or Xbox? (PlayStation); Pepsi or Coca-Cola? (Coca-Cola); Tennis or Never Watching Sport Again? (Never Watching Sport Again). In this instance, you might want to see a black and white picture of your Lead conversion % or Opportunities >50% likely to land where your first five statuses are ‘No’ and your last six are ‘Yes’.

Sugar reporting

You can achieve the first by doing a summation report and grouping on the ‘Converted’ flag. However, the latter requires a bit more work.

You’ll need to create a check-box type field on the module(s) you wish to run the report on and enter the following formula in the ‘Calculated Value’ box.


Once this is in place, you’re halfway there

Return to the List View, and using Sugar’s List View selection (either a sample of the checkboxes or all of the records up to 1,000 by using the top left ‘select all’) and alter the record(s) in some way. Perhaps a recalculation of values or, slightly more convoluted, is to add a tag and then remove it using mass update. However, these will impact the Date Modified, so be careful if you report on this field elsewhere.

If you create a Summation (with Details) report and group it on your new checkbox, you will see that the old data is in one of the two buckets you’ve created. A drawback is that you’re looking at ‘True’ or ‘False’ as the group labels, but it’s better than not having it at all. Perhaps you could phrase the report title such that it makes sense? That’s for the reader to decide.

Of course, you could ignore the above and ask us to handle it all, which we are happy to do.

We could even write a script to set the true/false flag without impacting the date modified if you wanted.