How to Analyze Profit Margin for BOMs with Item Category Groups CPFH and DISH Based on Custom CDS Views and Queries in Sales
Previously It was challenging to perform sales analytics on bills of materials (BOMs), especially BOMs with item category groups CPFH and DISH (referred to as CPFH-based BOMs and DISH-based BOMs). For these BOMs, pricing and cost are separately processed on main item and subitem levels.
As of SAP S/4HANA Cloud 2302, several BOM related fields are available in analytical CDS views and analytical apps. These fields are intended for flexible analysis of sales data on the BOM level, main item level, or subitem level.
As an example, this blog describes how to build custom analytical queries to analyze profitability for CPFH-based and DISH-based BOMs.
Introduction to New Fields
The BOM-related fields newly available include:
- Pricing reference product of main item (MainItemPricingRefProduct)
- Higher-Level item (HigherLevelItem)
- Propagate profitability segment (PropagatePrftbltySgmt2BOM)
- Determine cost (CostDeterminationIsRequired)
Refer to the following table for multi-level differentiation for CPFH-based and DISH-based BOMs:
|Field||CPFH-based BOM||DISH-based BOM|
|Main Item||Subitem||Main Item||Subitem|
|Determine cost (CostDeterminationIsRequired)||Blank||X||X||Blank|
|Propagate profitability segment (PropagatePrftbltySgmt2BOM)||A||B||A||B|
For more information about these fields and related CDS views and apps, see BOM Fields in Analytical CDS Views and Apps.
Creating Custom Analytical Query
1. In the Custom CDS Views app, create a custom view. Specify the scenario as Analytical Cube and select I_BillingDocumnetItemCube as the primary data source.
2. Create a calculated field (Main Item Product in 3rd/4th BOM) and maintain the calculation logic as follows. This field can retrieve the main item product for each item in CPFH-based and DISH-based BOMs.
- For CPFH-based and DISH-based BOMs, this field makes BOM-level aggregation possible in later profitability analysis.
- For non CPFH-based or DISH-based BOMs, this field retrieves the product of an item.
case when (I_BillingDocumentItemCube.HigherLevelItem is not initial and
(I_BillingDocumentItemCube.PropagatePrftbltySgmt2BOM = ‘A’ and I_BillingDocumentItemCube.CostDeterminationIsRequired = ”) OR
(I_BillingDocumentItemCube.PropagatePrftbltySgmt2BOM = ‘B’ and I_BillingDocumentItemCube.CostDeterminationIsRequired = ‘X’) OR
(I_BillingDocumentItemCube.PropagatePrftbltySgmt2BOM = ‘A’ and I_BillingDocumentItemCube.CostDeterminationIsRequired = ‘X’) OR
(I_BillingDocumentItemCube.PropagatePrftbltySgmt2BOM = ‘B’ and I_BillingDocumentItemCube.CostDeterminationIsRequired = ”))
3. Select KPIs such as the net value, cost, and profit margin.
4. Choose Check. If no error exists, choose Publish.
5. In Next Steps, choose Custom Analytical Queries.
6. Specify the data source as the custom CDS view (that is, YY1_DEMO_3RD4THBOM_BILLING) you created earlier.
7. Maintain other attributes as needed.
8. Choose Preview. If everything is fine, choose Publish.
Analyzing Profit Margin
The following figure shows a CPFH-based BOM, for which pricing is processed on the main item level and cost is processed on the subitem level.
1. Create a sales order based on this BOM, complete the subsequent processes, and generate billing documents.
2. In the Custom Analytical Queries app, run the query you created earlier.
In this example, two billing documents exist for this BOM.
3. To analyze KPIs on the main item or subitem level, you can remove dimensions such as the billing document and sold-to party.
To analyze profitability on the BOM level, you need to additionally remove the product dimension and keep the Main Item Product in 3rd/4th BOM field (which is the calculated field you created earlier).
This blog covers how to create custom CDS views and queries for analyzing CPFH-based and DISH-based BOMs. Hope it can help you better understand flexible analysis of sales data on the BOM level, main item level, or subitem level for BOMs. Please leave your comments below if you have any feedback. If you like this blog, please don’t hesitate to follow my profile.
Find more about Sales topics in SAP S/4HANA and SAP S/4HANA Cloud: