1. SuiteAnalytics
  2. Workbook
  3. Analytics Data Source Overview
  4. The Analytics Data Source and SuiteAnalytics Workbook
  5. Analytics Data Source Changes
  6. Open Sales Orders Lines

Open Sales Orders Lines

This saved search displays the items allocated to open sales orders in your account, so that you can better monitor your inventory.

To perform this search using the Saved Search application, all required criteria and results fields are available through the transaction record type. To recreate this search using SuiteAnalytics Workbook however, some required fields are only available through the transaction line and transaction accounting line record types. You must therefore join these record types in your dataset to recreate the search. Additionally, some required fields might have different labels in Workbook. The following table lists key differences in fields between the saved search and the workbook:

Key Field Differences in Workbook

Field Name in Saved Search

Record Type in Workbook

Required Join in Workbook

Field Name in Workbook

Main Line

Transaction Line

Transaction > Transaction Line

Main Line

Tax Line

Transaction Line

Transaction > Transaction Line

Tax Line

Closed

Transaction Line

Transaction > Transaction Line

Closed

Account Type

Account

Transaction > Transaction Line > Transaction Accounting Line > Account

Type

Shipping Line

Transaction Account Line

Transaction > Transaction Line > Transaction Accounting Line

Shipping Cost Item Type
Warning:

In this workbook you must add fields from the transaction line and transaction accounting line record types. When you add fields from either the transaction line or transaction accounting line record type to a transaction dataset, data duplication can occur. For more information, see Joining Transaction Line and Transaction Accounting Line in a Dataset.

To recreate this search using Workbook:

Create a new dataset with transaction as the root record type. Then, define the dataset as follows:

Important:

Users assigned to the Sales role do not have access to the account record type in Workbook, which is required to properly recreate this saved search. If you create and share this workbook with users assigned to the Sales role, make sure you give them access to the account record type first.

Root Record Type

Joined Record Type(s)

Custom Formula Field(s)

Data Grid

Criteria

Transaction

Transaction Line > Transaction Accounting Line > Account

Important:

Make sure you join the transaction accounting line record type from the transaction line record type. If you join both the transaction line and transaction accounting line record types directly from the transaction record, it can duplicate or otherwise skew your data. For more information, see Joining Transaction Line and Transaction Accounting Line in a Dataset.

Create the following formula fields on the Dataset Builder:

  • Name: Unfulfilled Items

    Output Type: Float

    Definition: -{‌transactionlines.quantity}-NVL({‌transactionlines.quantitycommitted},0)-NVL({‌transactionlines.quantityshiprecv},0)

    Use this formula to replace null values in the Transaction Line Quantity and Quantity committed fields with 0.00.

  • Name: Positive Item Quantity

    Output Type: Float

    Definition: -{‌transactionlines.quantity}

    Use this formula to display your item quantities as positive values.

For more information about how to use formula fields in SuiteAnalytics Workbook, see Formula Fields.

Add the following fields to the grid from the transaction record type:

  • Transaction

  • Status

  • Posting Period

  • Entity

Add the following fields to the grid from the transaction line record type:

  • Item

  • Location

  • Quantity

  • Committed

  • Quantity Shipped/Received/Picked Up

Add the following custom formula fields to the grid:

  • Custom Formula Field 1 (Unfulfilled Items)

  • Custom Formula Field 2 (Positive Item Quantity)

Set the following criteria in the Dataset Builder:

  • [Custom Formula Field 2] greater than 0.00

  • Type is Sales Order

  • Status any of Sales Order : Partially Fulfilled and Sales Order : Pending Fulfillment and Sales Order : Pending Billing/Partially Fulfilled

  • Transaction Line: Main Line is false

  • Transaction Line: Tax Line is false

  • Transaction Line: Item Type none of Shipping Cost Item

  • Account: Type is income

Note:

If the Multi-Book Accounting feature is enabled in your account, you can use the accounting book field in a criteria filter to view data for specific accounting books. To access the accounting book field, use the join path transaction>transaction line> transaction accounting line.

Open Sales Orders Lines Pivot Tables

Create a new workbook using the open sales order lines dataset you created, and set the following fields to the appropriate dimensions for each pivot table:

After you set each field to the appropriate table dimension, click the refresh Report refresh icon icon to generate the table.

You can also customize the table and add totals and grand totals, or rename each table. For more information, see Pivot Table Customization.

Open Sales Orders Lines Charts

By recreating this saved search using Workbook, you can also chart your source data to visualize the information using different chart types.

Create the following charts by dragging the listed fields from the Dataset Panel to the corresponding section of the Layout panel, and select the chart type.

After you set each field and the chart type, click the Refresh Report refresh icon icon to generate the chart. You can also filter values, add a title and subtitle, and rename each axis. For more information, see Workbook Charts.

Related Topics

General Notices