Function Fields in ServiceNow Reports: Calculate and Display Data on the Fly

[AnveshKumar M]

Introduction:

Function fields are a powerful feature in ServiceNow reports that allow you to calculate and display data on the fly, but this is under used feature because of lack of awareness. This can be useful for Performing calculations on existing fields.

Function fields are not stored in the database, but are instead calculated on the fly when the report is run. This makes them very efficient and flexible.

Creating a Function Field:

To create a function field, navigate to All > System Definition > Dictionary and click New. Select the Function field and fill in the following fields:

• Name: The name of the function field.
• Table: The table that the function field will be associated with.
• Function definition: The formula that will be used to calculate the function field value.

The formula can include any valid ServiceNow function, for all the available functions refer the documentation here.

Example:

The following function field calculates the duration between closed time and created time of an RITM:

Formula: glidefunction:datediff(closed_at, sys_created_on)

This function field could be used to create a report that shows the average number of days to fulfill service requests.

Using Function Fields in Reports:

Once you have created a function field, you can use it in reports just like any other field. To do this, simply drag the function field from the Report Fields pane to the report.

You can also use function fields in filters, breakdowns, and other report elements.

Benefits of Using Function Fields:

There are a number of benefits to using function fields in ServiceNow reports:

• Flexibility: Function fields allow you to calculate and display data in any way you need.
• Efficiency: Function fields are calculated on the fly, so they do not impact the performance of your reports.
• Scalability: Function fields can be used to create complex and sophisticated reports without having to modify the underlying database schema.

Conclusion:

Function fields are a powerful and versatile tool that can be used to enhance the capabilities of ServiceNow reports.

Tips for Using Function Fields

Here are a few tips for using function fields in ServiceNow reports:

• Use parentheses to group formula elements. This will help to ensure that the formula is evaluated correctly.
• Test your formulas thoroughly before using them in production reports. You can use the Report Preview feature to test your formulas and make sure that they are working as expected.
• Be aware of the performance implications of complex function fields. If you are using a complex function field in a high-traffic report, you may want to consider using a calculated field instead.

[Navaneeth1]

I tried this but the duration is showing 0 days 0 hours etc for me. Is the code used correct?

[Pankaj Jadhav]

how can we get current date to calculate actual age in function field

(gs.dateDiff(current.sys_created_on.getDisplayValue(), gs.nowDateTime(), true))/84600 ;

[James Kailukai1]

You (certain "role" users) can create these directly in a calendar report by using the "Configure function field". 

This actually creates new fields on the table it is associated to.

The feature is convenient, but if not used in a "smart" way, you have the potential to have a lot of these function fields added to your tables before you know it. 

[sagarshinde]

@AnveshKumar M , @James Kailukai1 

In Functional  dictionary entry (incident,problem table)  field value should be record created Month only (e.g April. May).

I tried with below glide function it does not work. suggest on this

glidefunction:new GlideDateTime(current.sys_created_on).getMonth()

Thank you!

[Tim Deniston]

I found that using "now()" as one of the inputs allows you to compare a date/time field with the current date time. The 'Type' field should be "Duration" and the 'Function definition' field would be this: 

glidefunction:datediff(now(),sys_created_on)

That's not documented anywhere (not surprising, unfortunately), but it is now! Make sure the "earlier" date/time is second in the input list, otherwise, you'll get an odd result. If you needed to display the duration since a certain field value, use what is above. If you need to display the duration to a certain field value, like Planned Start Date on a change request, use this: 

glidefunction:datediff(start_date,now())

Let me reiterate that this only produces good values if the first input is "later" than the second input. If your use case requires something else, I think you'd need to use GlideDBFunctionCaseBuilder and cross your fingers that you can figure it out.

[Elizabeth10]

@Tim Deniston  I am getting this error message when I use now()
now is not a supported function , Script error in script

any ideas of how to solve this? 

[Tim Deniston]

@Elizabeth10 Can you post a screenshot of how you're using it? Here's a screenshot of how I'm using in a custom field called "Duration since creation":

I'm curious -- Is your field in a application scope other than Global? 

[Elizabeth10]

@Tim Deniston  I am just trying to create it in a report in the Global application.

[Tim Deniston]

It looks like starting from the reporting interface might not work directly. It's trying to be helpful, but it's not. 🙂

If you create the new function field from that interface, you need to use something it thinks is valid, like this and then update it elsewhere:

glidefunction:datediff(sys_updated_on,sys_created_on)

You can then go to the table's dictionary list and update the value to "now()". 

1. Type "sys_dictionary.filter" in the navigator. 
2. Apply the filter: Function definition > is not empty AND Table > is > [whatever your table is]
   
3. Open the correct dictionary entry.
4. Replace the placeholder field with "now()". 
   
5. Save the dictionary entry.

[simonvolkwe]

Hello together,

is there an easy way to check if any field value is null/empty inside the function?

I have this function which is calculating the duration between start and end date of a project.

TIMEDIFF(u_start_date_new , u_end_date_new)

It is not breaking my instance but i have many error messages and i want to get rid of them by checking for null/empty before the function is calculated.

Greetings

Simon