Reporting in MS Dynamics AX 2012 – SSRS Concepts

Reporting is a critical for any organization because it is the way that helps users to get the visibility of the business. For any business software solution, reporting is an integral part due to its importance. Microsoft Dynamics AX is a powerful ERP business suite that gives the powerful reporting. The reporting should be strong because it is the primary way to gain the business profitability, sales, growth and sustainability. For this MS Dynamics AX features a powerful and an existing technology/framework for reporting known as SSRS; full form is SQL Server Reporting Services. We know that MS Dynamics uses SQL Server and SQL server provides a service SSRS out of Dynamics AX too. So using the existing state of the art technology, Microsoft is giving support for SSRS in its powerful and agile ERP solution too. You provide reporting in your own software solution using SSRS. Read about SSRS at Microsoft Technet.

SQL Server Reporting Services in MS Dynamics AX

Microsoft Dynamics AX gives developers the ability to create and customize reports for the AX client and the Enterprise Portal (EP) client. The SSRS platform includes a complete set of tools that developers use to create, manage,  and deliver reports. By creating reports using SSRS, you can create interactive, graphical, tabular and free form reports from data sources such as relational or multi-dimensional database and XML-based data sources.

Report Execution Process

Report Execution Process – SSRS. Source: Microsoft

1. A user requests a report from a menu item (the menu item contains the link to the report)

2. The reporting server receives the request that came from Microsoft Dynamics AX client. The Report server instance then requests the data from MS Dynamics AX server. The .rdl indicates the data source of the report which could be Dynamics AX query, a report data provider class or a an external data source.

3. The AX server sends the report data back to Report Server

4. The reporting services renders the report and sends the rendered report to the MS Dynamics AX client

5. The report viewer finally displays the report in the MS Dynamics AX client using the report viewer controls

This process is very important to understand before we start developing a report.

Types of Report

Broadly, there are two types of report

1. Production Reports

The production report presents data in a pre-defined way.

2. Adhoc Reports

These reports present data in a way selected by user.

Getting Ready – Tools, and Components for SSRS

The Microsoft Dynamics AX 2012 report development tools offer a model-based approach.

Our old and gold friend – Visual Studio

MS Visual studio 2010 is used to create and modify Dynamics AX SSRS reports. The Visual studio tools provide report designers with ability to create reports using the familiar IDE visual studio while using the powerful and rich reporting features of SSRS. The reports are created, managed, deployed, modified and processed on the report server using the integrated Visual Studio report development tools.

RDL – Report Definition Language

The reports that you create are stored in the Report Definition Language (RDL) format specified by SSRS.

Reporting Tool

The reporting tool provided by SSRS platform consists of a Modelling tool and  Model Editor.

Model Elements for Report

SSRS Model Elements

Control

Controls are used to filter the data that is displayed in a report, connect related reports, and control report presentation

Design

A report design represents the layout of a report. A report can have multiple designs that share datasets and parameters

Auto Design: Report design that is generated automatically based on the report data. Visual Studio will automatically generate the design for the report based on dataset provided to it. You create an auto design report using Model Editor.

Precision Design: A report design that you create by using SQL Server Report Designer. Precision designs are typically used when a report requires a precise layout, as is the case for invoices or bank checks and when you need custom placement of the fields. A precision design is free-form.

Images

It contains the image resources that are used on the SSRS report.

Parameters

It is used to apply filtering data on the report.

Dataset

A report dataset identifies the data that is displayed in a report. The dataset elements contain information that is used to bind to a data source.

The SSRS reporting framework provides 5 types of data connections

1. Microsoft Dynamics AX query: The AOT queries that we create in queries node
2. Report Data providers (RDP): We use RDPs when we cannot get data using the AOT queries and need some logic and get data in a specific manner. To create RDPs, we use Dynamics AX classes
3. SSAS OLAP queries: To get data using pre-aggregated views of MS Dynamics AX
4. Transact-SQL queries: We can access external databases us
5. Internet Services queries:  We use data methods to get data feeds provided by ISPs

SSRS Extensions

The MS Dynamics AX SSRS framework takes advantage of custom extensions supported by SSRS platform. This helps created fully integrated reporting experience following the standards of security access rights and data formatting standards.

Creating SSRS Report in Dynamics AX 2012

The concepts discussed will be enough to get started with creating SSRS Report. This will be discussed in my next post.

Methods of creation of reports

There are 2 ways in which a  report can be created, be it a Auto Design or Precision Design, the ways are 2 and they are:

1. Query Based: In a query based report, the data for the report is fetched using a simple MS Dynamics AX Query. Click here to view How to create SSRS Report (Query-Based).

2. Report Data Provider (RDP): When we have a complex data requirements that needs business logic to manipulate the data to create useful information, we use RDP class. In my next post, I will write about SSRS report creation with RDP (Report Data Provider).