Using Function Fields in Report Aggregates

[johnfeist]

I have a function field (Resolution Days) that converts calendar_stc into days.  When I try to use it as a column in a report, all is good.  However, when I try to use Resolution Days in a pie/donut chart where the aggregation is Average Resolution, I get an ACL violation.  Is there a restriction around using function fields that way?

[Craig Tinsley]

@johnfeist .. I was able to get this to work. Here was one of my scenarios:

1. I created a report on the "change_request" table

2. I created a function field called "Change Duration" (on the change_request table).

3. Return Type: "Duration".

4. My glide function: glidefunction:datediff(end_date,start_date)

When I ran this with the "List" report type, it worked just fine, BUT when I attempted to do a score (using an average of my Change Duration field), it gave me the ACL error.

Here was my issue: there are several existing OOTB ACLs for incident (in particular, "incident.*" / Operation: "read"). When we deployed ServiceNow last year, someone added a condition script to "incident.*", and in reviewing the ACL Function Field rules, I noted that you CANNOT have an incident ACL / Operation: "read" with a script. When I deleted the script from the ACL, my aggregation worked. While that may be one solution, we NEED that script (for other reasons), so I had to do a work-around... Here's what I did

1. I created (7) ACLs:

Name: change_request.end_date - Operation: read

Name: change_request.start_date - Operation: read

Name: change_request.end_date - Operation: report_view

Name: change_request.start_date - Operation: report_view

Name: change_request.u_rpt_change_duration_2 - Operation: read

Name: change_request.u_rpt_change_duration_2 - Operation: report_on

Name: change_request.u_rpt_change_duration_2 - Operation: report_view

NOTE: the "u_rpt_change_duration_2" is the system generated name of the column (aka: function field) in the table, so I had to go to the change_request table and get the ACTUAL name of the column

Once I completed those things, aggregation worked again.

Hope this helps - good luck!