Could I Show "SUM(field_a) / SUM(field_b)" value in report?

TI5
Kilo Contributor

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_groupbyfield_afield_b
GroupA8391406
GroupA3991302
GroupA842598
GroupA2697044
GroupA1077695
GroupB391406
GroupB591302
GroupB22598
GroupB797044
GroupB277695

 

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.

find_real_file.png

 

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

 

5 REPLIES 5

Allen Andreas
Administrator
Administrator

Hello,

You'd have to consider creating a function field: https://docs.servicenow.com/bundle/sandiego-now-intelligence/page/use/reporting/concept/function-fie...

Please mark reply as Helpful/Correct, if applicable. Thanks!


Please consider marking my reply as Helpful and/or Accept Solution, if applicable. Thanks!

Thank you for reply !

You can add a value to other field within the same record, but you can't seem to add a value to a field of the other record.

Please excuse my lack of explanation. 

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,

I'm sorry, your reply doesn't make any sense to me.

I don't know if you're replying to me meaning you're trying to use a function field, as I mentioned, which if so, would be helpful.

Otherwise, I don't know if you're telling me something else you're trying to do.

Please mark reply as Helpful/Correct, if applicable. Thanks!


Please consider marking my reply as Helpful and/or Accept Solution, if applicable. Thanks!

TI5
Kilo Contributor

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