Create SSRS Report in AX 2012

Create Query Base SSRS report in Dynamics AX 2012:-

Today, let us learn how to quickly create report models and report in Visual studio and add to AOT[Dynamics AX 2012] and see a running report.

Some information:

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.

Also, for this post I am assuming that all report services are configured in the system.

Let us create a query in AOT as a dataset source for our report.

Create a new query by name – “SR_InventTableQuery” and add InventTable as datasource and add ItemId range to it. I will let you know the significance of adding the range as I proceed further..

So, your new query should like below :

Please note: we can use already existing queries which are in AOT for report as datasource, for better understanding I have created new query above.

Lets proceed further. Open visual studio 2010 and lets us create a new Dynamics AX project.

Once visual studio is launched >> 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 let us 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.

Then we need to add the dataset to the newly created 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 [picture explains better than 1000 words]

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

Now , you can select the list of fields and display methods you want to see on your report.
I have selected few fields form the fields and also couple of display methods like site Id and location Id as shown below and click on Ok Button.

wow..There you go… we are done with the datasets part and lets work on the design part real quick now…

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

In my case, when I expand the designs node, i see my fields and the data methods added in the data nodes. we will look in to other nodes in detail later.

Well there are now some important [not mandatory] properties but beautification properties which make your report look good with style. Once you expnad the designs node, you will find InventTableTable with the dataset name. Right click on it and go to properties and 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.

See the screen shot below

wow..we are getting closer. Now we can preview the data by right clicking the autodesign and by chosing option preview as shown below

Note: you can select the company parameter and click on the report tab to view the report. But our main aim is to deploy back this report model to AX.

To deploy the report to AOT, we have a very simple option. Right click on the SR_ReportNewModel from the soultion explorer and select option Add SR_ReportNewModel to AOT as shown below.

We are done with visual stuido development part. Now lets us switch to AX and see whether the report model has been saved to AOT or not. To do so, open your AX client and ogo to AOT >> Visual studio projects >> Dynamics AX Model projects . you should see SR_ReportNewModel project.

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

Now we are left out with final thing, creating menuitem for this report. This can be easily done by following hthe below process.

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

Cool.. we are done and hope you are excited to view the report now. Well you can add this menu item to relevant menu and I hope you knw this process.

Now , lets open the report, Right click on the newly created menu item and select open.[You should see the following as shown below]

Since we have made the company parameter visible- 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.

I have selected ceu as my company and I am leaving ItemId as blank to view all the items in the report. Now lets run the report and see how it renders the data. [Below is the report]

Note: AX uses SysOperationTemplateForm and SysOperationDialog classes for this report integration. we shall look in to details in next posts.