SQL Reporting on NetSuite Data Step-By-Step

We are enthusiastic about NetSuite’s business management solution as we finally see a fully integrated ERP/CRM/eCommerce system available for the small to mid tier.  While all the data is integrated, there are some limitations with the built-in reporting tools that limit access to certain domains of information at the same time.  For example, a prospective client that delivered long-term professional services needed to see both forecasted business and actual (committed or won) business on one report.  This report would help management assess the project resources they will need over a long time horizon by seeing what projects are committed and what will be needed for anticipated projects.  NetSuite’s built-in tools, while quite good in terms of end user reporting, can not get this information (forecasted and sales) to land in one report.  The trick to get to this data is through ODBC.

This post is about our Remote Reporting Service designed to specifically address these types of concerns.  We will discuss the general steps to connect to NetSuite from SQL Reporting Services.  To learn more, visit our web site.

Instructions to Connect SQL Reporting Services to NetSuite

Here are the general steps.  We will provide illustrations below to help make our points.

Create Database Connection

1. We assume that you have SQL Reporting Services installed.  Note, we are using SQL Reporting Services 2008 for our platform integrated with Windows SharePoint Services to support our Remote Reporting Services offering.
2. Install NetSuite ODBC Drivers on the Reporting Server.  See this post for more information.
3. Start Business Intelligence Studio on the Report Server to create a new report.   Add a new data set to get data to your report.
4. The Dataset allows you to create a connection to the NetSuite ODBC Driver.  Name your connection.  Change the type to ODBC.  Click Edit to open the connection properties dialog.
5. The NetSuite driver is installed as “NetSuite.com” in the system data source (machine) configuration.
6. Supply logon information.  Test your connection to confirm you are connecting to the NetSuite system.
7. Now that you can connect, write your query that will support your report.  You can test the query to verify it is working.  We recommend referencing the NetSuite “Table of ODBC Views” to craft your queries.

Query NetSuite Views

1. Write your query and test to ensure you get result sets.
2. Next, add your parameters.  Parameters in this environment do NOT use the “@Parameter” notation as you would expect for a SQL Server connection.  Instead, use a “?” for each parameter placeholder.  They are referenced in the order they show up in the query.
3. As you add “?” parameters, Business Intelligence Studio adds parameters to the Parameters Dialog.   Open that dialog to add type, defaults and other options.
4. Test your query as you build it from the Dataset Properties Query Designer Dialog.
5. As you test, a dialog automatically prompts you for parameter inputs.
6. Once you are satisfied with your query, you can design the report.

Report Design and Deployment

1. At this point, developing the report is performed like you would any other SQL Report.
2. If you decide you want to have SharePoint host your SQL Report (note, this assumes this has been previously configured; not a simple task whatsoever), be sure to set the options within the Report, Properties, Deployment dialog.

We believe this is a great solution as our clients get the benefit of a subscription service without the need to procure and configure hardware and software.  Life really is better in the cloud!