Search

NetSuite Custom Reports

NetSuite Custom Reports

NetSuite users who connect with SuiteTalk can use RESTlets or saved searches to create custom reports in CData Connect.

Note: RESTlets and saved searches are not compatible with SuiteQL.

RESTlets

This section shows how to use a RESTlet script to query saved searches.

Note: Using RESTlets is the recommended way to query saved searches. Using RESTlets is different from the method described in Saved Searches. This older method relies on the SuiteTalk API, which is more limited in what saved searches can return.

Create a RESTlet Script

Open a new file in a text editor. Then copy and paste the following RESTlet script and save it as search_script.js:

/**
 *@NApiVersion 2.x
 *@NScriptType Restlet
 */
define(['N/search'],
    function (search) {
        // Get a standard NetSuite record
        function _get(context) {
            var myTransactionSearch = search.load({
                id: context.searchid
            });
            var pagedData = myTransactionSearch.runPaged({
                pageSize: 1000
            });
            if (pagedData.count == 0) {
                return JSON.stringify({
                    "pagedData": {
                        "searchDefinition": {
                            "count": [
                                {"columnCount":0}
                            ]
                        }
                    }
                });
            }
            var results = pagedData.fetch({
                index: context.index
            })
            return JSON.stringify(results);
        }

        return {
            get: _get
        };
    });

Using RESTlets to Query Saved Searches

Follow the steps below to deploy the RESTlet script and execute the RESTlet.

Enable SuiteScript

Go to Setup > Company > Enable Features. Under the SuiteCloud tab, ensure the options for Client SuiteScript, Server SuiteScript, and SuiteScript Server Pages are all enabled.

Upload the SuiteScript File

Go to Documents > Files > SuiteScripts. Click the Add File button. Upload the search_script.js script that you created in Create a RESTlet Script.

Create a Script Record

Go to Customization > Scripting > Scripts > New. From the menu, find search_script.js and select it. Then click Create Script Record.

Deploy the Script Record

Give the script a name and under the Deployments tab add a title. Then click Save. This creates a deployment.

Note: While the status of the Script Deployment is Testing, only the user that created the deployment returns results from any saved searches queried. To make the RESTlet available for other users, update the Status of the Script Deployment to Released and add supported roles in the Audience tab.

Get the Deployment / Script Id

You can find a link to the deployment in the Script Record. Follow the link to the deployment. Or, you can also go to Customization > Scripting > Script Deployments and use the filters to find the deployment you made. In the deployment record, you can see a URL and External URL. Copy the script number and deployment number from this URL.

Get a Saved Search Id

To execute search_script.js, you need to pass it the Id of a saved search: Go to Lists > Search > Saved Searches and copy in the Id of a saved search. We recommend you use a search that is ordered by a column that always gives the same ordering, such as the Date Created. This is because NetSuite does not cache results that can cause duplicates or missing data if data is changed while you are paging through the results.

Add a Custom Report

  1. In the Connections page of CData Connect, select your NetSuite connection.

    If you do not yet have a NetSuite connection, follow the steps in the NetSuite Setup Guide.

  2. Click the Custom Reports tab and click Add to add a new report.

    An Add Custom Report dialog appears.

  3. Select RestletSchema and click Confirm.

  4. Enter the following information:

    • Name—the name of the custom report.

    • SearchId—the Id of the saved search obtained in the NetSuite UI.

    • ScriptID—the Script Id of the RESTlet to create a schema for.

    • DeploymentNum—the deployment number of the RESTlet schema to execute. Typically, this number is 1.

  5. Select whether to UseLabels. This determines if the NetSuite connector should try to match column names to what would appear in the Saved Search results or just use the API name returned by the saved search itself. Note that even when UseLabels is set to true, names are converted to alphanumeric with underscores. Also, be aware that sometimes labels come back with identical names for saved searches. In these cases, a number is appended to the duplicate names to keep the names unique.

  6. Select whether to FlattenSelects. Set this parameter to false if your saved search includes multiselect columns. This parameter controls how the NetSuite connector models the data types of selects (such as an entity / customer reference). When FlattenSelects is set to false, multiselect data is returned as a JSON aggregate.

    By default, the NetSuite connector exposes two columns for selects – one for the name and one for the value (often an Id). This does not work correctly if your saved search includes multiselect columns. There is nothing in the RESTlet metadata that distinguishes multiselect from single-select columns.

  7. Click Save to save the custom report.

Limitations

  • Currently, the NetSuite connector only supports RESTlets that conform to a response in the format of the included search_script.js script.

  • The NetSuite connector does not support server side filtering on the results. To enable queries to execute faster, define all filters within the saved search itself.

Saved Searches

Saved searches are supported with some limitations due to NetSuite restrictions.

Note: We recommend taking a look at RESTlets instead of following this guide. While these steps still work, saved searches used from SuiteTalk API as detailed below are limited both in how metadata can be obtained and content available. RESTlets have fewer limitations and return accurate metadata from NetSuite.

SavedSearches View

The SavedSearches view is a good way to retrieve a list of your available saved searches for a given search type. A SearchType must be specified to retrieve information from the SavedSearches view. For instance:

SELECT * FROM SavedSearches WHERE SearchType='Transaction'

Add a Custom Report

  1. In the Connections page of CData Connect, select your NetSuite connection.

  2. Click the Custom Reports tab and click Add to add a new report.

    An Add Custom Report dialog appears.

  3. Select SavedSearchSchema and click Confirm.

  4. Enter the following information:

    • Name—the name of the custom report.

    • SavedSearch—the name of the saved search. This becomes the name of the resulting table.

    • SavedSearchId—the Id of the saved search.

    • SearchType—the type of search for the custom report. Click for a list of search types.

    • ResultsToSample—(optional) an integer designating the number of results to sample.

  5. Click Save to save the custom report.

Limitations

NetSuite imposes a few limitations on saved searches. Calculated columns (formulas) cannot be retrieved via the NetSuite API. Only columns directly from the table can be retrieved. If you have a calculation in your saved search, you would need to return each individual column used in the calculation and perform the calculation client side. Ie:

SELECT (col1 / col2) AS calc FROM MySavedSearch

Saved searches that return an aggregation or summary (Group / Count / Sum / Minimum / Maximum / Average) cannot be returned at all. The NetSuite API throws an exception upon attempting to retrieve these saved searches. Due to this limitation, these saved searches are unavailable for our and any other third party tool.