How Do I Report On Aggregates and Calculated Fields

[johnfeist]

I'm hoping someone can point me to documentation on how to do more complex types of reports.   I'm out of the SQL Server world and need to be able to produce reports where the query might look something like this:

SELECT a.FieldA, SUM(a.FieldB) TotalB, AVG(b.FieldC) AvgC, SUM(CASE WHEN d.FieldD > 5 THEN 1 ELSE 0 END) OverCount

From tableA a LEFT JOIN tableb b ON a.FieldA = b.FieldA

                                            LEFT JOIN tableC c On a.FieldA = c.FieldX

                                            LEFT JOIN tableD d ON a.FieldY = d.FieldZ

Is this the type of reporting that goes beyond what ServiceNow can do?   I know that I can do the joins in a database view, but what about aggregations and calculated fields?

Thanks for any insights you can provide.

John

[sachin_namjoshi]

You can create metric definition to build this kind of reports.

https://docs.servicenow.com/bundle/geneva-performance-analytics-and-reporting/page/use/advanced_repo...

Regards,

Sachin

[johnfeist]

Thank you Sachin.   This gets me started and I should be able to create the view linking Incident to one or more metrics.   What I still don't see is how I would go from there to showing a tabular report that might look something like this:

Assignment Group Number Created Number Closed Average Time to Close Min Time To Close Max Time to Close

Or a report showing something like this

Assignment Group Total Open Incidents Open Under 30 Days Open 30-60 Days Open 61-90 Days Open Over 90 Days

I know I can pull data into SQL Server to do that type of reporting, I'd like to keep that as a last resort option.

Best regards,

John