Matrix reports in AX 2012

Today I will demonstrate you that How to create matrix reports in AX 2012.

Define a query

1. In the Microsoft Dynamics AX development workspace, open the AOT.
2. In the AOT, right-click the Queries node, and then click New Query.
3. Right-click the node for the query, click Rename, and then type CustTransactions.
4. Expand the CustTransactions query.
5. In the AOT, right-click the Data Dictionary node, and then click Open New Window.
6. In the new window, expand the Tables node.
7. Locate the CustTable table and drag it onto the Data Sources node for the query.
8. Expand the node for the CustTable_1 data source.
9. Select the Fields node, in the Properties window, set the Dynamic property to Yes.
10. Locate the CustTrans table and drag it onto the Data Sources node located within the CustTable_1 data source.
11. Expand the node for the CustTrans_1 data source.
12. Select the Fields node, in the Properties window, set the Dynamic property to Yes.
13. Locate the CustGroup table and drag it onto the Data Sources node located within the CustTrans_1 data source. This will be three levels down the hierarchy.
14. Expand the node for the CustGroup_1 data source.
15. Select the Fields node, in the Properties window, set the Dynamic property to Yes.
16. Right-click the Relations node for the CustTrans_1 data source, and then click New Relation.
17. Select the node for the relation and specify the following values in the Properties window.

Property	Value
JoinDataSource	CustTable_1
Field	AccountNum
Related Field	AccountNum

18. Expand the node for the CustGroup_1 data source.
19. Right-click the Relations node for the CustGroup data source, and then click New Relation.
20. Select the node for the relation and specify the following values in the Properties window.

Property	Value
JoinDataSource	CustTable_1
Field	CustGroup
Related Field	CustGroup

21. Save the query.

Create a reporting project

1. Open Microsoft Visual Studio.
2. On the File menu, point to New and then click Project. The New Project dialog box is displayed.
3. In the Installed Templates pane, click the Microsoft Dynamics AX node. In the Templates pane, click Report Model.
4. In the Name box, type SampleMatrixReport, and in the Location box, type a location.
5. Click OK.

Create a report with a matrix data region

1. In Solution Explorer, right-click the project, point to Add, and then click Report.
2. In Model Editor, right-click the Report1 node and then click Rename.
3. Type SampleMatrixReport as the name.
4. Right-click the Datasets node for the report, and then click Add Dataset.
5. Select the node for the dataset.
6. In the Properties window, specify the following property values.

Property	Value
Data Source	Dynamics AX
Data Source Type	Query
Default Layout	Matrix
Name	CustTransactions
Query	1.      Click the ellipsis button (…). A dialog box displays where you can select a query that is defined in the AOT and identify the fields that you want to use. 2.      Select the CustTransactions query and then click Next. 3.      Expand the All Fields node for CustTable. 4.      Select the AccountNum field from the CustTable table. 5.      Expand the CustTrans > All Fields node, select the AmountMST and TransDate fields from the CustTrans table. 6.      Expand the CustGroup > All Fields node, select the Name field from the CustGroup table. 7.      Click OK to save the field selection. The Query field is now SELECT CustTable.1.AccountNum,CustTrans.1.TransDate,CustTrans.1.AmountMST,CustGroup.1.Name FROM CustTransactions

7. In Model Editor, select the Datasets > CustTransactions > Fields > AmountMST node.
8. In the Properties window, set the Aggregate Function property to Sum and set the Format String property to Currency.
9. In Model Editor, drag the CustTransactions node onto the Designs node. An auto design named AutoDesign1 is created for the report.
10. Expand the AutoDesign1 node, expand the node for the matrix data region, and then expand the Data node.
11. Drag the TransDate field from the Data node onto the Column Groupings node.
12. Select the TransDate node.
13. In the Properties window, type the expression =Year(Fields!TransDate.Value) for the Label property.
14. In Model Editor, expand the TransDate node, expand the Group on node, and then select the TransDate node that is located below the Group on node.
15. In the Properties window, type the expression =Year(Fields!TransDate.Value) for the Expression property.
16. In Model Editor, drag the Name field from the Data node onto the Row Groupings node.
17. Select the Name node.
18. In the Properties window, set the Display Width property to 1in and set the Display Subtotals property to False.
19. In Model Editor, drag the AccountNum field from the Data node onto the Row Groupings node.
20. Select the AccountNum node.
21. In the Properties window, set the Display Width property to 1in and set the Display Subtotals property to False.
22. In Model Editor, select the AmountMST node that is located below the Data node.
23. In the Properties window, set the Display Width property to 1in, and set the Text Align property to Right.

Apply layout and style templates

1. In Model Editor, select the AutoDesign1 node.
2. In the Properties window, set the Layout Template property to ReportLayoutStyleTemplate. Also, type Customer transactions for the Title property.
3. In Model Editor, expand the AutoDesign1 node, and then select the CustTransactionMatrix node.
4. In the Properties window, set the Style Template property to MatrixStyleTemplate. Also, delete the default text for the Title property so that it does not display a title for the data region.
5. In Model Editor, right-click the AutoDesign1 node, and then click Preview. Click the Report tab to see the report. The report displays a list of customer transactions with customer groups down the left side and years across the top.
6. Close the Preview window.