NetSuite - Create a search that will show a count of customers created this month and also a count of customers inactivated the same month

To create a saved customer search which will show you the count of customers created this month and customers inactivated this month on the same search, follow the steps below:

1. Create a customer search, List > Search > Saved Searches > New > Customer.
2. On the Criteria Tab, mark Use Expressions = T

Criteria:
( | System Notes: Date is within this month | And
    System Notes: Type is Create | Or
    System Notes: Date is within this month | And
    System Notes: Field is Inactive | And
    System Notes: New Value is T | )

Results:
Formula (Numeric) | Sum | CASE WHEN (to_char({datecreated}, 'MM')) = to_char({today}, 'MM') THEN 1 ELSE 0 END
Formula (Numeric) | Sum | CASE WHEN {isinactive} = 'T' then 1 else 0 end

Note: You may change the summary label of the result fields to 1. Created within the Month and 2. Inactivated this Month. You may also replace {datecreated} on the formula depending on the field you're using.

Read More

NetSuite - How to create Saved Search to Identify and Count on One Report the Lead, Prospect and Customer Record per Sales Rep

1. Create a Customer Saved Search
2. Navigate to Lists > Searches > Saved Searches > New > Customer 
3. Set the following settings: 
    a. Criteria Tab > Add Filter: Inactive = is False 
    b. Results Tab > Sort by = Id > Add Field: 
         *  ID 
         *  Sales Rep -> Summary Type = Group 
         *  Name 
         *  Formula (Numeric) -> DECODE ({stage}, 'Lead',1,0)  and  Summary Type = Group 
         *  Formula (Numeric) -> DECODE ({stage}, 'Prospect',1,0)  and Summary Type = Group 
         *  Formula (Numeric) -> DECODE ({stage}, 'Customer',1,0) and Summary Type = Group 
4. Preview the result
5. Hit save

Read More

NetSuite - How to create a Daily Sales Report

1. Navigate to Reports>Sales>Sale by Customers>Customize.
2. Under Edit Columns, expand Sales > Add Amount (Gross).
3. Add in five columns for Amount (Gross) and set the last column as:

Alternate Date Range Type=Relative to Report date
Alternate Date Range = today

4. On the other Amount (Gross) Columns, use the following:
Alternate Date Range Type=Relative to Report date
Alternate Date Range =

--yesterday
--two days ago
--three days ago
--four days ago
--five days ago

Whatever is set on the Alternate Date Range depends on what are the data that needs to be seen on the report.
Example: Do they want to report on Sales for this week, Monday-Friday? Then the Date should be set would be the date on that week (example: 12/24/2012 to 12/28/2012). They can also set the Date=this business week.

In this way, the other columns will show an amount that depends on what was set as the Date and the Alternate Date Range.

5. Enter a Report Name and hit Save.
6. On the preview mode, select this business week as the Date filter. Click Refresh.

You should now have your own Daily Sales Report. This Report can be cusotmized according to the User's needs.

Read More

NetSuite - How to create Opportunity Saved Search Similar to the Total Open Opportunities Report with System Notes Columns Displayed

This article discusses creating a saved search that contains details provided in the Pipeline Analysis of Open Opportunities and adding the information from system notes to show changes made and its date.

The Pipeline Analysis of Open Opportunities gives information about opportunity deals that are approaching their expected close dates and which opportunities sales reps should close to winning. However, standard reports do not have the ability to show system notes thus changes made in the record cannot be exposed.

To  a Saved Search Open Opportunity Saved Search to show changes made and its date:
1. Log in to NetSuite
2. Navigate to Reports > Pipeline Analysis > Total Open Opportunities Report
3. Customize the report and we will see that it does not allow adding System Note Fields
4. To  the search, navigate to Reports > Saved Searches > All Saved Searches > New
5. Select Opportunity
6. Set the Criteria Tab: Filter
    * System Notes Fields: Date
    * Status = Opportunity-Open
7. Set the Results Tab: Add Fields
    * Number
    * Date
    * Company
    * Sales Rep
    * System Notes Fields: Date
    * System Notes Fields: Type
    * System Notes Fields: Old Value
    * System Notes Fields: New Value
8. Available Filters Tab: Show in Footer = True
     * System Notes Fields: Date
     * System Notes Fields: Type
9. Click Save & Run

Read More

NetSuite - How to Create a Case Saved Search to List Cases Closed Within Specific Number of Days After Created Date

To create a Case Saved Search to list cases with Date Closed within 2 days to 7 days after Created Date:

1. Navigate to Lists > Search > Saved Searches > New

2. Click Case

3. Enter a Search Title

4. Under Criteria > Standard subtab, add the following:

• Status is Closed
• Formula (Numeric)  
• Formula = CASE WHEN (ROUND({closed} - {createddate}) <= 7) THEN 1 ELSE 0 END
• Formula (Numeric) = equal to
• Value = 1
• Formula (Numeric)  
• Formula = CASE WHEN (ROUND({closed} - {createddate}) > 2) THEN 1 ELSE 0 END
• Formula (Numeric) = equal to
• Value = 1

5. Under Results > Columns subtab, add the fields to display in the search result, for example:

·         Number

·         Created Date

·         Date Closed

·         Status

6. Click Save & Run.

Read More

NetSuite - How to Create a search which would count the number of Estimates created per month per Customer

To create the search, perform the following steps:

1. Navigate to Lists > Search > Saved Searches > New.

2. Select Transaction.

3. Provide a Search Title.

4. In the Criteria tab > Standard subtab, add the filters as needed:

Type is Estimate

5. On the Results tab > Columns subtab, set the following:

Field	Summary Type	Formula	Summary Label
Name	Group
Formula (Numeric)	Sum	DECODE (to_char({datecreated}, 'MM'), '01' , 1)	January
Formula (Numeric)	Sum	DECODE (to_char({datecreated}, 'MM'), '02' , 1)	February
Formula (Numeric)	Sum	DECODE (to_char({datecreated}, 'MM'), '03' , 1)	March
Formula (Numeric)	Sum	DECODE (to_char({datecreated}, 'MM'), '04' , 1)	April
Formula (Numeric)	Sum	DECODE (to_char({datecreated}, 'MM'), '05' , 1)	May
Formula (Numeric)	Sum	DECODE (to_char({datecreated}, 'MM'), '06' , 1)	June

Note: Since there are 12 months in a year, add 12 Formula (Numeric) fields. Update the formula to match the month (e.g. 01 = January, 02 = February)

6. Click Save and Run.

Read More

NetSuite - How to create Closed Case Analysis Report : Incident Date vs. Created Date to compute the Time to Close column

Question: How is Time to Close column computed on the Closed Case Analysis Report in Reports >Customer Service > Closed Case Analysis?  

Answer: The difference between the Incident Date field and the Date Created field:

Incident Date: It is initially the same date as the Date Created. This date can be editable by users.
Date Created: This is the date when the Case record is generated or saved. This field is system generated and cannot be edited.
 The Time to Close is calculated based on the Date Created field. Changing the Incident Date field will not change the Time to Close.

If the case has been closed and never re-opened, then it is based on the Date Created field.
If the case has been re-opened and then closed, then the Time to Close is calclulated based from the Date Last Modified field.

Read More

NetSuite - How to Create an opportunity report to show the opportunity status and items details like item name & purchase price

Question/Problem: 
 
A.) Navigate to  Lists > Search > Saved Searches > New > Transaction, Status is available but this field will only show the transaction status and not the status value coming from the Status field on the opportunity
B.) Navigate to  Lists > Search > Saved Searches > New > Opportunity, Opportunity Status is available but no item fields to get the item details

---

Answer/Solution:
 
Navigate to Reports > Financial > Transaction Detail > Customize > Edit Columns--Remove unnecessary columns like Account, Clr, Split
---Click Transaction folder > Status folder: select Name
---Click Transaction folder > Item folder: select Purchase Price (or select any field you would like to show)
--Under Filters > Transaction folder > Transaction Type subfolder, click either Abbreviation or Long Name=equal to OPP(Opportunity)

Read More

NetSuite - How to Create a Saved Search To Display Which User Created the Sales Order

To create a Saved Search to display the User created the Sales Order, please follow the steps below: 

1. Navigate to Reports > Saved Searches > All Saved  Searches > New.

2. Select Transaction as the Search Type.

3. Enter a Search Title and set the following:

A. Criteria tab > Standard sub tab:

Type is Sales Order
Main Line is True 

B. Results tab > Columns sub tab:

Created By

4. Save & Run.

Read More

NetSuite - How to create a Sales Report by Month when column option is not available

In order to use the same column option for a New Report the option to use Matrix Report will show the similar information.
For this example the report will show Transaction by Item that are posted to a particle account. The same concept can be used for customized reports.

1. Navigate to Reports >  New Report
2. Select Transactions
3. Metric = Amount
4. Check Matrix for format of the report
5. Component = Item
6. Field = Name (Grouped)
7. Column = Month
8. Click More Customization
9. In Edit Columns:
Add any other columns you would like to see (Eg. Item Description)
10. In Filters:
Transactions folder > Account (Line) > Select your account
11. Rename the Report in the Name field
12. Save
13. When viewing the report you can change the date range the range to be considered.

Read More

NetSuite - How to create a saved search which replicate the Sales Order by Item Summary Report

How to create a  saved search which would replicate the Sales Order by Item Summary Report, users can perform the following steps:

1. Navigate to Lists > Search > Saved Searches > New > Select Transaction.

2. Enter a Search Title.

3. In the Criteria tab > Standard sub tab, add the following filters:

    Type = any of Sales Order, Return Authorization

    Status = none of Sales Order:Cancelled, Sales Order:Pending Approval, Return     Authorization:Cancelled,Return Authorization:Pending Approval

    Main Line = False

    Tax Line = False

    Shipping Line = False

4. In the Results tab > Columns sub tab, set the following fields:

     Field | Summary Type

    Item Fields... = Type | Group

    Item | Group

    Item Fields...= Description | Group

    Quantity | Sum

    Amount | Sum

5. Under the Results tab, also set the following:

    Sort By = Item Fields : Type

    Then By = Item

    Show Totals = True (checked)

6. In the Available Filters tab, set the following

    Filter | Show in Footer

    Date | True (checked)

7. Preview/Save the search.

Read More

NetSuite - How to create a Custom pipeline Trend graph (e.g. Total Pipeline by Sales rep report) to be filtered per Sales rep

In the customer’s dashboard, the  Custom pipeline Trend graph cannot be filtered by Sales rep because it is dependent on a Standard report: Total Pipeline by Sales Rep Report.

The trend graph came from a standard KPI, and the standard report (Total Pipeline by Sales Rep) cannot be customized for it to be filtered per sales rep.

Since Reports cannot be customizable and be used in KPI, translate it via Saved search.

Below are the Steps:

I. Know from which report the Total Pipeline (projected) is based/getting the values.
  1. Home > Dashboard > Indicator = Total Pipeline (Projected)
  Period = End of this Month vs End of same month last year
    2. Click the Current value to drill down to the report.
  Locate where the Current amount is getting the value
  At the Total column, you will see that it is under the Projected Total column, along the row Total
  3. At the top of the page, right side of the report title Total Pipeline by Sales Rep Summary report, click "View Detail"
  You will be directed to the Total Pipeline by Sales Rep Detail report

II.   a Saved search based from the definition of Total Pipeline by Sales Rep Detail report.
 1. Reports> Saved searches > All saved searches > New > Transactions
 2. Go to Criteria tab > Use expressions = True > Add the following:

 Type = is any of Estimate, Opportunity......AND
 Main Line = is true..................................AND
 ( Probability = is not 100..........................OR
  Probability = is not empty..........................OR
  Probability = is not 0)..........................AND
 (((Formula (Text) = is True.. Formula = CASE WHEN {type} = 'Estimate' THEN {includeinforecast} ELSE NULL END ....) AND
 ( Formula (Text) = is Open..... Formula = CASE WHEN {type} = 'Estimate' THEN {status} ELSE NULL END ......OR
 Formula (Text) = is Expired..... Formula = CASE WHEN {type} = 'Estimate' THEN {status} ELSE NULL END)) ......OR
 (Formula (Text) = is not issued Estimate..... Formula = CASE WHEN {type} = 'Opportunity' THEN {status} ELSE NULL END ......AND
 Formula (Text) = is In Progress..... Formula = CASE WHEN {type} = 'Opportunity' THEN {status} ELSE NULL END))

 3. Go to Results tab > Add the following:
   - Sales rep…. Summary Type= Group
   - Type
   - Number
                 - Formula (Currency)… Sum….Formula = (CASE find WHEN 'Estimate' THEN {amount} WHEN 'Opportunity' THEN {projectedamount} END)  

4. Go to Available Filters tab > Filter = Expected Close date
 5. Enter the search Title
 6. Click Save & Run

III. Use the Custom saved search for the custom KPI
 1. Home > Dashboard > find the Key Performance Indicators portlet.
 2. Click Setup (arrow down > setup).
 - You will be directed to "Set Up Key Performance Indicators" page.
 3. Go to Key Performance Indicators tab.
 4. Click Add Custom KPIs.
 5. Type the name in the Search box or manually find it in the dropdown list.
 6. Once you find it, click the -> arrow at the left side of it to add in the Current Selections box at the right side.
 7. Click Done
 8. On the Range, select the desired Period
 - check the Compare box
 - Select value in the Compare range dropdown list
 * The Range and Compare Range will become your Previous and Current periods.

 9. Click Save

IV. Add the Custom KPI in the Trend graph
 1. Go to Home > Dashboard
 2. Click Personalize Dashboard
 3. At the left side of the screen, under Add Content, click the + for Trend Graphs
 4. Click the "Custom KPI # 3" or any custom KPI #
 5. Go to the trend graph (if you cannot find it, try choosing one Trend graph)
 - click Setup

-  A pop up screen will display
 
 6. Populate the fields in the pop up screen
 - Custom Trend Graph = * select the name of the Custom saved search
 - Trend graph: Trend type = Monthly
 - Trend graph: Show average = True/ checked
 - Trend Graph: Average window = 2
 - Trend Graph: Show Las Data Point = True/ checked
- Trend Graph: Include Zero on Y-axis = False/ unchecked
- Save

** Notes/ Limitations:
1. Per Sales rep, you can filter the Total Pipeline (projected) via custom saved search but there is a discrepancy in the available "Periods" in the KPI.
For example: For the KPI "Total Pipeline (Projected)", the Periods being compared are "End of This Month vs End of Same Month Last Year".
If you will use the Custom KPI from the saved search, you cannot find "End of this Month" and "End of same month last year" in list of available Periods.
2. If filtered based on Sales rep, you will get the same data from Total Pipeline (Projected) with that in the custom saved search/ KPI.
However, as mentioned in note #1, there are Periods which are not available. It depends on the nature of the report/ saved search or the underlying data inside those reports.
In this regard, it is suggested that you choose an alternative Period that will display a similar data, which is available in the drop down list for both the Total Pipeline (projected) and Custom KPI.

Read More

NetSuite - Computation of Formula Fields of Time between Date Created and Incident Date/Time Excluding Non-business Days/Hours in a Case Saved Search

You can create a case saved search to compute the Date created less incident date/time and excluding non-business days/ hours, using formula fields.

Summarized the steps as below:

* Notes:
          Sample business hours: 9:00am to 5:00pm
          Non-working days: Saturdays and Sundays


Steps:

I. Create 4 Custom CRM fields.

The first two fields will get the Date created and Incident date
The last 2 will get the Time (in hours, decimal) the case was Created and Incident time.
 

1. Setup > Customization > CRM Fields > New
*This field will get the "Date" the Case record was CREATED
- Label = 111 Date Created
- Type  = Free form text
- Store Value = False
- Applies to > Case = True
- Display > subtab = Main
- Validation & Defaulting:
 Formula = True
 Default Value = CASE WHEN TO_CHAR({createddate},'D') BETWEEN 2 AND 6 THEN CASE WHEN to_char({createddate},'HH24') < 9 then to_char(trunc({createddate}) + 9/24) when
to_char({createddate},'HH24') >= 17 then to_char(trunc({createddate}) + 17/24)  else to_char({createddate}) end ELSE to_char(trunc({createddate}) + 17/24) END
**sample Field ID: custevent42
 
2. Setup > Customization > CRM Fields > New
*This field will get the "Date" as per INCIDENT DATE.

- Label = 222  Incident Date
- Type  = Free form text
- Store Value = False
- Applies to > Case = True
- Display > subtab = Main
- Validation & Defaulting:
 Formula = True
 Default Value = CASE WHEN TO_CHAR({startdate},'D') BETWEEN 2 AND 6 THEN CASE WHEN to_char({startdate},'HH24') < 9 then to_char(trunc({startdate}) + 9/24) when to_char({startdate},'HH24') >= 17
then to_char(trunc({startdate}) + 17/24)  else to_char({startdate}) end ELSE to_char(trunc({startdate}) + 17/24) END
**sample Field ID: custevent44
 
3. Setup > Customization > CRM Fields > New
*This field will get the "Time", expressed in Hours, the Case record was CREATED
- Label = 333 Time Created
- Type  = Free form text
- Store Value = False
- Applies to > Case = True
- Display > subtab = Main
- Validation & Defaulting:
 Formula = True
 Default Value = (to_number(CASE WHEN TO_CHAR({createddate},'D') BETWEEN 2 AND 6 THEN CASE WHEN to_char({createddate},'HH24') < 9 then to_char(trunc({createddate}) + 9/24,'HH') when
to_char({createddate},'HH24') >= 17 then to_char(trunc({createddate}) + 17/24,'HH24')  else to_char({createddate},'HH24') end ELSE to_char(trunc({createddate}) + 17/24,'HH24') END) + (to_number(CASE WHEN
TO_CHAR({createddate},'D') BETWEEN 2 AND 6 THEN CASE WHEN to_char({createddate},'HH24') < 9 then to_char(trunc({createddate}) + 9/24,'MI') when to_char({createddate},'HH24') >= 17 then
to_char(trunc({createddate}) + 17/24,'MI')  else to_char({createddate},'MI') end ELSE to_char(trunc({createddate}) + 17/24,'MI') END) / 60))
**Field ID: custevent45
 
4. Setup > Customization > CRM Fields > New
*This field will get the "Time", expressed in Hours, of the "Incident date"
- Label = 444 Incident Time
- Type  = Free form text
- Store Value = False
- Applies to > Case = True
- Display > subtab = Main
- Validation & Defaulting:
 Formula = True
 Default Value = to_number(CASE WHEN TO_CHAR({startdate},'D') BETWEEN 2 AND 6 THEN CASE WHEN to_char({startdate},'HH24') < 9 then to_char(trunc({startdate}) + 9/24,'HH24') when
to_char({startdate},'HH24') >= 17 then to_char(trunc({startdate}) + 17/24,'HH24')  else to_char({startdate},'HH24') end ELSE to_char(trunc({startdate}) + 17/24,'HH24') END) + (to_number(CASE WHEN
TO_CHAR({startdate},'D') BETWEEN 2 AND 6 THEN CASE WHEN to_char({startdate},'HH24') < 9 then to_char(trunc({startdate}) + 9/24,'MI') when to_char({startdate},'HH24') >= 17 then to_char(trunc({startdate}) +
17/24,'MI')  else to_char({startdate},'MI') end ELSE to_char(trunc({startdate}) + 17/24,'MI') END) / 60)
**Field ID: custevent46
 
II. Create a Saved search.
1. Reports > Saved Searches > All saved searches > New > Case

2.  Search Title = enter the Search Title
- Criteria tab > specify any criteria
- Results tab:
 - Number
 - Date Created
 - Incident Date
 - 111 Date Created
 - 222  Incident Date
 - 333 Time Created
 - 444 Incident Time
 - Formula (Text) > Formula = case when to_date({custevent44}) - to_date({custevent42}) = 0 then to_number({custevent46}) - to_number({custevent45})  when to_date({custevent44}) - to_date({custevent42}) = 1 then to_number({custevent46})
+ to_number({custevent45}) when to_date({custevent44}) - to_date({custevent42}) > 1 then (18 - to_number({custevent46})) + (18 - to_number({custevent45})) + (((to_date({custevent44}) - to_date({custevent42}))
- 1) * 8) end

- Save and Run

Read More

NetSuite - How to Create a Saved Search to List or Highlight Leads, Prospects or Customers created on a Weekend

To create a Saved Search which lists or highlights leads, prospects or customers created on a weekend:

1. Navigate to Lists > Search > Saved Searches > New.

2. Click Customer.

3. Enter a Search Title.

4. Under Criteria > Standard subtab, add the following:

• Stage is any of Lead/Customer/Prospect
• Formula (Text)
•     Formula = CASE WHEN TO_CHAR({datecreated},'D') IN (1,7) THEN 'T' END
•     Formula = is
•     Value = T

5. Under Results > Columns subtab, add the fields to display in the search result, for example:

• ID
• Name
• Date Created

6. If you have an existing search and you wish to highlight the records created during the weekend, under the Highlighting > Highlight if... subtab, enter the following condition and set the desired Image/Text Color/Background Color/Bold/Description:

· CASE WHEN TO_CHAR({datecreated},'D') IN (1,7) THEN 'T' END

7. Click Save & Run. 

Read More

NetSuite - How to Display Total Opportunities Without Partner Association for Each Month

User needs to  a list of opportunities without a value for Partner. The user prefers to see the result on a per-month basis. 

To  the report:

1. Navigate to Lists > Search > Saved Search > New.
2. Select Opportunity.
3. Under Criteria tab, insert:

Filter = Formula Text

Description = is empty

Formula = {customer.partner}

3. Under Results tab > Columns subtab, set the following fields:

Field = Customer

Summary Type = Group

Summary Label = Customer

Field = Formula (Numeric)

Summary Type = Sum

Formula = Case WHEN {customer.partner} is NULL Then 0 ELSE 1 END

Summary Label = Total Estimates Without Partner

Field = Formula (Numeric)

Summary Type = Sum

Formula = DECODE (to_char({dated}, 'MM'), 01, 1,0)

Summary Label = January

Field = Formula (Numeric)

Summary Type = Sum

Formula = DECODE (to_char({dated}, 'MM'), 02, 1,0)

Summary Label = February

Field = Formula (Numeric)

Summary Type = Sum

Formula = DECODE (to_char({dated}, 'MM'), 03, 1,0)

Summary Label = March

5. Click Save & Run.

Note: The result of the search will show the number of opportunities per customer that are not associated with any partner record. The result will display the count in a monthly basis. Additional months can be displayed by following the sample formula given above.

Read More

NetSuite - How to create Saved search to show total quantity sold against items per transactions in one line

Problem:
Customer needs to  a report to compare quantity sold per item per transaction. This should only show all in one line on the report.

Solution:

 a search which pulls a specific set of transactions (e.g. sales orders, invoices, cash sales) and present the total sum quantity of item A and the total sum quantity of item B sold against each sales order as a single line item record with a field for each item.

eg: SO Id, Sales Person, Total Red Widgets, Total Blue Widgets

 a saved transaction search and set the following on the results tab:

1. Navigate to Reports>Saved Searches>All Saved Searches>New
2. Click Transactions
3. Under the Criteria tab, set type to certain transaction type (e.g. Sales Order, Invoice, Cash Sale)
4. On the Results tab, set the following:

a.
Field : Number
Summary Type: Group

b.
Formula (Numeric) : case when {item.id} = XXX then {quantity} end
Summary Type: Sum

Note: XXX is the internal ID of the various items.  as many Formula (Numeric) fields for each item you are selling.

Read More

NetSuite - How to create a Search for customers with a certain number of transactions for a particular date or period

A report or search for customers with a certain number of transactions (sales orders, invoices, etc) for a particular date or period

1. Navigate to Reports > Saved Searches > All Saved Searches > New
2. Select Customer
3. Under the Criteria tab > Standard subtab, set the following filters:
    Transaction: Type = Sales Order (or any type of transaction user are searching for)
    Transaction: Date = (set user preferred date or date range
4. Under the Criteria tab > Summary sub-tab, set the following:
    Summary Type = Count
    Field = Transaction: Number
    Description = is greater than 1 (or any number user want to filter it to)
5. Under the Results tab > Columns subtab, set the following:
    Name (Set the Summary Type to Group)
    Transaction: Number (Set the Summary Type to Count)
6. Enter a name for the saved search and select Save & Run.

Read More

NetSuite - How to create a Lead Count by Lead Source Report

To count the actual number of lead source,  a new report and use Customer Count as the Metric. To  the custom report, perform the following steps:

     1. Navigate to Reports > New Reports.

     2. Select Customer Count.

     3. In the New Customer Count Report page, set the following:

          a. Enter a Report Title
          b. Metric = Total
          c. Format = Summary
          d. Component = Lead Source
          e. Field = Lead Source Name (Grouped)

     4. Click Save.

Read More