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!

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube

| Tags: , , | Category: NetSuite, Reporting | 9 Comments

9 thoughts on “SQL Reporting on NetSuite Data Step-By-Step

  1. Does anyone know if there is another language or set of commands beside SQL for talking with databases?

    I’m working on a project and am doing some research thanks

  2. Marty Zigman says:

    From my memory, SQL has been the standard for querying relational databases since late 1970’s or early 1980’s. It is set oriented versus row (or record) oriented. Pretty much, this is the standard for workign with databases. Querying NetSuite via ODBC is through exposed views with standard SQL. It is all read-only. If you want to update the database, you use the web services tier which enforces all business rules.

  3. PANKAJ SRIVASTAVA says:

    The best way is to connect Netsuite as Linked server and then use in SSRS/SSIS and SSAS for any Data analysis, I am using it and it is very efficient way to access NetSuite data

  4. Marty Zigman says:

    Hi Pankaj,

    We did a major project to build a NetSuite SQL Server Data Warehouse using this technique. Be careful. Have a look at this article.

    https://blog.prolecto.com/2012/11/22/warning-sql-server-linked-server-to-netsuite-issues/

    Marty

  5. PANKAJ SRIVASTAVA says:

    Thanks Marty for heads up

Leave a Reply

Your email address will not be published. Required fields are marked *