Microsoft SQL Server Reporting Services with ServiceNow

With the ServiceNow ODBC Driver, you can use external reporting tools like SAP Crystal Reports, Cognos Reports, Microsoft SSRS, etc to create reports.

I try to create as many reports as I can using OOB ServiceNow Reports functionality. This meets the needs of most clients. The decision to build external custom reports depends on the need for certain advanced calculations, table joins, report groupings, and formatting. Although in future ServiceNow versions this will likely be improved.

If you decide to use Microsoft SQL Server Reporting Services (SSRS), below are some helpful installation and report development tips:

1. Install SQL Linked Server

"Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server."

2. Install and Configure ServiceNow ODBC Driver

Install the ServiceNow ODBC Driver for 64-bit, or 32-bit versions of Windows. Configure ODBC Driver to connect to your Linked Server.

ServiceNow Wiki Article and ODBC Driver links

My blog article on the ServiceNow ODBC Driver

3. Install SQL Server Business Intelligence Development Studio

Your SQL Server version drives how the Microsoft reporting tools are installed. Refer to your SQL Server installation documentation, but remember that the most important step is that you need to have SQL Server Business Intelligence Development Studio installed.

4. Create Project

1. Open SQL Server Business Intelligence Development Studio
2. Click File > New > Project
3. Select "Report Server Project". Adjust Name and Location, and click Ok.

4. Right click "Shared Data Sources". Click Add New Datasource.

5. In the Shared Datasource Properties, add your connection string.

6. Edit your SQL Server connection settings. Test Connection and Save. Click Ok on Datasource Setup.

7. Right Click "Reports". Click Add New Report.
8. Step through the Report Wizard and create your report. I found that I had the best luck just creating a minimal report using the wizard, and then expanding upon that later. SSRS did crash before trying too much using the wizard.


SSRS Report Detail

Without getting into the fine detail of SSRS Reporting, here are the main aspects of the the SQL Server Business Intelligence Development Studio tool

  1. Report Data Section: here you can add images for your report, user parameters, and different datasets. The different dataset functionality is nice, you can build summary and detail sections using that.

  2. Design and Preview Section: In this area, you add the fields for your report using the Toolbox. It also has the Row and Column groups sections which you can add totals to the report. Click Preview to view the report and test your report.

  3. Solution Explorer: view your datasources and different reports. I create all my reports using the same project, and remember to back them up often

  4. Error List: this is where errors are found. Common errors are on columns that don't exist. This is pretty helpful, although sometimes I would need to close SSRS and repreview the report to remove certain errors.

Good luck writing your SSRS reports, hope this helps!