In SAP S/4HANA Cloud, it is common to encounter scenarios where different types of data are stored in the same column across multiple rows. To meet reporting requirements, this data often needs to be displayed in separate columns on the same row. This article demonstrates how to achieve this transformation using Custom CDS Views.
Example Scenario: Tax Data Conversion
Consider a table where various tax components (e.g., Total Amount, Base Amount, IGST, CGST, SGST) are stored in a single column, AmountinCoCodeCurrency, but on different rows. The goal is to display these values in distinct columns in the same row for a CDS report.
Input Table
Company Code
Journal Entry
Fiscal Year
Posting View Item
AmountinCoCodeCurrency
Line Item ID
Transaction Key
Financial Account Type
1110
94000001
2024
1
1250
K
1110
94000001
2024
2
1050
S
1110
94000001
2024
3
100
T
JIS
1110
94000001
2024
4
100
T
JIC
1110
94000001
2024
5
0
T
JII
Output Table
Company Code
Journal Entry
Fiscal Year
Total Amount
Base Amount
IGST
CGST
SGST
1110
94000001
2024
1250
1050
100
100
0
Steps to Achieve the Transformation
Create Custom CDS Views for Each Tax Component
For each tax component, create a Custom CDS View to extract its specific data. Use a unique filter condition to isolate the relevant rows.
Base Amount:
Filter: Line Item ID = ‘S’ and Financial Account Type != ‘K’.Total Amount:
Filter: Financial Account Type = ‘K’.IGST:
Filter: Transaction Key = ‘JIS’.CGST:
Filter: Transaction Key = ‘JIC’.SGST:
Filter: Transaction Key = ‘JII’.Combine CDS Views
After creating individual CDS Views for each tax component, combine them into a single Custom CDS View using an Inner Join. Use common fields such as Company Code, Journal Entry, and Fiscal Year as the join conditions to align the data.
Define the Output Structure
The resulting CDS View should include columns for:
Company CodeJournal EntryFiscal YearTotal AmountBase AmountIGSTCGSTSGST
Key Considerations
Filter Design: Ensure that each tax component has a unique identifying filter to avoid overlapping or missing data.Join Conditions: Use precise join conditions to maintain data integrity and avoid duplication.Performance: Test the combined CDS View for performance, especially with large datasets. Optimize as needed.
By following this approach, you can efficiently transform row-based data into columnar format in a CDS Report, as illustrated in the example. This method provides flexibility for customizing reports and meeting business requirements in SAP S/4HANA Cloud.
In SAP S/4HANA Cloud, it is common to encounter scenarios where different types of data are stored in the same column across multiple rows. To meet reporting requirements, this data often needs to be displayed in separate columns on the same row. This article demonstrates how to achieve this transformation using Custom CDS Views.Example Scenario: Tax Data ConversionConsider a table where various tax components (e.g., Total Amount, Base Amount, IGST, CGST, SGST) are stored in a single column, AmountinCoCodeCurrency, but on different rows. The goal is to display these values in distinct columns in the same row for a CDS report.Input TableCompany CodeJournal EntryFiscal YearPosting View ItemAmountinCoCodeCurrencyLine Item IDTransaction KeyFinancial Account Type111094000001202411250 K111094000001202421050S 11109400000120243100TJIS 11109400000120244100TJIC 111094000001202450TJII Output TableCompany CodeJournal EntryFiscal YearTotal AmountBase AmountIGSTCGSTSGST1110940000012024125010501001000 Steps to Achieve the TransformationCreate Custom CDS Views for Each Tax ComponentFor each tax component, create a Custom CDS View to extract its specific data. Use a unique filter condition to isolate the relevant rows.Base Amount:Filter: Line Item ID = ‘S’ and Financial Account Type != ‘K’.Total Amount:Filter: Financial Account Type = ‘K’.IGST:Filter: Transaction Key = ‘JIS’.CGST:Filter: Transaction Key = ‘JIC’.SGST:Filter: Transaction Key = ‘JII’.Combine CDS ViewsAfter creating individual CDS Views for each tax component, combine them into a single Custom CDS View using an Inner Join. Use common fields such as Company Code, Journal Entry, and Fiscal Year as the join conditions to align the data.Define the Output StructureThe resulting CDS View should include columns for:Company CodeJournal EntryFiscal YearTotal AmountBase AmountIGSTCGSTSGSTKey ConsiderationsFilter Design: Ensure that each tax component has a unique identifying filter to avoid overlapping or missing data.Join Conditions: Use precise join conditions to maintain data integrity and avoid duplication.Performance: Test the combined CDS View for performance, especially with large datasets. Optimize as needed.By following this approach, you can efficiently transform row-based data into columnar format in a CDS Report, as illustrated in the example. This method provides flexibility for customizing reports and meeting business requirements in SAP S/4HANA Cloud. Read More Technology Blogs by Members articles
#SAP
#SAPTechnologyblog
