Computed Columns in Microsoft Dynamics 365 Finance and Operations offer a versatile way to dynamically calculate and display data without physically storing it in the database.
Computed columns are useful for tasks like formatting values, calculating totals, or deriving new data from existing fields without storing the computed values in the database.
Ready to dive into computed columns? This blog provides an overview, step-by-step guidance, and practical implementation tips for using computed columns in Dynamics 365 Finance and Operations.
What are computed columns?
Computed Columns are virtual fields that dynamically calculate values when queried. Unlike traditional fields, they are not physically stored in the database, making them ideal for scenarios where real-time calculations are needed without modifying database schema.
For example, if you need a field that shows the sum of two numbers, X and Y, you can simply use a computed column Z that calculates X+ Y dynamically.
Why use computed columns? Benefits you should know
The purpose of computed columns in Dynamics 365 Finance and Operations is to perform calculations and derive new data based on existing columns within a table. Here are some of it their benefits:
- Improved performance: They avoid unnecessary data storage while offering fast calculations.
- Dynamic filtering and sorting: Unlike display methods, computed columns support filtering and sorting within forms and views.
- Enhanced flexibility: They can be seamlessly used in data entities and views, enabling customized solutions.
Practical example: Using computed columns to display financial dimensions
Here’s a practical application where we demonstrate displaying financial dimensions as fields in a form grid using a view. These dimensions are fetched directly from tables via computed columns, ensuring both efficiency and high performance.
In this example, we focus on financial dimensions, but the same approach applies to ledger dimensions as well. The main difference lies in the source tables:
- Financial dimensions: Data is retrieved from the DimensionAttributeValueSet table.
- Ledger dimensions: Data is sourced from the DimensionAttributeValueCombination table.
While SQL contains all the necessary data within DimensionAttributeValueSet (or DimensionAttributeValueCombination), these tables are not directly accessible in Visual Studio. This is where computed columns come into play. Computed columns enable us to create a new column by writing a direct SQL query within a method, allowing us to retrieve and display the required fields seamlessly.
In this example, we’ll retrieve fields like BankValue, LegalEntityValue, and ProjectsValue and display them as computed columns in our view. This approach is both straightforward and optimized, ensuring excellent performance. Let’s walk you through the process step by step.
