create report models and report in Visual studio and add to AOT[Dynamics AX 2012]

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 dataset. 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.

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 expand 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, let’s switch to parameters node in the report. If you expand the parameters node, you will find some parameters. Let’s work on AX_CompanyName parameter. By default it is hidden. Let’s unhide or make it visible it as we want to display the items based on the company [dataareadid] selection by the user.

See the screen shot below

we are getting closer. Now we can preview the data by right clicking the autodesign and by choosing 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 solution explorer and select option Add SR_ReportNewModel to AOT as shown below.

We are done with visual studio 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 Go 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 the below process.

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

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 know 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 on the DataAreaId and since we have added range ItemId to the query – we get two 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]