SQL Query in SN

[Shri3]

Hi,

I'm new to SN.  I have a requirement to create some custom reports

1.  Is there a possibility in SN where I can write a complex sql query and treat that as a data source?

2. can I have a multi level Pivot report with more than 5 columns?

Shri

[Community Alums]

Hi Shri,

Here are your answers:

1.  Is there a possibility in SN where I can write a complex sql query and treat that as a data source?

You used to be able to use gs.sql() to run MySQL queries in Background Script.

Navigate to System Definition> Scripts- Background or just simply type as "Background".

2. can I have a multi level Pivot report with more than 5 columns?

Yes !! you can have a Multi level Pivot reports for more columns, refer to this doc to create one :

https://docs.servicenow.com/bundle/sandiego-now-intelligence/page/use/reporting/concept/c_Multilevel...

Mark my answer correct & Helpful, if Applicable.

Thanks,

Sandeep

[Community Alums]

HI Shri,

Glad to see my answer helped you, Kindly mark the answer as Correct & Helpful both such that others can get help.

Thanks,
Sandeep

[Hitoshi Ozawa]

Hi Shri,

JDBC data source may be created with sql query statement. Will need to test to determine if "complex" as stated in the question is supported or not. 

https://docs.servicenow.com/bundle/sandiego-platform-administration/page/administer/import-sets/refe...

As Sandeep replied, multilevel pivot reports are supported.

Will need more explicit details to fully answer the question fully.

[Shri3]

Thanks for the replies.

The requirement is something like this

We are implementing ITBM and currently trying to map our portfolio planning process.  One of our existing report has a lot of data, which are spread out in different tables on SN

e.g., Projects, demands, resource plans - estimation vs actuals, quarterly, monthly, year aggregated by resource groups, roles, resource names etc.,

My question is : I want to replicate that report in SN by combining various tables and aggregating them at SQL level and using that query to create a report in SN.  We want all this to be done within SN

This query may return around 25 fields and I want to use those 25 fields to create a pivot report

which has around Year/quarter/month/weeks as columns --> estimation vs actuals as data

project/demand, group, roles, resource name, priority, state as rows

Is this possible?

Shri