Walkthrough: Creating a Matrix Report (Visual Studio Reporting Tools for Microsoft Dynamics AX)

Dynamics AX 2009

In this walkthrough, you will create a report to display customer transaction data in a matrix data region. A matrix data region displays data in a two-dimensional grid of columns and rows that intersect at specific data points. When defining a matrix data region, you can specify column and row groupings. The data that appears in the detail cells are aggregates based on the intersections of columns and rows.

This walkthrough illustrates the following tasks:

  • Defining a query

  • Creating a reporting project

  • Creating a report with a matrix data region

To complete this walkthrough, you will need:

  • Microsoft Dynamics AX with sample data

    NoteNote

    This walkthrough uses the CustTable, CustTrans, and CustGroup tables. In order to view data in the report, these tables must be populated with data.


  • Microsoft SQL Server 2005

  • Microsoft Visual Studio 2008

  • Microsoft Dynamics AX Reporting Tools for Microsoft Visual Studio

There are several ways to retrieve data for reports. In this walkthrough, you will use a query that is defined within the Microsoft Dynamics AX development environment. The following procedure explains how to define the query that will be used to retrieve customer transaction data.

To define a query

  1. Open Microsoft Dynamics AX.

  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. 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 data source.

  9. Locate the CustTrans table and drag it onto the Data Sources node located below the CustTable data source.

  10. Expand the node for the CustTrans data source.

  11. Locate the CustGroup table and drag it onto the Data Sources node located below the CustTrans data source.

  12. Right-click the Relations node for the CustTrans data source, and then click New Relation.

  13. 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

  14. Expand the node for the CustGroup data source.

  15. Right-click the Relations node for the CustGroup data source, and then click New Relation.

  16. 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

  17. Save the query.

Next, you will create a reporting project. When you create a reporting project in Microsoft Visual Studio, you can choose from two project templates: Visual Basic and Visual C#. In this walkthrough, you will use the Visual C# template. After you have created a project, you will add another project to your solution so that you can reference several predefined layout and style templates that you will use to define the layout of your reports. These templates are the standard templates for Microsoft Dynamics AX reports.

To 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 displays.

  3. In the Project Types pane, click the Visual C# node. In the Templates pane, click Dynamics AX Reporting Project.

  4. In the Name box, type SampleMatrixReport, and in the Location box, type a location.

  5. Click OK.

    A reporting project contains a model by default. A model is a file with the .moxl file name extension. When a reporting project first opens in Microsoft Visual Studio, the model automatically displays in Model Editor. It contains a default report named Report1.

To reference the project that contains the standard layout and style templates

  1. On the File menu, point to Add and then click Existing Project from Dynamics AX.

    A dialog box displays where you can select a project that currently exists under the Report Library node in the AOT.

  2. In the dialog box, select Shared Library, and then click OK.

  3. In Solution Explorer, right-click the SampleMatrixReport project, and then click Add Reference.

  4. In the Projects tab, select SharedLibrary, click Add, and then click OK.

Next, within your reporting project, you will create a report that displays customer transaction data in a matrix data region. You will define a column grouping based on the TransDate field and two row groupings based on the Name and AccountNum fields. The following procedure explains how to create the report.

To create a report with a matrix data region

  1. In Model Editor, right-click the Report1 node and then click Rename.

  2. Type SampleMatrixReport as the name.

  3. Right-click the Datasets node for the report, and then click Add Dataset.

  4. Select the node for the dataset.

  5. 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

    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. Select the CustTransactions query. Select the AccountNum field from the CustTable table. Select the AmountMST and TransDate fields from the CustTrans table. Select the Name field from the CustGroup table. Be sure the All check box is not marked.

  6. In Model Editor, select the AmountMST node.

  7. In the Properties window, set the Aggregate Function property to Sum and set the Format String property to Currency.

  8. In Model Editor, if the Name field displays as Name1, right-click the node for the field, click Rename, and then type Name.

    NoteNote

    When tables are joined in a query and the tables have fields with the same name, the field names are distinguished by the name followed by a number. You can rename the nodes that appear for dataset fields in Model Editor.


  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.

To 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 node for the matrix data region.

  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.

  6. Close the Preview window.

Community Additions

ADD
Show: