1. NetSuite Enterprise Performance Management
  2. Profitability and Cost Management Sync SuiteApp
  3. Profitability and Cost Management Sync Installation and Setup
  4. Managing SuiteQL Queries in Profitability and Cost Management Sync

Managing SuiteQL Queries in Profitability and Cost Management Sync

You can write SuiteQL queries to load transaction data and metadata from NetSuite into your Profitability and Cost Management application.

Note:

The SuiteQL query results don't include metadata hierarchy.

To define a SuiteQL query:

  1. In NetSuite, go to NetSuite EPM > Profitability and Cost Management > Manage SuiteQL Queries.

  2. On the Manage SuiteQL Queries page, do one of the following:

    • To create a new SuiteQL query, click Add SuiteQL Query.

    • To update an existing SuiteQL query, click Edit next to the query.

  3. In the ID field, enter a unique ID for the SuiteQL query you are creating.

    Important:

    The integration recognizes IDs the prefixes of which begin with custsql_. If you omit this prefix, custsql_ is automatically prepended.

  4. In the Name field, enter a unique name for the SuiteQL query you are creating.

  5. In the Result Column Labels field, enter the names of your SuiteQL query result columns, separated by commas.

    Important:

    Make sure you don't enter line breaks when entering the column names.

    Tip:

    To populate the field with the column names you defined in the query, click Populate Result Column Labels. For details, see Step 7 below.

  6. In the Query field, provide the query details.

    Review the following guidelines for adding filters to SuiteQL queries:

    • Add only the filters you want to make available in Data Exchange.

    • To filter values using the '@' symbol, keep the following in mind:

      • You can use the '@' symbol to specify parameter placeholders for filters. For example, see the following query where a date is used as a filter:

        select top 10 memo from transaction where createddate > to_date(@datecreatedfilter@, 'DD-MM-YYYY');

        When you use this query, ensure that the value you provide for datecreatedfilter matches the date format specified in the to_date function. Here are some examples:

        • If your date value is '31-07-2020', the query should be:

          select top 10 memo from transaction where createddate > to_date('31-07-2020', 'DD-MM-YYYY');

        • If your date and time value is '2015/05/15 8:30:25', the query should be:

          select top 10 memo from transaction where createddate > to_date('2015/05/15 8:30:25', 'YYYY/MM/DD HH:MI:SS');

      • Using the '@' symbol to separate values in IN clauses is not supported.

      Note:

      The filter placeholders become available in Data Exchange. These placeholders don't have default values. You should set their values during Data Integration execution.

  7. To populate the Result Column Labels field with the column names you defined in the query, click Populate Result Column Labels.

    If you use this option, follow these guidelines:

    • Replace filter placeholders with specific values before using this action.

    • If this action overwrites existing column labels, confirm the action when prompted.

  8. Click Save.

  9. To ensure the integration recognizes the created SuiteQL query, place the custsql_ prefix on the Profitability and Cost Management allowlist. For instructions, see Data Source Allowlist.

For general information about SuiteQL, see SuiteQL.

Related Topics

General Notices