Using Merchandise Hierarchy to Analyze Sales and Item Performance

A major benefit of the Merchandise Hierarchy feature is the ability to organize sales and item performance data in SuiteAnalytics workbooks and custom reports. Sales workbooks and reports are enhanced by the inclusion of merchandise hierarchy elements such as levels and nodes, as they help group and organize data in the reports by product classifications. With Merchandise Hierarchy you can now visualize sales by company branches, groups, departments, classes, and more.

You can create workbooks and custom reports that include merchandise hierarchy internal custom fields combined with any other NetSuite field.

In NetSuite, you can use either of the following tools to analyze sales and item performance:

  • SuiteAnalytics Workbook – a new analytical tool available in NetSuite for creating highly customizable workbooks that combine queries, pivot tables, and charts. For more information, see Creating Merchandise Hierarchy Workbooks.

  • Report Builder – you can use the Report Builder to customize reports' data and formatting. For more information, see Creating Merchandise Hierarchy Reports.

  • Saved Searches

Creating Merchandise Hierarchy Workbooks

With SuiteAnalytics Workbook, you can create custom workbooks that include merchandise hierarchy elements using a new data model that supports multilevel joins. This means you can join fields from multiple records in one single workbook. SuiteAnalytics Workbook also enhances the visualization of data in workbooks, providing better grouping of hierarchical data in pivot tables, and charts.

For more information, see the help topic SuiteAnalytics Workbook.

When you have only one hierarchy version defined in your system, you will create merchandise hierarchy workbooks that display the current hierarchy structure and item classification. These workbooks use custom merchandise hierarchy fields that have been added to the item record. They only show the current classification of items in the hierarchy and cannot show different classifications from different hierarchy versions.

Important

In order to create workbooks that use the current merchandise hierarchy structure, you must select the internal custom fields that have been added to the item record. For reports that use historical hierarchy data when your system includes multiple merchandise hierarchy versions, see Using Hierarchy Versions in Workbooks and Reports.


To create a SuiteAnalytic Workbook you must complete the following steps in order:

  1. Enable SuiteAnalytics – see the help topic Enabling SuiteAnalytics Workbook in Your NetSuite Account.

  2. Select a record type – see Select a Record Type for Your Merchandise Hierarchy Workbook.

  3. Select your source data – see Select the Source Data for Your Merchandise Hierarchy Workbook.

  4. Filter your source data – see Select the Filter Criteria for Your Merchandise Hierarchy Workbook.

  5. Set up the design of your report – see Set up Pivots and Charts for Your Merchandise Hierarchy Workbook.

The following example shows how to create a workbook for cash sale type transactions. To demonstrate how merchandise hierarchy date can be utilized, the workbook will show transactions for items assigned to a specific node: the Women’s Wear department. You can change the criteria to produce different types of merchandise hierarchy workbooks as needed.

Select a Record Type for Your Merchandise Hierarchy Workbook

The following steps show you how to select a root record for your merchandise hierarchy workbook. The available records are based on your assigned role and permissions. If you do not see the desired record, contact your administrator.

To select a root record type for your merchandise hierarchy workbook:

  1. Click the Analytics tab in the NetSuite navigation menu.

  2. On the Analytics dashboard, click New Workbook.

  3. Select Transaction as the record type from the list.

Continue to Select the Source Data for Your Merchandise Hierarchy Workbook.

Select the Source Data for Your Merchandise Hierarchy Workbook

The following steps show you how to select the source data for your merchandise hierarchy workbook by creating a query. Only fields that are added to the Data Grid can be used to generate a pivot table or chart.

In this example we will:

  • Remove any unwanted columns that have been added by default

  • Add merchandise hierarchy levels to the workbook that show the current classification of items in the hierarchy

  • Add the metric data that we want to appear in the workbook

To select the source data for your merchandise hierarchy workbook:

  1. Click the Data tab.

  2. Remove unwanted columns:

    1. Click the Field Menu icon in any column.

    2. Select Remove All Columns from the dropdown list to remove all the columns automatically added to the workbook.

  3. Add Merchandise Hierarchy levels:

    Note

    In this example, the workbook will show the current classification of items in the hierarchy.

    To create a workbook that shows the current classification of items, select merchandise hierarchy levels from the Item record.

    If you have defined multiple hierarchy versions, you can create a workbook that shows historical version classifications. Select merchandise hierarchy levels from the Transaction Line record (do not link the Item record). For more information, see Using Hierarchy Versions in Workbooks and Reports.


    1. Click the Join Record icon next to Transaction in the Records field list.

    2. Scroll down and click Transaction Line in the Join record list.

    3. Click the Join Record icon again, this time next to Transaction Line in the Records field list.

    4. Click Item in the Join record list.

    5. In the Item list navigate to the merchandise hierarchy levels that you want to add to the workbook. If you have prefixed the name of the hierarchy levels with [MH] it is easier to locate them in the list. In this example we add:

      • [MH] Department

      • [MH] Silhouette

      • [MH] Class

    6. Double-click each hierarchy level to add it to a column in the workbook.

  4. Add columns for the metrics we want to show in the workbook:

    1. In the Item list navigate to the metrics that you want to add to the workbook. In this example we add:

      • Amount (Net)

      • Quantity

    2. Double-click each metric to add it to a column in the workbook.

Continue to Select the Filter Criteria for Your Merchandise Hierarchy Workbook.

Select the Filter Criteria for Your Merchandise Hierarchy Workbook

The following steps show you how to filter the source data for your merchandise hierarchy workbook. Filters applied to the Data Grid from the Criteria tab determine the values that are available for the workbook.

In this example we will:

  • Create a Type filter: Cash Sale

  • Create a hierarchy node filter: Department : Women’s Wear

To filter your source data for your merchandise hierarchy workbook:

  1. Click the Criteria tab.

  2. Create a Cash Sale filter:

    1. In the Records list double-click Type. The Filter window appears.

    2. In the Filter window, select the filter conditions you want to apply to the field.

      Double-click Cash Sale to add the filter and click OK.

  3. Create a hierarchy node filter for Department : Women’s Wear.

    1. Double-click [MH] Department in the Transaction Line list. The Filter window appears.

    2. In the Filter window, select the field values (node) on which to filter. Double-click Women’s Wear to add the filter and click OK.

Continue to Set up Pivots and Charts for Your Merchandise Hierarchy Workbook

Set up Pivots and Charts for Your Merchandise Hierarchy Workbook

The following steps show you how to create a pivot table and a chart for your merchandise hierarchy workbook.

To create a pivot table for your merchandise hierarchy workbook:

  1. Click the Add Pivot link.

  2. On the Pivot tab, drag the desired fields from the Fields list to the Rows, Columns, or Values tabs in the Layout panel. Alternatively, drag the fields from the Fields list directly to the Pivot Table Viewer.

    In this example we drag the following to the Rows tab:

    • [MH] Department

    • [MH] Silhouette

    • [MH] Class

    Tip

    Click the Field Menu icon and select Rename to edit the name of a column. For example you can remove any prefix you may have added to the level name.


    In this example we drag the following to the Measures tab:

    • Amount (Net)

    • Quantity (Sum)

  3. Click the Refresh icon to generate the pivot table.

    Important

    If any changes are made on the Data tab, Criteria tab, or Pivot tab, you must click the Refresh icon in the Pivot tab to update the pivot table.


For more details regarding working with pivots, including formatting the layout, changing the summary type, or adding filters, see Pivot Your Data Source.

To create a chart for your merchandise hierarchy workbook:

  1. Click the Add Chart link.

  2. On the Chart tab, drag the desired fields from the Fields list to the X-Axis, Series, or Measures tabs in the Layout panel.

    In this example we drag the following to the X-Axis tab:

    • [MH] Class

    In this example we drag the following to the Measures tab:

    • Quantity (Sum)

  3. Click the Refresh icon to generate the pivot table.

For more details regarding working with charts, see Chart Your Data Source.

Remember to save your workbook. You can save your workbook at any time, click the Menu icon , and select Save.

Creating Merchandise Hierarchy Reports

You can create custom reports that include merchandise hierarchy elements using Report Builder.

For more information regarding customizing reports with the Report Builder, see the help topic Report Customization.

Internal Custom Fields for Merchandise Hierarchy Reports

To create customized sales reports that utilize your merchandise hierarchy structure, it is necessary to manually add the internal custom fields that have been created for the merchandise hierarchy in the Report Builder. Internal custom fields are created automatically for all the levels in your merchandise hierarchy when the Custom Segments feature is enabled. These internal custom fields cannot be accessed nor edited. For more information, see Enabling Merchandise Hierarchy Features.

You can use the custom merchandise hierarchy fields created by Custom Segments as columns or filters.

The custom merchandise hierarchy fields are located in the Add Fields list in the Report Builder. Each custom field appears under the label representing the record type where it was applied. For transactions, the custom merchandise hierarchy fields appear twice in the Add Fields list. One instance represents the custom field as a transaction column. The other represents the custom field that has been added to the item record.

When you have only one hierarchy version defined in your system, you will create merchandise hierarchy reports that display the current hierarchy structure and item classification. These reports are also known as item-based reports because they use custom merchandise hierarchy fields that have been added to the item record. They only show the current classification of items in the hierarchy and cannot show different classifications from different hierarchy versions.

Important

In order to create reports that use the current merchandise hierarchy structure, you must select the internal custom fields that have been added to the item record. The location of these custom merchandise hierarchy fields varies depending on the type of report being customized. For more information, see Locating Custom Merchandise Hierarchy Fields for Reports.


Locating Custom Merchandise Hierarchy Fields for Reports

Custom merchandise hierarchy fields are located in the Add Fields list in the Report Builder.

The type of report you want to customize determines the location of the internal custom fields you need to add.

Note

Do not use the internal custom fields described in this section for reports that use historical hierarchy data when your system includes multiple merchandise hierarchy versions. For more information, see Using Hierarchy Versions in Workbooks and Reports.


To locate the internal custom fields needed to create reports using the latest merchandise hierarchy structure, refer to the following table. Note that we have provided details of only the most common types of report that utilize merchandise hierarchy data.

Report Type

Report

Custom Merchandise Hierarchy Fields Location

Notes

Purchases

Purchase by Vendor

Purchases > Item

Select fields with the suffix (Custom Column)

Purchase by Item

Item Type

Select fields with the suffix (Custom)

Purchase Order Register

Purchase Order Transactions > Item

Open Purchase Orders

Open Purchase Orders > Item

Purchase Order History

Purchase Order Transactions > Item

Select fields with the suffix (Custom Column)

Inventory/Items

Inventory Profitability

Item

Select fields with the suffix (Custom)

Current Inventory Snapshot

Inventory Item

Select fields with the suffix (Custom)

Physical Inventory Worksheet

Inventory Item

Select fields with the suffix (Custom)

Inventory Valuation

Inventory Item

Select fields with the suffix (Custom)

Inventory Revenue

Inventory Item

Select fields with the suffix (Custom)

Inventory Back Order Report

Item Type

Select fields with the suffix (Custom)

Inventory Activity Detail

Inventory Item

Select fields with the suffix (Custom)

Stock Ledger

Item

Select fields with the suffix (Custom)

Items Pending Fulfillment

Item Type

Select fields with the suffix (Custom)

Inventory Turnover

Inventory Item

Select fields with the suffix (Custom)

Transfer Order Registry

Transfer Order Transactions

Select fields with the suffix (Custom)

Sales

Sales by Item

Item Type

Select fields with the suffix (Custom)

Sales by Customer

Sales > Item

Sales by Sales Rep

Sales > Item

Sales by Sales Team

Sales Team Sales > Item

Sales by Partner

Sales > Item

Sales by Historical Team

Historical Revenue > Item

Sales by Historical Team (Transaction Date)

Sales > Item

Sales by Promotion

Sales > Item

New Customer Sales

Sales > Item

To customize reports for Merchandise Hierarchy:

  1. Navigate to the report you want to customize in the Reports menu, and select the customize option.

  2. In the Report Builder on the Edit Columns page, edit the name to make it easy to identify in the Saved Reports list.

  3. Select the Item Type 1 row and clear the Group box.

  4. Optional. Select the Item Type column and click the Remove Column button.

  5. Now you need to add the custom merchandise hierarchy fields to your report:

    Important

    In order to create reports that use the current merchandise hierarchy structure, you must select the correct custom merchandise hierarchy fields. See Locating Custom Merchandise Hierarchy Fields for Reports.


    1. Navigate to the correct custom merchandise hierarchy fields. For example, to add the internal custom fields to a Sales report:

      1. In the Add Fields list on the left expand the Item Type list.

      2. Scroll down to the merchandise hierarchy internal custom fields. If you have prefixed the name of the hierarchy levels with [MH] it is easier to locate them in the list.

    2. Drag and drop the internal custom fields into the Report Preview panel.

  6. After you have added all the required fields, you can reorder the columns as needed by clicking the Move buttons or by dragging the columns into position.

    Tip

    In the Column Label field you can edit the name of the column. For example you can remove any prefix you may have added to the level name.


  7.  Select the columns that you want to be able to collapse and expand as needed in the report and check the Group With Previous Column box for each of them.

  8. After you have added the required columns click Save.

Adding Filters to Customized Merchandise Hierarchy Reports

You can use the custom merchandise hierarchy fields created by Custom Segments as filters for your customized reports. Filters let you restrict the view in the report to a specific node or set of nodes.

To add merchandise hierarchy filters to your report:

  1. Navigate to the report you want to customize in the Reports menu, and select the customize option.

  2. Click Filters in the Report Builder menu to open the Filters page.

  3. Now you need to add the custom merchandise hierarchy fields that you want to use as filters:

    1. Navigate to the correct custom merchandise hierarchy fields. See Locating Custom Merchandise Hierarchy Fields for Reports. For example, to add merchandise hierarchy filters to a Sales report:

      1. In the Add Fields list on the left expand the Item Type list.

      2. Scroll down to the merchandise hierarchy internal custom fields. If you have prefixed the name of the hierarchy levels with [MH] it is easier to locate them in the list.

    2. Drag and drop the internal custom fields into the Report Preview panel.

  4. Select the field that represents the hierarchy level you want to filter on. Notice when you click on the custom field, the fields under the Report Component and Field columns in the Choose Filters panel are automatically pre-populated.

  5. Check the box in the Show in Filter Region column for the new filter.

  6. Click in the Value field and select Equal to and click Done.

  7. After you have added the required filters click Save.

You can click the Preview button when you are customizing the report to visualize the report before you save it. Click Return to Customization to return to the Report Builder.

To view your customized reports, go to Reports > Saved Reports > All Saved reports (Administrator).

Note

To view the custom filters in the finished report, click the More button to expand the options toolbar at the bottom of the report.


Example of a Customized Merchandise Hierarchy Report – Sales by Item Summary

In this section we will show you how to customize the Sales by Item Summary report to show data from your current merchandise hierarchy item classification.

With a customized Sales by Item Summary report you can show an overview of your sales, not only organized by item, but also by the levels or nodes in your hierarchy version. In the following image you can see the standard Sales by Item Summary report on the left, and the same report including the merchandise hierarchy elements on the right. Levels can be grouped and then expanded or minimized as needed depending on the level of detail you need to show in the report.

To customize the Sales by Item Summary report for Merchandise Hierarchy:

  1. Navigate to Reports > Sales > Sales by Item > Customize Summary (administrator).

  2. In the Report Builder on the Edit Columns page, edit the name to make it easy to identify in the Saved Reports list. For example, Sales by Item Summary – Merchandise Hierarchy.

  3. Select the Item Type 1 row and clear the Group box.

  4. Optional. Select the Item Type column and click the Remove Column button.

  5. Now you need to add the custom merchandise hierarchy fields to your report:

    Important

    It is vital that you select the correct custom merchandise hierarchy fields in this step. Follow these instructions very carefully.


    1. Navigate to the correct custom merchandise hierarchy fields:

      1. In the Add Fields list on the left expand the Item Type list.

      2. Scroll down to the merchandise hierarchy internal custom fields. If you have prefixed the name of the hierarchy levels with [MH] it is easier to locate the internal custom fields in the list.

    2. Drag and drop the internal custom fields into the Report Preview panel.

  6. After you have added all the required fields, you can reorder the columns as needed by clicking the Move buttons or by dragging the columns into position.

    Tip

    In the Column Label field you can remove the [MH] prefix so that it does not appear in the report.


  7.  Select the columns that you want to be able to collapse and expand as needed in the report and check the Group With Previous Column box for each of them.

  8. Add filters as required to your report. For information regarding how to add filters, see Adding Filters to Customized Merchandise Hierarchy Reports.

  9. After you have added the required columns and filters click Save.