Five (5) Best practices for Calculated Columns in a CDS View
A calculated column is a column that does not exist directly in the database table, but we still need it to meet the business requirement. There can be many reasons for example – Unit Conversion, Currency conversion, creating full name using the first name and the last name columns, etc
The calculation column helps us to push some logic to the database layer but we need to be careful as these calculations still take resources. Some thoughts should be given on whether the calculation column is needed or when it should be created. Although it may vary on case to case basis, let me provide five (5) generic pointers:
Calculated Column is a good idea when:
- Calculation needs addition and multiplication both. If only addition (or multiplication) is needed, then perhaps aggregation functions is sufficient.
- String manipulation is needed based on more than one column.
- A new column can simplify the client reporting in some way. A business considers shipping cost to be critical, thus reporting shipping cost in a new column may be helpful.
- Create it at the very last possible view. If all expenses needs to be reported in USD – so instead of converting every single transaction to USD, do an aggreation based on type of currency first and then apply the currency conversion.
- Complex logic or conversion is needed – helps in code push down.
CDS programming is comparatively new so we need to constantly educate ourselves on the best practices when it comes to the new Virtual Data Modelling paradigm. I hope this makes sense. If it does, it makes sense to follow our LinkedIn page. LinkedIn page is the best place to get the latest updates on training sessions and blogs or to share feedback.
