A chart provides a graphical representation of data. Displaying data in a chart makes it easy for users to see comparisons, patterns, and trends in the data. In this walkthrough, you will create a report to display customer transaction data in a column chart.
This walkthrough illustrates the following tasks:
  • Defining a query
  • Creating a reporting project
  • Creating a report with a column chart
  • Creating and configuring report parameters and filters
  • Applying layout and style templates
  • Changing the format to a bar or line chart

Prerequisites
To complete this walkthrough, you will need:
  • Microsoft Dynamics AX with sample data
    Note:
    This walkthrough uses the CustTableCustTrans, and CustGroup tables. In order to view data in the report, these tables must be populated with data.Microsoft SQL Server 2008
  • Microsoft Visual Studio 2008
  • Microsoft Dynamics AX Reporting Tools for Microsoft Visual Studio
Defining a Query
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 CustTransactionData.
  4. Right-click the Data Dictionary node, and then click Open New Window.
  5. In the new window, expand the Tables node.
  6. Locate the CustTable table and drag it onto the Data Sources node for the query.
  7. Locate the CustTrans table and drag it onto the Data Sources node located below the CustTable data source.
  8. Right-click the Relations node for the CustTrans data source, and then click New Relation.
  9. Select the node for the relation and specify the following values in the Propertieswindow.
    Property
    Value
    JoinDataSource
    CustTable_1
    Field
    AccountNum
    Related Field
    AccountNum

  10. In the AOT, right-click the Ranges node for the CustTrans data source, and then click New Range.
  11. Select the node for the range, and in the Properties window, select the TransTypefield from the drop-down menu for Field.
  12. Locate the CustGroup table and drag it onto the Data Sources node located below the CustTrans data source.
  13. Right-click the Relations node for the CustGroup data source, and then click New Relation.
  14. Note:
    A report parameter is automatically generated for the range when the query is used in a report dataset and the Dynamic Filters property is set to False.

    Select the node for the relation and specify the following values in the Propertieswindow.
  15. Property
    Value
    JoinDataSource
    CustTable_1
    Field
    CustGroup
    Related Field
    CustGroup

    Save the query.
Creating a Reporting Project
You will start by creating a reporting project in Microsoft Visual Studio. When you create a reporting project, 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 Filemenu, point to New, and then click Project. The New Projectdialog box displays.
  3. In the Project Types pane, click the Visual C# node, and in the Templatespane, click Dynamics AX Reporting Project.
  4. In the Namebox, type SampleChartReport, and in the Location box, type a location.
  5. Click OK.
  6. A reporting project contains a model by default. A model is a file with the .moxl filename 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 Filemenu, point to Add and then click Existing Project from Dynamics AX.
  2. A dialog box displays where you can select a project that currently exists under the Report Library node in the AOT.
  3. In the dialog box, select Shared Library, and then click OK.
  4. In Solution Explorer, right-click the SampleChartReport project, and then click Add Reference.
  5. In the Projectstab, select SharedLibrary, click Add, and then click OK.

Creating a Report with a Column Chart
Before you create a chart, you need to decide what type of chart to create. There are two types of charts: XY chartsand pie or doughnut charts. An XY chart is a column, line, or bar chart. During design, you can switch between related chart types. For example, you can create a column chart and then later change it to a bar or line chart. In this walkthrough, you will begin by creating a column chart. Later in the walkthrough, you will change the design so that the data displays in a bar chart and then in a line chart.

To create a report with a column chart
  1. In Model Editor, right-click the Report1 node, and then click Rename.
  2. Type ColumnChartReportas the name.
  3. Expand the ColumnChartReport node if it is not already expanded.
  4. Right-click the Datasets node, and then click Add Dataset.
  5. Select the node for the dataset.
  6. In the Propertieswindow, specify the following values.
  7. Property
    Value
    Data Source
    Dynamics AX
    Data Source Type
    Query
    Default Layout
    ColumnChart
    Name
    CustomerTransactions
    Dynamic Filters
    False
    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 CustTransactionData query. Select the AmountMST and TransDate fields from the CustTrans table and select the Name field from the CustGroup table. Be sure the All check box is not marked.

  8. In Model Editor, expand the node for the CustomerTransactions dataset if it is not already expanded.
  9. If the Namefield displays as Name1, right-click the node for the field, click Rename, and then type Name.
  10. Note:
    When tables are joined in a query and the tables have fields with the same name, the field names are distinguished by the field name followed by a number. You can rename the nodes that appear for dataset fields in Model Editor.

    Select the AmountMST field, and in the Properties window, set the Aggregate Function property to Sum and the Format String property to Currency.
  11. Drag the CustomerTransactions node onto the Designs node for the report. An auto design called AutoDesign1 is created for the report.
  12. Expand the AutoDesign1 node, expand the node for the chart data region, and then expand the Data node.
  13. Drag the Name field to the Categories node.
  14. Drag the TransDate field to the Series node.
  15. Note:
    The AmountMST field should be the only field that remains below the Data node.

    Select the node for the TransDate series.
  16. In the Propertieswindow, type =Year(Fields!TransDate.Value) for the Label property.
  17. In Model Editor, expand the node for the TransDate series, expand the GroupOnnode, and then select the TransDate node that is located below the GroupOnnode.
  18. In the Propertieswindow, type =Year(Fields!TransDate.Value) for the Expression property.
Creating and Configuring Report Parameters and Filters
Next, you will create and configure several parameters and filters for the report. The report already contains a parameter for the TransType field since a range based on this field was added to the query when it was defined in the AOT and you set the Dynamic Filters property for the data set to False. You will add some additional parameters to allow users to specify the to date and from date. After you have created the parameters, you will create filters to filter the data based on the range specified by the user. The following procedure explains how to create and configure the report parameters and filters.

To create and configure report parameters and filters
  1. In Model Editor, under the ColumnChartReport node, expand the Parametersnode, and then select the CustomerTransactions_TransType parameter.
  2. In the Propertieswindow, type Sales for the Default Value property and select Hiddenfrom the drop-down menu for the Visible property. This will limit the query to return only sales invoice transactions and hide the parameter so that it does not display to the users.
  3. In Model Editor, right-click the Parameters node and then click Add Parameter.
  4. Select the node for the parameter.
  5. In the Propertieswindow, specify the following values.
  6. Property
    Value
    Data Type
    System.DateTime
    Name
    FromDate
    Prompt String
    From date:

    In Model Editor, right-click the Parameters node and then click Add Parameter.
  7. Select the node for the parameter.
  8. In the Propertieswindow, specify the following values.
  9. Property
    Value
    Data Type
    System.DateTime
    Name
    ToDate
    Prompt String
    To date:

    In Model Editor, expand the Designs node, expand the AutoDesign1node, and then expand the node for the chart data region.
  10. Right-click the Filters node, and then click Add Filter.
  11. Select the node for the filter.
  12. In the Propertieswindow, specify the following values.
  13. Property
    Value
    Expression
    =Fields!TransDate.Value
    Name
    FromDateFilter
    Operator
    GreaterThanOrEquals
    Value
    =Parameters!FromDate.Value

    In Model Editor, right-click the Filters node, and then click Add Filter.
  14. Select the node for the filter.
  15. In the Propertieswindow, specify the following values.
Property
Value
Expression
=Fields!TransDate.Value
Name
ToDateFilter
Operator
LessThanOrEquals
Value
=Parameters!ToDate.Value

Applying Layout and Style Templates
Next, you will specify layout and style templates for the report. A layout template defines the general layout and style settings for a report. A style template contains the layout and style settings for a data region that displays in the body of a report. You will apply the predefined templates that are defined in the SharedLibrary project. These templates are the standard templates for Microsoft Dynamics AX reports. The following procedure explains how to apply layout and style templates to the report.

To apply layout and style templates
  1. In Model Editor, select the AutoDesign1 node.
  2. In the Propertieswindow, set the LayoutTemplate 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 chart data region.
  4. In the Propertieswindow, set the Style Template property to ColumnChartStyleTemplate.
  5. Delete the default text for the Title property so that it does not display a title for the data region.
  6. Set the Value Axis Data Scale Min Value property to 5. This will set the starting value on the axis of your report to 5 instead of 0. This is one of many properties that will define the look of your report.
  7. In Model Editor, right-click the AutoDesign1 node, and then click Previewto view the report. Specify dates for the parameters, and then click the Reporttab to view the report.
  8. Close the Preview window.


Changing the Format to a Line or Bar Chart

During design, you can switch between several related chart types. First, you will switch from a column chart to a bar chart. After that, you will switch it to a line chart. The following procedures explain how to switch between chart types.

To switch the format to a bar chart
  1. In Model Editor, select the node for the chart data region.
  2. In the Propertieswindow, set the Chart Type property to Bar.
  3. In Model Editor, right-click the AutoDesign1 node, and then click Preview.

To switch the format to a line chart
  1. In Model Editor, select the node for the chart data region.
  2. In the Propertieswindow, set the Chart Type property to Line.
  3. Click the tab for the Preview window to view the changes