Part 3 : AX SSRS Reporting : Data sources & types

Hi,

In Dynamics AX 2012 there are multiple data sources and data source types supported. In this post we will walk through them.

Data sources

AX 2012 SSRS reports supports two data sources. Data sources options are:

  1. Dynamics AX
  2. Dynamics AX OLAP

Dynamics AX:

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:

  1. Query
  2. Report Data provider
  3. Business Logic
  4. Enum Provider

 

The data source type to data source mapping is listed below:

  Dynamics AX Dynamics AX OLAP
Query Supported Supported
Report Data Provider Supported Not Supported
Enum Provider Supported Not Supported
Business Logic Supported Not Supported

 

Query

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.

Business Logic

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.

Enum Provider

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.

– Girija

 

Disclaimer

Advertisements

Published by

Girija Shankar Beuria

A software developer by profession with 10+ years of experience in the following technologies : Data Warehousing, Business Intelligence applications using SQL Server BI Stack, Product Frameworks and Test automation framework, MOSS , C# .Net, .NET, POWERSHELL, AMO, HTML 5, JavaScript, Reporting Service Web service, Dynamics AX, Dynamics AX 2012 BI Cubes, Dynamics AX 2012 SSRS Reports, SQL Azure, Windows Azure Web Services, ASP .NET MVC 4 Web API, WCF, Entity Framework, WPF, Excel Object Model, Windows 8 Apps, Windows Phone Apps

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s