How to: Define a Report Dataset [AX 2012]
Updated: February 1, 2013
Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012
A report dataset identifies data that is displayed in a report. You can use a query, stored procedure, or a data method to retrieve data. The following procedure explains how to define a report dataset.
To define a dataset
In Model Editor, expand the node for the report that you want to work with.
Right-click the Datasets node, and then click Add Dataset.
Select the node for the dataset.
In the Properties window, set the following properties.
The data source for the dataset. To access data from the Microsoft Dynamics AX database, select Dynamics AX.
If you plan to use a data source other than the predefined Microsoft Dynamics AX data source, you must first define the data source in Model Editor before defining the dataset. For more information, see How to: Define a Report Data Source.
Data Source Type
The means of retrieving data from the data source. The options that display in the drop-down menu depend upon what you specify for the Data Source property. For example, if you select Dynamics AX you will have the following options for the Data Source property:
Query to use a query defined in the Application Object Tree (AOT).
Business Logic to use a data method defined in a reporting project.
Report Data Provider to use business logic defined in a Microsoft Dynamics AX X++ class.
AX Enum Provider to use an enum type.
For more information, see Guidance for Choosing the Data Source Type.
The default data region type for the dataset. Specify a value for this property if you plan to use the dataset in an auto design report. For more information about data region types, see Report Data Region Overview.
Set the Dynamic Filters property to True to create dynamic filters on your report. When this value is True, a dataset parameter and report parameter will be created. Both of which will have the same name. Dynamic filters allow the end user of the report to add filters based on any of the fields on the report.
When the value is False, the dataset parameter and report parameter for the default ranges are created. The end user of the report cannot add additional filters. For more information about creating dynamic filters, see Adding Interactive Features to Reports.
When the Dynamic Filter property is set to False, the SrsReportDataContract object will not contain the query contract.
The name of the dataset.
The query string that is used to retrieve the data for the dataset based on the given data source and data source type.
If the Data Source Type property is set to Query, do one of the following:
If you are using the predefined Dynamics AX data source, click the ellipsis button (...) to open the Select a Microsoft Dynamics AX Query dialog box. This will allow you to select a query that is defined in the AOT, and which fields, display methods, and field groups are to be returned by the query. To improve the performance of the report, select only the data that is needed for the report to run.
If you are using a data source other than the predefined Dynamics AX data source, click the ellipsis button. If your data source is a SQL data source, the SQL query builder displays where you can build a Transact SQL (TSQL) query string. If it is an Online Analytical Processing (OLAP) data source, the OLAP query builder displays where you can build a Multidimensional Expression (MDX) query string.
If the Data Source Type property is set to Business Logic, type the name of the data method or click the ellipsis button to display the Select a Data Method window. Only data methods that have a return value of System.Data.DataTable can be used when defining a dataset. Data methods that do not return a System.Data.DataTable will not display in the window. If the data method that you select has parameters and you have not yet specified values for the parameters, you will be prompted to enter values for the parameters. For more information, see Data Method Selector.
If the Data Source Type property is set to Report Data Provider, click the ellipses button (...) to open a dialog box where you can select a class from the AOT.
If the Data Source Type property is set to AX Enum Provider, type the name of the enum or click the Query drop-down.
The drop-down list contains the Microsoft Dynamics AX base and system enums.
If your report uses the predefined Dynamics AX data source and a query that is defined in the AOT in Microsoft Dynamics AX, you must be especially careful when updating the query in the AOT. For example, if you remove a field in the query and the field displays in the report, the report will display an empty column for the field. Whenever you make updates to a query, be sure to consider how those updates may affect your reports. Updates to a query may also require updates to your reports.
If your report uses an OLAP data source and the MDX query has the ability to return a variable number of data columns, your report may show empty columns. This could be due to a parameter that was passed to the query and is not recommended.
After you define a dataset, you can reference the dataset when setting the Dataset property for a data region in an auto design. Alternatively, in Model Editor, you can drag the dataset onto the Designs node. This will create an auto design for the report displaying the data for the dataset. For more information, see How to: Create an Auto Design for a Report.
If you create a precision design report, the dataset will be available in the Report Data window for SQL Report Designer. For more information, see How to: Create a Precision Design for a Report.