This is the third and final blog on how to use ServiceNow ODBC drivers for Excel reporting. The first blog was a general overview, and the second blog about how to install and configure the driver. In this blog I will show you how to import data from ServiceNow to Excel using the ODBC driver.
Build Microsoft Query
1. First, we will need to build a MS Query to be able to use the ServiceNow ODBC Driver as a data source provider in Excel. I’m using MS 2010.
2. Select ‘ServiceNow’ as your data source, un-tick ‘Use the query wizard to create/edit queries’ and click on OK.
3. Enter your login details as shown in the example screen shot below and click OK.
4. From ‘Add tables’ window, select data tables (e.g. contract_sla, task_sla and incident) and click on Add button. Now we will see three tables that we selected in the query pane.
5. Click on close button in the ‘Add Query’ window to close it.
Then, select the data that you would like to create into a table:
1. Create joins (similar to Database views on ServiceNow) where needed - you can see a line between these two tables.
2. Enable filter pane by clicking on filter button (e.g. to pull only this year’s data).
3. Select fields from the selected tables as required adding them as table columns.Note: To see display values for reference fields and list fields, use fields that have a prefix of ‘dv_’ to field name. For example, to have priority as a column instead of ‘incident.priority’ use ‘incident.dv_priority’.
4. If you want to save this query then click on File > Save As. If you want to run this query and load data into excel, then File > Return Data to Microsoft Excel.
Create a (Pivot) table from the selected data
Finally, let’s create a table from ServiceNow data. In this example, I will show how to create a Pivot Chart but you may have other requirements; Excel has a huge amount of options for various graphs OOTB.
1. Show the data in a table as shown below and click OK.
2. Create this into a Pivot Table in new sheet. Insert > Pivot Table > Pivot Table
3. Then, select the table range, select New Worksheet and click OK.
4. Drag and drop the fields into the pivot boxes of Report filter, column and row labels and values.
5. In this example, we convert the count values to percentage by placing the cursor inside the pivot table, Pivot table tools > Options > Show Values As > % of Parent Row Totals. This is a handy tool when you want to compare the numbers quickly.
6. Finally, remove grand and sub totals to make report look cleaner and neat!
A pivot table in Excel, created with data from ServiceNow utilising an ODBC driver.
As mentioned in my first blog, you can now share this report; the data can be updated by those with access to ServiceNow only.
The Wiki page is useful source of information on using ODBC Driver in Excel.