Can I create calculated fields on a Report?

ewilks
Giga Expert

I am trying to write a report where I return rows with number data fields. It doesn't matter from which table I'm getting the data, as this is a more general question.
In any case, I would like to know if it's possible to do any of the following.
1. calculate a field in the report: I get two number fields from a table and would like to, say, add the columns to display in a new column.
2. Create a new column and populate that column based on data in another column: e.g. If Column1 = blue then CalculatedColumn =12 else CalculatedColumn =6)
3. Count distinct: Retrieve a number of rows that would include data from a report accessing Tasks, Request Items and Requests, then find how many distinct Request Items there are (for instance, let's say I have 20 tasks for 16 RequestItems which were part of 10 Requests...then I'd like to see a distinct count of 10 Requests.

It seems like it would have to be possible, but if none of these is possible in a standard report, is there another approach to find this data? Please keep in mind that I'm just getting started in this and if you give me code, I won't have any idea where I would write or call the code.
Thanks,
Eric

13 REPLIES 13

mduluk
Giga Expert

I second the custom chart app.   We used it to make a pie chart of a ticket to show how many were completed before or after a due date.   It is a little tricky to get used to, but once I played with it a bit, I was able to calculate what I need and return those values.     The drawback is that it only recalculates on a predetermined interval, and I can not click through to see the records since the values are just totals and not actual record links.


cerretan
ServiceNow Employee
ServiceNow Employee

Another request here from a customer of mine.   Has anyone made an enhancement request?   Any feedback?


gyedwab
Mega Guru

If you want to generate those calculations, you can use Explore Analytics to apply formulas against the data. For example, here's a simple report that is generating the % availability based on the total duration of outages:



https://my.exploreanalytics.com/pub/view/7d34c674e18c4edba0d5de14b156e749



It's using a simple formula builder (pictured below), and it calculates live on-load (so it'll be up-to-the-minute). You can publish it to ServiceNow dashboards and CMS pages natively.



You can give it a try for free.


Screenshot 2015-01-29 14.07.21.png


gyedwab
Mega Guru

Also, in comparison with custom charts, any user can build these reports and they don't require any javascript, even in the calculation.


cerretan
ServiceNow Employee
ServiceNow Employee

Thank you Guy - is Performance Analytics required?