Netsuite to Planning Cloud - Part 2: Setting up your Saved Search in Netsuite

Welcome to Part 2 of this 5 part series on setting up the integration between Netsuite and Planning Cloud. For some background information on the project, please see Part 1.

      Part 1 - User based vs Token Based Authentication       

      **Part 2 - Tips on setting up your Saved Search in Netsuite**

      Part 3 - Tips on setting up Data Management in Planning Cloud 

      Part 4 - How to set up Drill through from Planning Cloud to Netsuite

      Part 5 - How to give your end users access to run the integrations

I do not like to repeat anything that has been well documented before, so in this blog I will not be going through the steps needed to install and configure the PBCS bundle in Netsuite. My colleague Mike has done this in an entertaining and thorough way in the following blogs:

https://stay-ahead-in-the-cloud.blogspot.com/2019/03/connecting-two-clouds-netsuite-to.html

https://stay-ahead-in-the-cloud.blogspot.com/2019/07/setting-up-custom-role-for-netsuite-integration.html

      Please note: This blog series assumes you already have a basic understanding of how Netsuite works, you have installed the PBCS bundle in Netsuite and that you have set up your PBCS Integration user and role. 

      I am by no means a Netsuite expert, the lessons learnt I am going to share with you are a direct result of my (often trial and error) experience and there may be better ways to achieve the same thing (although I have not found them!)

      Tips on setting up your Saved Search in Netsuite

The PBCS bundle comes with sample saved searches, these are a good starting point but will probably require some editing to get the exact set of data/metadata required for the data load to Planning Cloud. Since these are locked, we need to create our own.

To create your own saved search:

1. Start with an existing PBCS saved search from the bundle that is closest to what you need and do a “Save as”. We used the PBCS - Trial Balance saved search.

2. Ensure the ID of the new saved search starts with customsearch_nspbcs_. This is required for Data Management to pick up the relevant saved searches when you initialise the connection.

3. Add extra rows to the saved search so that all corresponding PBCS dimensions are present, ideally the Internal or External ID should match the member names in Planning Cloud to reduce mappings required

For example: Saved Search Edit > Results

4. Ensure the Internal ID of the Period is included in the saved search – this will become a filter in PBCS

5. If using multiple currencies, ensure the Amount column in the saved search is pulling the data in the local currency of the subsidiary (assuming you are loading in Local currency in PBCS). To do this, set Consolidated Exchange Rate to None on the Saved Search. This was not the default setting.

          Otherwise you may be pulling incorrect values to PBCS.

6. Check the Criteria of the saved search – these will come through as filters in PBCS. 

(Do not confuse this with the “Available Filters” in Netsuite which do not come through as filters in PBCS)

By default in the PBCS Trial Balance saved search, the following criteria will be applied

         Actual criteria used:

Period – allows you to run the saved search for a chosen period

Posting – always set to True

Formula (Text) – used to filter the saved search to only show specific subset of subsidiaries 

Note that I had to remove the Mainline filter to return the entire data set, setting it to either Yes or No both returned only a subset of the required data!

(More on filters in Part 3)

7. Check that your PBCS Integration role has access to all the segments/custom lists used in the saved search

     In my case, there was a dimension in PBCS (Projects) which was set up as a Custom List in Netsuite. I had to give our custom PBCS Integration role specific access to this list in Netsuite before it was able to successfully run the saved search from PBCS.

 

Optional tips for your saved search

• Create separate fields for Period and Year – this will make your period mappings simpler (see screenshot above for how to do this)
• Include columns for the internal ID of account, period, and entity – this will be used in the drill through set up later (More on drill-through in Part 4)

I hope these tips will save you some time when creating your saved search. Any questions, feel free to drop me a comment.

That concludes Part 2, stay tuned for Part 3 - Tips on setting up Data Management in Planning Cloud

Thanks for reading :) 

Ciao for now.