SSRS report in Dynamics AX 2012

 

SSRS report in Dynamics AX 2012

SQL Server Reporting Services is the primary reporting platform for Microsoft Dynamics AX. Reporting Services is a server-based reporting platform that includes a complete set of tools to create, manage, and deliver reports, and APIs that enable you to integrate or extend data and report processing in custom applications. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components.

In a Visual Studio reporting project for Microsoft Dynamics AX, you can define a report in a report model. A report consists of a collection of items, such as datasets, parameters, images, and report designs. A model can contain more than one report.

Create a new query by name – “SR_InventTableQuery” and add InventTable as datasource and add ItemId range to it.
we can use already existing queries which are in AOT for report as datasource.

Open visual studio 2010 and lets us create a new Dynamics AX project.
click on file menu >> New project as shown below

Select Microsoft Dynamics AX from the installed templates >> report model and name the model as SR_ReportNewModel as shown below

Now add a new report to the newly created report Model as shown below. Right click on the SR_ReportNewModel from the solution explorer, Add >> Report

Rename the report to SR_InventTable by right click and rename option on the newly added report.

Right click on the datasets node and chose the option New datset. Rename it to InventTable and go to query property and click on the ellipsis (…) button to select the query which we have created as shown below.

It will open with list of Dynamics AX Queries from which we should select our query “SR_InventTableQuery” and click on next button as shown below

Select the list of fields and display methods you want to see on your report.
and click on Ok Button.

Work on the design part.

Select the InventTable dataset and drag and drop on to your designs node as shown below. It will create autodesign for you .

Set the style template to “TableStyleTemplate” as shown below.

On to autodesigns, we also need to set an important property called Layout Template – set it to ReportLayoutStyleTemplate as shown below

Now, lets switch to parameters node in the report. If you expand the parameters node , you will find some parameters. Lets work on AX_CompanyName parameter. By default it is hidden. Lets unhide or make it visible it as we want to display the items based on the company [dataaread id] selection by the user.

Preview the data by right clicking the autodesign and by chosing option preview as shown below

To deploy the report to AOT. Right click on the SR_ReportNewModel from the soultion explorer and select option Add SR_ReportNewModel to AOT as shown below.

Open your AX client and ogo to AOT >> Visual studio projects >> Dynamics AX Model projects .

Also, In AOT >> SSRS Reports >> Reports >> you should see SR_InventTable report.


Go to AOT >> Menu items >> Output >> Right click and Select New Menu item and set the following properties as shown below.

Well you can add this menu item to relevant menu.

Open the report, Right click on the newly created menu item and select open.

we have option of generating the report based onthe dataareaid and since we have added range ItemId to the query – we get twow ranges as shown above.

Below is the report.

AX uses SysOperationTemplateForm and SysOperationDialog classes for this report integration.