Suppose that there are the following records in pm_project_task table.
(field_for_groupby, field_a and field_b are custom field.)
| field_for_groupby | field_a | field_b |
| GroupA | 83 | 91406 |
| GroupA | 39 | 91302 |
| GroupA | 84 | 2598 |
| GroupA | 26 | 97044 |
| GroupA | 10 | 77695 |
| GroupB | 3 | 91406 |
| GroupB | 5 | 91302 |
| GroupB | 2 | 2598 |
| GroupB | 7 | 97044 |
| GroupB | 2 | 77695 |
I would like to display the value of "SUM(field_a) divided by SUM(field_b)" in the report.
However, the report designer has only the following items in the aggregation options.
What should I do ?
=====
I've added some information.
If the value I want to display is expressed in SQL, it will be as follows.
SELECT SUM(field_a) / SUM(field_b) FROM pm_project_task GROUP BY field_for_groupby
Hello, Allen.
Use function field to divide field_a by field_b,
Do you mean you just need to sum those values?
I tried to use a function field.
I set it up as follows.
(u_testing_failure_count stands for field_a, u_kilo_step_count_total stands for field_b.)
And then, the aggregation settings is as below.
GroupA total is 0.034064382.
The breakdown is as follows.
(83 / 91406) + (39 / 91302) + (84 / 2598) + (26 / 97044) + (10 / 77695) = 0.034064382
What I want to do is the sum of the field_a values of the five records in the group divide by field_b of the same group. The calculation is as follows.
(83 + 39 + 84 + 26 + 10 ) / (91406 + 91302 + 2598 + 97044 + 77695) = 0.000672138
After all, I decided to deal with it as follows.
Calculate the total value in the same group in advance, insert it into the table created to store the value, use this table as the data source in the report designer, and use function field to divide the total value of the field_a by the total value of the field_b and display the value on the graph.
Excuse me for my insufficient explanation.
Thank you, Allen.
