Walkthrough: Add a Computed Column to a View

บทความ
05/18/2015

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

This walkthrough describes how you can add a computed column to a view in Microsoft Dynamics AX.

A computed column is the output of a computation that inputs a regular column. For example, suppose your table has a column that is named AnnualRent. Your view could return a computed column that is named MonthlyRent and which calculates AnnualRent/12.

You can add the computed column to the select list of columns only. If the computed column is itself a select statement with a where clause, you can include the name of a real column in that where clause.

Prerequisites

To fully understand this walkthrough you must know how to do the following:

How to use the Application Object Tree (AOT).
How to write an method in X++.

Create a View

In this section you create a view named TestCompColView.

Click AOT > Data Dictionary > Views > New View.
Right-click the new View1 node, and then click Properties.
In the Properties window, change the Name property to TestCompColView.
Expand the Views > TestCompColView > Metadata > Data Sources node.
Open a second AOT window and drop the node AOT > Data Dictionary > Tables > CustTable onto the TestCompColView > Metadata > Data Sources node in the first AOT window.
Expand the TestCompColView > Metadata > Data Sources > CustTable_1 > Fields node.
In the other AOT window, expand the TestCompColView > Fields node.
From under the TestCompColView > Metadata > Data Sources > CustTable_1 > Fields node, drop the AccountNum field onto the TestCompColView > Fields node of the other AOT. Also drop the SubsegmentId field in the same way.
Click TestCompColView > Save.

Add a Static Method to the View

The technique to add a computed column begins with you adding a static method to the view. The method must return a string. The system automatically concatenates the returned string with other strings that the system generates to form an entire T-SQL create view statement.

The method that you add to the view typically has at least one call to the DictView.computedColumnString method. The parameters into the computedColumnString method include the name of a data source on the view, and one field name from that data source. The computedColumnString method returns the name of the field after qualifying the name with the alias of the associated table. For example, if the computedColumnString method is given the field name of AccountNum, it returns a string such as A.AccountNum or B.AccountNum.

Steps to add a method to a view

Under AOT > Data Dictionary > Views, expand the node for your TestCompColView view that you created in the previous section.
Under your view, click Methods > New Method. The method editor window is displayed.
Change the method definition to the following:

private static server str compColSubsegAcctMethod()

Note

The method name can be anything, but all the other keywords are required as shown.

Often no parameters are allowed for the method. For more information, see Walkthrough: Add a Computed Column to a Union View.
Write X++ code in the body of the compColSubsegAcct method, as is described in the following section.

Write X++ Code in the Body of the Method

This section provides the entire method for a computed column example. Nothing in the method is tied to any particular computed column. However, later when you add a computed column to the view, you set the ViewMethod property of the column to this method name.

The following string is returned from this method:

substring(A.SUBSEGMENTID,1,1) + ' - ' + A.ACCOUNTNUM

    private static server str compColSubsegAcctMethod()
    {
        #define.ViewName(TestCompColView)
        #define.DataSourceName("CustTable_1")
        #define.FieldSubsegmentId("SubsegmentId")
        #define.FieldAccountNum("AccountNum")
        str sReturn,
            sAccountNum,
            sSubsegmentId;
        DictView dictView2;
    
        // Construct a DictView object for the present view.
        dictView2 = new DictView(tableNum(#ViewName));
    
        // Get a string that has the target field name
        // propertly qualified with an alias (such
        // as "A." or "B.").
        sAccountNum = dictView2.computedColumnString
            (#DataSourceName,
            #FieldAccountNum,
            FieldNameGenerationMode::FieldList,
            true);
    
        sSubsegmentId = dictView2.computedColumnString
            (#DataSourceName,
            #FieldSubsegmentId,
            FieldNameGenerationMode::FieldList,
            true);
    
        sReturn = "substring("
            + sSubsegmentId
            + ",1,1) + ' - ' + "
            + sAccountNum;
    
        // Helpful confirming or diagnostic information.
        info(sAccountNum);
        info(sSubsegmentId);
        info(sReturn);
    
        return sReturn;
    }

The output to the Infolog from the previous method is shown in the following.

Displayed in the Infolog
Synchronize database A.ACCOUNTNUM A.SUBSEGMENTID substring(A.SUBSEGMENTID,1,1) + ' - ' + A.ACCOUNTNUM

Synchronize database

A.ACCOUNTNUM

A.SUBSEGMENTID

substring(A.SUBSEGMENTID,1,1) + ' - ' + A.ACCOUNTNUM

Add a Computed Column to the View

In this section you add a computed column to the view.

Right-click the Fields node under your view, and then click String Computed Column.
For the new field or column, change the Name property to compCol_Subseg_Acct.
Change the StringSize property to 32.

Link the Computed Column to the Method

In this section you relate the computed column to the static method that you added in a previous section.

For the computed column, you set the ViewMethod property to the name of the compColSubsegAcctMethod method that you wrote in a previous step.

Synchronize the View to the Database

In this section you synchronize the changes to your view from the AOT into the underlying database.

Right-click the AOT node for your view, and then click Save. This causes the Microsoft Dynamics AX system to send the T-SQL create view statement to the underlying database.

Activities During Synchronization

During synchronization the Microsoft Dynamics AX system detects the computed column. The system obtains the name of the static method that builds the computed column from the ViewMethod property of the computed column. At that moment the system calls your method.

The system enhances the string that your method returns by wrapping it in a T-SQL cast function. The enhanced string is then added to the whole string that contains the T-SQL create view statement. Your view is created in the database before synchronization finishes.

Later, your method is not called when the view is queried. Your method is called only if the view is resynchronized to the database.

Tip

If your view fails to synchronize, it might be that your method for the computed column generates invalid T-SQL syntax. It can help you diagnose the problem if you activate the SQL tracing feature of Microsoft Dynamics AX. In the trace you can see the entire create view statement and your substring in it. For information about how to activate SQL tracing, see Tracing with the Tools Menu.

The Final View

The following T-SQL create view statement is sent by Microsoft Dynamics AX to the database.

CREATE VIEW [dbo].[TESTCOMPCOLVIEW]
AS
    SELECT
        A.SUBSEGMENTID AS SUBSEGMENTID,
        A.ACCOUNTNUM AS ACCOUNTNUM,
        A.DATAAREAID AS DATAAREAID,
        A.RECID AS RECID,
        (CAST
            (
                (substring(A.SUBSEGMENTID,1,1)
                + ' - '
                + A.ACCOUNTNUM
                )
                    AS
                    NVARCHAR(32)
            )
        )
            AS COMPCOL_SUBSEG_ACCT
    FROM
        CUSTTABLE A

The String Returned from the Method

The previous compColSubsegAcct method returns the following string:

substring(A.SUBSEGMENTID,1,1) + ' - ' + A.ACCOUNTNUM

The system enhances the returned string into the following T-SQL code segment. The enhancements include the following:

The CAST function
The datatype is added – which here is NVARCHAR(32).
The name of the computed column – which here is COMPCOL_SUBSEG_ACCT.

The enhancements are used to build the following T-SQL code segment, which you can see is part of the final create view statement.

        (CAST
            (
                (substring(A.SUBSEGMENTID,1,1)
                + ' - '
                + A.ACCOUNTNUM
                )
                    AS
                    NVARCHAR(32)
            )
        )
            AS COMPCOL_SUBSEG_ACCT

See the Data in the Computed Column

The data that is generated for the computed column can be seen by using the AOT. Click AOT > Data Dictionary > Views > TestCompColView > Open.

Sample Data from the View

The following table displays sample data that includes the computed column.

SubsegmentId	AccountNum	CompCol_Subseg_Acct
Medium	4000	M - 4000
Gross	4001	G - 4001

แชร์ผ่าน

Walkthrough: Add a Computed Column to a View

Prerequisites

Create a View

Add a Static Method to the View

Steps to add a method to a view

Write X++ Code in the Body of the Method

Add a Computed Column to the View

Link the Computed Column to the Method

Synchronize the View to the Database

Activities During Synchronization

The Final View

The String Returned from the Method

See the Data in the Computed Column

Sample Data from the View

See also

แหล่งทรัพยากรเพิ่มเติม