Simple view with computed columns for date functions

In a recent development project we had to add query ranges and grouping on the week, month or year number dynamically. Initial thoughts were to use expressions in query ranges combined with date functions like wkOfYr. However after a few attempts it was clear that this was not the best solution to our problem. That is when we decided to use a view with computed columns.

We created a view and added the original table as a datasource. The idea was to add the week of year, month number and year number as computed columns and then use them in the query as normal fields.

The following are the steps you need to do to add a computed column for the week number:

  • First create a new integer computed column for the week number. Right-click on fields under the view and select new > integer computed column. Let’s call it MyComputedColumn for our example.
  • Then create a method on your view which will calculate the value for the computed column. The method must be set to private static of type string. For AX 2012 you should set the method to run on server. The method below is self-explanatory, it gets the SQL field name from the view and returns an SQL select statement to convert the date to week number using the SQL function DATEPART.
private static str myDateWeek()
{
        DictView dictView = new dictView(tableNum(MyView));

        str myTransDate = dictView.computedColumnString(
            identifierStr(DataSourceName),
            fieldStr(DSTableName, TransDate));

        return "SELECT DATEPART(WEEK," + myTransDate +")";
}
  • Next set the “View Method” property on the computed column you created earlier.
  • Finally build and db synch your code. Remember the view is created in the database under the views section. Here you can see the result of the computed column.
(CAST ((SELECT DATEPART(WEEK,T1.MyTransDate)) AS INT)) AS MyComputedColumn

For the month and year you can create additional computed columns with their respective methods.

"SELECT MONTH(" + myTransDate +")";

"SELECT YEAR(" + myTransDate +")";

Remember that a computed column works by adding SQL query syntax to the view. Therefore you need to use SQL functions like DATEPART(WEEK,%1) and not D365 FO/ AX functions like WkOfYr in the returned select statement. You can easily find SQL syntax functions on the web such as this one at w3schools.