Walkthrough: Creating a Report Bound to a Dimension Data Source

Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

This walkthrough provides the steps to create a report that uses dimensions. Dimensions are additional classifications defined in a table for financial, product, and cost attributes. You will create a report that displays a dimension string, dimension attributes, and a report parameter based on a dimension. When you run the report, you can filter the report by the dimension.

This walkthrough illustrates the following tasks:

• Defining a query

• Creating a reporting project

• Adding a dataset bound to a dimension data source

• Designing and previewing a report

• Adding a dimension report parameter and filter

Prerequisites

To complete this walkthrough, you will need:

• Microsoft Dynamics AX with sample data

  Note

  This walkthrough uses the LedgerJournalTrans table. To view data in the report, this table must be populated with data.

• Microsoft Visual Studio 2010

• Microsoft Visual Studio tools for Microsoft Dynamics AX

Defining a Query

There are several ways to retrieve data for reports. In this walkthrough, you will use a query that is defined in the AOT within Microsoft Dynamics AX development workspace. The following procedure explains how to define a query that will retrieve ledger data.

To define a query

1. Open Microsoft Dynamics AX development workspace.

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

4. Expand the DimensionReportQuery node.

5. At the top level of the AOT, right-click the Data Dictionary node, and then click Open New Window.

6. In the new AOT window, expand the Tables node.

7. Locate the LedgerJournalTrans table and drag it onto the Data Sources node for the DimensionReportQuery query.

8. Expand the Data Sources node, expand the LedgerJournalTrans data source that you added, right-click Fields, and then click Properties.

9. In the Properties window, set the Dynamic property to Yes.

10. Save the query.

Creating a Reporting Project

In this section, you will create a reporting project in Microsoft Visual Studio. In this walkthrough, you will use the Report Model template.

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 is displayed.

3. In the Installed Templates pane, click Microsoft Dynamics AX. In the Templates pane, click Report Model.

4. In the Name box, type SampleDimensionsReport. In the Location box, type a location.

5. Click OK.

Adding a Dataset Bound to a Dimension Data Source

Now that you have created a reporting project, you are ready to add a dataset to the report design that will bind the report to specific data. In this example, you will bind the report to a query. You will use the DimensionReportQuery query that you created because it has the LedgerJournalTrans table as a data source, and that table has dimension strings and dimension attributes. The following procedure explains how to add a dataset to a report that is bound to a Microsoft Dynamics AX query.

To add a dataset bound to a dimension data source

1. In Solution Explorer, right-click the SampleDimensionReport project, point to Add, and then click Report.

2. In Model Editor, right-click the new report and then click Rename. Type DimensionReport as the name.

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

4. Select the node for the dataset.

5. In the Properties window, specify the following values.

   Property	Value
   Data Source	Dynamics AX
   Data Source Type	Query
   Default Layout	Table
   Dynamic Filters	False When the value is False, the filters that are defined in the report model are static. The end-user of the report cannot add additional filters. Set the Dynamic Filters property to True to create dynamic filters on your report. Dynamic filters allow the end user of the report to add filters based on any of the fields on the report, including dimensions.
   Name	LedgerDimension
   Query	Click the ellipsis button (…). A dialog box displays that you can use to select a query that is defined in the AOT and identify the fields that you want to use. Select the DimensionReportQuery query and then click Next. Under the LedgerJournalTrans node, expand the All Fields node and select JournalNum, Txt, and LedgerDimension_String. LedgerDimension_String is a combination of dimension attributes and it will display as a string on the report. Expand the AX Dimensions node. The AX Dimensions node provides a list of the dimensions that can be accessed for the query. Expand the LedgerDimension node, and then select CostCenter and Department. Click OK.

Design and Preview a Report

Next, you will add an auto design report. You will specify layout and style templates for the report, and then preview the report in Visual Studio. A layout template is used to define the placement of the headers and footers in a report. Style templates are used to adjust the style settings for the data regions that are displayed in a report, such as the size of the text, borders, and background color. Layout and style templates are useful for defining reusable layout and style settings. A template can be applied to many reports to maintain consistent appearance for all of the reports.

The following procedures explain how to add an auto design report, how to apply a layout template and a style template, and how to preview the report.

To add an auto design report

• In Model Editor, select the LedgerDimension dataset node and drag it onto the Designs node. An auto design named AutoDesign1 is created for the report.

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 Ledger Listing for the Title property.

3. In Model Editor, expand the AutoDesign1 node, and then select the node for the LedgerDimensionTable data region.

4. In the Properties window, set the Style Template property to TableStyleTemplate.

To preview the report

1. In Model Editor, select the AutoDesign1 node.

2. From the Model Editor toolbar, click Preview.

3. In the Preview window, select the Report tab to view the report. The report displays the dimension string in the Account column and the dimension attributes in the CostCenter and Department columns.

4. Close the Preview window.

Adding a Dimension Report Parameter and Filter

In this section, you will add a report parameter to specify which Department data to use in the report. You will use the report parameter to define an expression for a report filter that is based on Department. When the report is run, the Department report parameter will display on the parameter form to prompt the end user to select the Department for which to show data.

To add a dimension report parameter

1. Right-click the Parameters node, point to Add, and then click Parameter. A node for the parameter is displayed.

2. Select the node for the parameter.

3. In the Properties window, specify property values for the parameter.

   Property	Description
   Allow Blank	False
   Data Type	String
   Default Value	Provide a default value that is in your data for the Department dimension attribute, such as OU_160.
   Multi Value	False
   Name	DepartmentParameter
   Nullable	False
   Prompt String	Department
   Values	Click the ellipsis button (...). The Select values dialog is displayed. Select the From dataset radio button and set the following properties. Property Value Dataset LedgerDimension Value Field Department Department displays in the drop-down list because it is one of the dimension attributes you selected when you created the LedgerDimension dataset. The Value Field value will be used in the filter. Label Field Department The Label Field value displays to the end user. Click OK.
   Visibility	Visible

To add a filter that is based on a dimension

1. In Model Editor, expand the Designs > AutoDesign1 > LedgerDimensionTable node.

2. Right-click the Filters node, and then click Add Filter. A node for the filter is displayed.

3. Select the node for the filter.

4. In the Properties window, specify the following properties to set the Department filter based on the Department report parameter.

   Property	Value
   Expression	=Fields!Department.Value
   Name	DepartmentFilter
   Operator	Equals
   Value	=Parameters!DepartmentParameter.Value

To preview the report

1. In Model Editor, select the AutoDesign1 node.

2. From the Model Editor toolbar, click Preview.

3. In the preview window, the Department report parameter was added to the Parameters tab. The default value that you set for the Department report parameter is displayed, such as OU_160 or use the drop-down to select a value.

4. Select the Report tab to view the report. The report displays the journal batch number and descriptions for the department you specified.

5. Close the Preview window.

Next you can deploy the report, add the report to the AOT, and access it from a menu item. For more information, see How to: Add Reports to Microsoft Dynamics AX and How to: Create a Menu Item for a Report.

See also

Creating Reports Overview