Scenario
User needs a report of the items shipped on a monthly basis. The report needs to have the months as columns for easier visualization and data analysis.
Currently, pulling the data on a per month basis in a Report is not possible yet. We do have an enhancement related to this:
- Enhancement # 138533 - Reports > Ability to run any (or most) reports in matrix format, using any field as pivot columns
As a workaround, a Saved Search or a SuiteAnalytics Workbook Pivot can be created to achieve the desired report.
This article discusses how to create both a Saved Search and a SuiteAnalytics Workbook Pivot to get the Quantity of Items Shipped Per Month. Before we begin, please note the following:
- The steps provided are a general guide on how to create a report with the data presented in separate columns per month.
- This can also be applied to get information like Quantity Sold, Received, Returned, Adjusted, etc per Month.
- You can modify the criteria as necessary to get the data you require.
Saved Search (Item)
1. Go to Lists > Search > Saved Searches > New
2. Select Item
3. Add the following fields in the Criteria and Results tab
Note: You can modify or set other criteria as needed.
Formula used:
- Jan - CASE WHEN ((TO_CHAR(TO_DATE({transaction.trandate}),'MM')) = '01') THEN {transaction.quantity} ELSE 0 END
- Feb - CASE WHEN ((TO_CHAR(TO_DATE({transaction.trandate}),'MM')) = '02') THEN {transaction.quantity} ELSE 0 END
- Mar - CASE WHEN ((TO_CHAR(TO_DATE({transaction.trandate}),'MM')) = '03') THEN {transaction.quantity} ELSE 0 END
- Apr - CASE WHEN ((TO_CHAR(TO_DATE({transaction.trandate}),'MM')) = '04') THEN {transaction.quantity} ELSE 0 END
- May - CASE WHEN ((TO_CHAR(TO_DATE({transaction.trandate}),'MM')) = '05') THEN {transaction.quantity} ELSE 0 END
- Jun - CASE WHEN ((TO_CHAR(TO_DATE({transaction.trandate}),'MM')) = '06') THEN {transaction.quantity} ELSE 0 END
- Jul - CASE WHEN ((TO_CHAR(TO_DATE({transaction.trandate}),'MM')) = '07') THEN {transaction.quantity} ELSE 0 END
- Aug - CASE WHEN ((TO_CHAR(TO_DATE({transaction.trandate}),'MM')) = '08') THEN {transaction.quantity} ELSE 0 END
- Sep - CASE WHEN ((TO_CHAR(TO_DATE({transaction.trandate}),'MM')) = '09') THEN {transaction.quantity} ELSE 0 END
- Oct - CASE WHEN ((TO_CHAR(TO_DATE({transaction.trandate}),'MM')) = '10') THEN {transaction.quantity} ELSE 0 END
- Nov - CASE WHEN ((TO_CHAR(TO_DATE({transaction.trandate}),'MM')) = '11') THEN {transaction.quantity} ELSE 0 END
- Dec - CASE WHEN ((TO_CHAR(TO_DATE({transaction.trandate}),'MM')) = '12') THEN {transaction.quantity} ELSE 0 END
4. Enter a Search Title
5. Click Save & Run
Here is how the search will look like:
Limitation:
If you use this, you can only have 1 year per Saved Search. If you have multiple years in the Date range, you will not get accurate results, because the formula we used is based on the month.
For example, you have data for January 2020 and January 2021. The quantity shipped for both will be included in the 1st column of the search, under the month of January.
SuiteAnalytics Workbook
1. Click the Analytics tab
2. Go to the Datasets tab, then click the New Dataset button
3. Look for the Transaction dataset
4. Add the following fields in in the columns:
- Status
- Transaction Line: Item
- Transaction Line: Item Type
- Transaction Line: Quantity
- Transaction Line: Location
5. Add a Formula field to get the Absolute value of the Quantity. Add this field as a column as well.
6. Add the following as Criteria:
7. Click the Save button, enter a name for the Dataset, then click Save
8. You will receive a confirmation that the Dataset has been saved. Click the Create New Workbook button.
9. In the Workbook, create a Pivot
10. Drag and Drop the following fields accordingly, then click the Refresh button to generate the Pivot
11. Enable the totals
Here's how it would look like:
12. Once you're satisfied with the Pivot, click Save.
For more information about SuiteAnalytics Workbook, please refer to SuiteAnswers 92690.
-----
If you find this information useful, let us know by reacting or commenting on this post!