SuiteAnalytics Workbook and Merchandise Hierarchy

 

SuiteAnalytics Workbook enables you to create customizable workbooks that combine queries, pivot tables, and charts. Workbooks are ideally suited to help you analyze items organized in merchandise hierarchies, enabling you to slice sales performance data by product categories (hierarchy nodes).

The following options are available to analyze your merchandise hierarchy with SuiteAnalytics:

For more information, see the help topic SuiteAnalytics Workbook.

Merchandise Hierarchy Workbook

A default Merchandise Hierarchy Workbook has been created for you. This workbook has all your custom Merchandise Hierarchy fields already added to the dataset. It also provides a pivot table that includes Merchandise Hierarchy fields by default. This workbook can be opened directly from the Hierarchy Manager. Depending on the data you want to see, there are two methods to open the workbook, each method provides a different view of your merchandise hierarchy:

  • Generic overview – the workbook includes all levels, nodes, and items in your merchandise hierarchy.

  • Contextual – the workbook includes a selected node and all the items assigned to nodes in the hierarchy path, plus their applied attributes.

The following image is an example of the Merchandise Hierarchy Workbook displaying a generic overview. It can be used to analyze your merchandise hierarchy sales without any customizing. The default Sales by Hierarchy Node pivot table displays sales sliced by hierarchy nodes. However, it does not include merchandise hierarchy attributes.

To open the default Merchandise Hierarchy Workbook with a generic view:

  1. Go to Setup > Merchandise Hierarchy > Hierarchy Manager (Administrator).

  2. Click the Open Workbook link at the top of the Hierarchy Manager.

Note

If you have the Analytics Administrator permission, you can also access the default Merchandise Hierarchy Workbook with a generic view from the Workbooks tab on the Analytics home page.


To open the default Merchandise Hierarchy Workbook with a contextual view:

  1. Go to Setup > Merchandise Hierarchy > Hierarchy Manager (Administrator)..

  2. Select the node in the hierarchy tree that will provide the context (the workbook will display items assigned to the selected node and any other nodes in the hierarchy path).

  3. Click the Attribute List tab at the bottom of the Information Panel.

  4. In the Attributes list point to the attribute you want to be displayed in the workbook. Click the Workbook icon that appears.

Note

If you have the Analytics Administrator permission, you can also open the Merchandise Hierarchy Workbook from the Workbooks list on the Analytics tab.


Analyzing Merchandise Hierarchy with Sales Workbook Templates

You can also analyze sales performance sliced by Merchandise Hierarchy elements using the SuiteAnalytics Sales workbook templates. Custom Merchandise Hierarchy fields are included in the datasets used by the following templates by default:

  • Sales (Invoiced) Workbook

  • Sales (Ordered) Workbook

If you have the Analytics Administrator permission, you can access these workbooks from the Workbooks tab on the Analytics home page.

Tip

If you create a new workbook and use Sales (Invoiced) or Sales (Ordered) as the root record type, note that the workbook will not include custom Merchandise Hierarchy fields by default.


You can drag custom Merchandise Hierarchy fields included in the dataset for these templates to columns or rows in a pivot table or chart. The following image is an example of Merchandise Hierarchy fields used in a Sales by Item pivot table.

For more information, see the help topic Workbook and Dataset Templates.

Note

Sales workbook templates do not include merchandise hierarchy attribute fields by default. To include attributes in your workbooks you must create a custom workbook. For more information, see Creating Custom Merchandise Hierarchy Workbooks.


Creating Custom Merchandise Hierarchy Workbooks

With SuiteAnalytics Workbook, you can create custom workbooks that include all the merchandise hierarchy elements you need, including merchandise hierarchy attributes. SuiteAnalytics Workbook supports multilevel joins, which 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.

If the Merchandise Hierarchy workbook does not include all fields you require, you can create a custom workbook by adding the required fields to the dataset. For more information, see the Custom Workbooks and Datasets topic.

To include Merchandise Hierarchy fields in a custom workbook, you must manually add them to the workbook. You should start with a Sales record type and find the Merchandise Hierarchy fields under Item in the Records list:

Important

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.

For reports that use historical hierarchy data when your system includes multiple merchandise hierarchy versions, see Using Hierarchy Versions in Workbooks and Reports.


Including Attributes in a Custom Merchandise Hierarchy Workbook

The following example shows how to create a workbook that includes merchandise hierarchy attributes. To demonstrate how merchandise hierarchy attributes 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.

Note

You can open the default Merchandise Hierarchy Workbook displaying contextual attribute data directly from the Merchandise Hierarchy Manager. It will not however, display all attributes for all items in the hierarchy. For more information, see Merchandise Hierarchy Workbook.


For more information regarding attributes, see Applying Attributes to Items in a Merchandise Hierarchy.

To create a custom workbook to include merchandise hierarchy fields and attributes you must complete the following steps in order:

  1. Start with the Merchandise Hierarchy workbook

  2. Add attribute fields to the dataset

  3. Set up your pivots and charts

Open the Merchandise Hierarchy Workbook for Customization

You should use the default Merchandise Hierarchy workbook as the starting point. The dataset used by this workbook includes all your custom Merchandise Hierarchy fields by default. It also provides a default Sales by Hierarchy Node pivot table which can incorporate attributes after they have been added to the dataset.

To open the Merchandise Hierarchy workbook:

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

  2. On the Analytics dashboard, select the Workbooks list.

  3. Select the Merchandise Hierarchy Workbook.

Note

You can also open this workbook directly from the Hierarchy Manager. See Merchandise Hierarchy Workbook.


Add Custom Fields to the Dataset in Merchandise Hierarchy Workbooks

The following steps show you how to add attribute fields to the dataset of your merchandise hierarchy workbook. Merchandise hierarchy attributes are custom item fields and therefore are located under Item in the Records list on the Dataset tab. Only fields that are added to the Data Grid can be used to generate a pivot table or chart.

In this example we will add the following attribute fields:

  • Fabric

  • Seasonal Collection

To add attribute fields to the dataset of your workbook:

  1. On the Dataset tab click the contextual menu button for the Merchandise Hierarchy workbook and click Open Dataset.

  2. Click Item in the Records list.

  3. Double-click the attribute you want to add in the Fields list. In our example, the first attribute we select is Fabric.

  4. Repeat steps 2 through 3 to add the Seasonal Collection attribute.

  5. Click Apply to apply the changes.

  6. Click Save As and enter a unique name to save your new custom Merchandise Hierarchy workbook.

Your selected attributes are now included in the dataset for your workbook. You can now customize your pivots and charts to include the attribute values.

Customize Pivots and Charts in Merchandise Hierarchy Workbooks

The Merchandise Hierarchy Workbook has one predefined pivot table by default, the Sales by Hierarchy Node pivot table. In our example we will customize the Sales by Hierarchy Node pivot table to include Fabric attribute values. You can add merchandise hierarchy attributes to any available pivot tables and charts provided by workbook templates, or create your own as needed.

To customize a pivot table in your merchandise hierarchy workbook:

  1. Click the Sales by Hierarchy Node tab. Note, this tab appears by default in Merchandise Hierarchy Workbook.

  2. Double–click the desired fields in the Fields list to add them to the Rows list in the Layout panel.

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

    • [MH] Department

    • [MH] Silhouette

    • [MH] Class

    • Fabric

    In this example we also add Quantity (Sum) to the Measures tab.

  3. Select the Item field in the Rows panel and click the Down Arrow icon in the Rows panel header to move it to the last row.

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

    Important

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


Remember to save your workbook. You can save your workbook at any time, click the Menu icon, and select Save (or Save As to save a copy of your workbook).

For more details regarding working with pivots, including formatting the layout, changing the summary type, or adding filters, see the Step 5: Pivot Your Dataset Query Results topic.