Using Chart control

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 CustTable, CustTrans, 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 Properties window.

   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 TransType field 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 Properties window.

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 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, and in the Templates pane, click Dynamics AX Reporting Project.
4. In the Name box, 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 File menu, 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 Projects tab, 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 charts and 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 ColumnChartReport as 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 Properties window, 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 Name field 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 Properties window, type =Year(Fields!TransDate.Value) for the Label property.
17. In Model Editor, expand the node for the TransDate series, expand the GroupOn node, and then select the TransDate node that is located below the GroupOn node.
18. In the Properties window, 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 Parameters node, and then select the CustomerTransactions_TransType parameter.
2. In the Properties window, type Sales for the Default Value property and select Hidden from 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 Properties window, 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 Properties window, 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 AutoDesign1 node, 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 Properties window, 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 Properties window, 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 Properties window, 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 Properties window, 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 Preview to view the report. Specify dates for the parameters, and then click the Report tab 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 Properties window, 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 Properties window, set the Chart Type property to Line.
3. Click the tab for the Preview window to view the changes