Report Grouping and Totals Not Working

đź’¬ General Discussion
1

I think I’m doing everything right in setting up a report. I’m trying to run a report of Opportunity Amounts and group it by Product Line. Product Line is a custom field.

Couple of issues:

  1. If there are more than 20 rows, the group total only sums up what is on the first page (20 rows) and there is no overall total for the group.
  2. Running a grouped bar chart just doesn’t work. It doesn’t sum up the value of Opportunity amount, it seems to be just grabbing a random value for one of the rows.

The report is sorted, and grouping properly, my issue is with the totals and also with the report.

Attaching a file doesn’t seem to work, so my settings are:
Main Group: Product Line
Product Line sorted by Ascending
Opportunity amount: Total=Sum

Can’t understand what I’m doing wrong. Any one can help?

2

Hi; did you ever get any resolution on this one? I can get group totals but not a grand total, which I think is the same problem you are having?

3

Hey, no I’ve not been able to get grand totals of all the groups. I’m pretty sure this used to work in previous versions. Hoping someone has some ideas!

4

Yes, I am having similar problems; there is a video that may be of help:

https://www.youtube.com/watch?v=PxTDjZkjjs8

but it still doesn’t address the groups of groups and subtotals/grand totals issue that i (and you) i think are having.

5

Looking for the exact same thing, just a grand total… If I found something I’ll post it here

6

Does this recent thread help?

I have a screenshot of settings there to create a grand total, I’m not sure if that is the same thing you want.

https://suitecrm.com/suitecrm/forum/suitecrm-7-0-discussion/21372-reports-bar-chart

7

When we (I?) mean a grand total, I mean this :

I’ve succesfully display the total of every group (here its group by client). But I want the total of every client.

Like 1.762.25 + 609

For now, with a chart I can have what I want but Its completly stupid to do this with a chart…

8

Is that a Report? Or are those subpanels?

9

Oh i’m sorry to not precise it, its a report !

10

Ok, and have you tried something like this? That’s a report too, not a chart.

https://suitecrm.com/suitecrm/forum/suitecrm-7-0-discussion/21372-reports-bar-chart#75998

11

Yep, And it sum the total of the subpannel only… Not a grande total of all the sub pannel…

Want to have a grande total of all my company, right now it sum only company by company (sub pannel by sub pannel…)

The only way I find to have what I want is to not group by company … But it make the report hard to read… I will have like 3K company, If I dont group it It will be total madness lol

12

This might sound stupid, but what if you just make 2 reports, one with the groupings, and the other one with just one number, the Grand total?

Then you can show both one after the other, in a Dashlet or in a custom entry-point.

Another alternative is to skip this Reports module altogether and go with Google Charts, which is what I always use now.

See https://suitecrm.com/suitecrm/forum/developer-help/20227-how-to-add-other-charts-in-report-charts-for-ex-line-chart-bubble-chart-gauge-chart-funnel-chart-etc#72572 and then my final version of googlecharts.php a few posts after that.

13

Your first option is not so bad, I’ll note it, It can be an option. But My report have a condition with a parameter so the 2 report will not be linked …

With your sec option, I can maybe do an SQL request with the sum of a fields ? I will give that a try too …

Another option I just discover now is “kreporter”, just discover this free plugin, maybe in this one I can do a grand total of a field ! I will definetly download it when my suiteCRM will be updated to 7.11 .

Thanks for your alltime support PGR !

14

The Google Charts version requires a bit of tweaking in the PHP file, but it is all very straight-forward, and it gives you every bit of freedom. You just run SQL and print the results in HTML on screen. You can do that “manually” if you just want to print a number, or you can use all the fancy Google Charts to achieve more elaborate results.

Note that Google Charts includes a format (a chart type) that is actually a table: https://developers.google.com/chart/interactive/docs/gallery/table

I also like to use these because they are more compact, more configurable, and interactive.

15

following

16

Still Issue 1: Group By report with SUM in paginated results would still produce wrong values just showing the sum of displayed records.

Issue 2: The Name of Group By field is used as title rather than the Display Value. Issue logged https://github.com/salesagility/SuiteCRM/issues/6307

Report-GroupBy
Report-GroupBy2376Ă—3298 754 KB