In Dynamics AX 2012 there are multiple data sources and data source types supported. In this post we will walk through them.
AX 2012 SSRS reports supports two data sources. Data sources options are:
- Dynamics AX
- Dynamics AX OLAP
This data source should be used if data is to be retrieved from Dynamics AX. There are various sub types (data source types) which can be used. In this option the data retrieval mechanism connects to the Dynamics AX instance and retrieves data.
Dynamics AX OLAP:
This data source should be used if data is to be retrieved from Dynamics AX cubes. In this option MDX query can be used to retrieve the data. In this option the data retrieval mechanism connects to the Dynamics AX cubes deployed on a SQL Server Analysis service (SSAS) instance and retrieves data.
Data source types
AX 2012 SSRS reports supports multiple data sources types. Data source types are used to define how the data is accessed in AX SSRS report from AX system. The options available are:
- Report Data provider
- Business Logic
- Enum Provider
The data source type to data source mapping is listed below:
|Dynamics AX||Dynamics AX OLAP|
|Report Data Provider||Supported||Not Supported|
|Enum Provider||Supported||Not Supported|
|Business Logic||Supported||Not Supported|
A Query data source type is used if all the data retrieval logic can be achieved through a query.
In case of data source being Dynamics AX it will be an AX query (AOT query) or in case data source is Dynamics AX OLAP it will be MDX query. Typically this is the most efficient way of retrieving data. In case of AOT queries the tables (used in the AOT query) fields & Display Methods can be used for data retrieval.
In case of data source being Dynamics AX OLAP, a MDX query has to be written to retrieve the data. The MDX query can have parameters as well.
Report Data Provider
A Report Data Provider data source type is used when there is some business logic that you want to add after retrieving the data.
Not the most effective way of retrieving data but if logic has to be run this will be used.
A Business logic is typically used when you want to access a data source other than Dynamics AX / Dynamics AX OLAP. This is also used for drill through to forms.
Not the efficient way to retrieve data. Should be avoided where ever possible.
An Enum provider is used when the requirement is to retrieve the values/label for an AX Enum. This is typically used to populate drop down selection in parameters.
Should be used to populate Enum parameter drop downs.
This MSDN article also mentions some parameters to consider before choosing a data source type.