Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Blogs / Learn with Subs / Make calculations faster by...

Make calculations faster by using computed columns, wisely

Subhad365 Profile Picture Subhad365 7 User Group Leader

You must have bumped across a situation in your implementation career, where you needed to class your data, based on year. As an example:




It becomes very difficult to design such a structure, where individually you might need to write code for every customer, for each year:



select sum(AmountMST) from custTable

where custTable.TransDate >= fromDate

&& custTable.TransDate <= toDate;


It will eventually result in extreme slowness of the process, which will make it go through each and every customer, over and over for all customers, for all the years.

Definitely not a good solution.

In all such cases, we can make a solution like this:

a. Pre-calculating the year as 'Year-names' in a view, where we can store the years like 2021, 2022, 2023, (no hardcoding):


Here 'YearName' is a computed column, which looks like this:


private static server str compYearName()

    {

        

        #define.PPCustTransYearNameView(PPCustTransYearNameView)

        #define.DataSource(CustTrans)

        #define.RecIdField(RecId)


        str yrExp;

        yrExp = 'select Year(TransDate) from custTrans where RecId = ' +

                    SysComputedColumn::returnField(

                                                    tableStr(#PPCustTransYearNameView),

                                                    identifierStr(#DataSource),

                                                    fieldStr(#DataSource, #RecIdField)

                                                    );


        return yrExp;

    }

Look at the code: it just takes out the Year part of the TransDate field of CustTrans table. And then we are using it as a computed column.




b. Creating another view (which refers to this view)  so as to bucket up the balances into quanta of years:




Here we are referring to our previously created view and then using the YearName column and the AmounMST column (which is a sum column):




 
As a result the view gives the year wise balances as follows:




You could see clearly that it's giving you year wise balances for every customer.


c. Calling the view, by passing on the customer and year: Now you can directly call this view, by passing on customer code and the year name. This will directly give you the output without even needing to calculate for every customer and resulting in unwanted slowness.  

Where you can use this:

1. Year wise customer sales related processes, reports, 

2. Customer trend.

3. Advanced analytics, etc.

Comments

*This post is locked for comments