Microsoft Dynamics Ax Views

Views are used to make extraction of data easier. A view is the result of an inner join of two or  more tables. Views are read only and support aggregated functions on the fetched data.

In a relational database, data is divided into numerous tables to prevent redundant data and for better performance. But extracting data for reports becomes more complex as you will often have to fetch data from several tables, in such scenarios we can create a view consisting of fields from different tables which are joined.

Example (1): Let’s create a view to sum customer transactions and print customer information.

1.       Go to the node Views, right-click and select New View. Rename the view to "MyFirstview" using the property sheet.

2.      Locate the node Metadata/Data Sources, right-click and select New Data Source. Go the property sheet for the new data source and pick the table CustTable using the property Table.

3.      Expand the data source CustTable and go to the node CustTable/Data Sources. Add an additional data source by right-clicking. Select CustTrans as the table for the data source.

4.      Open the property sheet for CustTrans and set the property Relations to Yes. Expand the node Relations for the CustTrans data source and check that a relation has been added.

5.      Right-click the node Metadata and choose Open New Window. This will open a new window with the sub tree metadata from MyFirstView.
Drill down the new window to CustTable/Fields. Select the fields AccountNum, Name and CustGroup and drag the selected fields to the other window at the node Fields at the first level of the view.

6.      Repeat step 5 by dragging the field AmountMST from CustTrans/Fields. Open the property sheet for the new view field and set the property Aggregation to Sum.

7.      Save the new view.

Output:

In this example a single field from the customer transaction table was used for an aggregated function. When using aggregate functions, the records in the related table will be fetched group by, i.e, here customer transactions were summed based on customer (grouped by customer).

If the field TransDate from CustTrans was added without any aggregation, the field AmountMST would have been calculated and grouped by customer and transaction’s date instead.  Also Notice that aggregated view fieldnames are automatically prefixed with the aggregation function name. In this case, ‘SumOfAmountMST’.
Above, you can also see the result of MyFirstView by opening in the table browser.

We can use a view as an alternative to a table from X++ or from a data source. If creating a report, we can use the view without having to figure out how to do calculations on your report. One of the few limitations views chave is it cannot be used in relations, such as, delete actions and tables collections.