Create Quick first SSRS report in Dynamics AX 2012

Friends,

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.

Happy Dax6ng,
Sreenath

About these ads

27 Responses to “Create Quick first SSRS report in Dynamics AX 2012”

  1. Yogesh Kasat Says:

    Nice article Sreenath… very helpful!

  2. T rama prasad Says:

    Hi, can someone tell me how i can display the customer name in de CustTrans table? Is this possible with adding a field to a table and form or do I need some kind of code to accomplish this?

  3. Ranganath Says:

    Thanks for article…..very helpful

  4. Paul Says:

    Hi! Did you also have to redeply your report model to SSRS (like during the installation of AX 2012) or does this happen automatically when you add the project to AOT?

  5. looneystar Says:

    I have created a precesion design, but want to add 2 boolean parameters. I have added them to the Report parameters, but they do not show up in Ax. Even within the Report manager, those parameters do not show up. Restarting visual studio and opening the report object brings up the same report, so im not sure if this is a deployment issue?

    • looneystar Says:

      I found a you tube video: http://www.youtube.com/watch?v=q1VLKs6gtXg&feature=related and i can deploy it from visual studio instead. This actually solved the issue. So the next thing is what was different to deploy from the AOT

      • Sreenath Reddy Says:

        Hi,
        we can deploy reports to a report server from Microsoft Dynamics AX and from Microsoft Visual Studio

        Microsoft Dynamics AX
        Reports can be deployed individually from a developer workspace in Microsoft Dynamics AX.

        In the AOT, expand the SSRS Reports node, expand the Reports node, right-click the report, and then click Deploy Element.

        The reports are deployed for all the translated languages.

        Microsoft Visual Studio
        Reports can be deployed individually from Visual Studio.

        In Solution Explorer, right-click the reporting project that contains the reports that you want to deploy, and then click Deploy.

        The reports are deployed for the neutral (invariant) language only.

        Microsoft PowerShell
        Used to deploy the default reports that are provided with Microsoft Dynamics AX.

        Sreenath

  6. Parag Says:

    Hey Hi,

    I am working on PurchPurchaseOrder report and I am facing following few issues when working on this.

    1. If I am creating new design in existing SSRS report then it is picking only the previous design, after compilation and AOS restart.
    2. If I am creating new report with RDP(Report Data Provider) just like the std SSRS report(PurchPurchaseOrder) then data is not coming on the report. If in visual studio I am passing parameters(recId) and doing preview then that’s working.

    Please suggest on this if you have any idea ..

    • bubbly Says:

      Hi I am updating PurchPurchaseOrderDP class to bring in additional field to showup on the Purchase Order report. I have updated PurchPurchaseOrderHeader table with those fields as well. In Visual Studio, when I pass in the recId, am able to see the data populated. But while running the report on client, I get an error message that this field is not in the Report Data Provider Table. I have deployed the report and compiled the class. Do I have to compile the whole AOT? Please advise.

  7. ambika Says:

    That was a helpful post Sreenath.I am trying to create a report in VS2010 and then deploy it to the report manager.However when i create a project in VS, I see the repository pointing to a wrong AX instance. Is there way to modify the repository to point it to the correct AOS instance. It is grayed out at this point. I tried changing registry to point to the instance that i need but to of no help. Any help greatly appreciated.

  8. Maks Says:

    Hi Sreenath, what’s up Macho man, do you remember me???

    Nice article Super man.. :)

    Maks

  9. Maks Says:

    Technically this is a super document. Keep up the good work going genius.

  10. Kevin Says:

    Kevin

    Hi .. I too installed Ax 2012 with pre requisite Visual Studio Tools , Business Intelligence Tools. When i open VS 2010, i am not able to start itself..How to get those ax template in Vs 2010 so that i can create my SSRS repot thru Ax2012.. Plz help with detailed steps….Thanks in Advance for ur help…

  11. GowriSankarreddy Says:

    Nice Article Sreenath

  12. Helen Fay Says:

    Great article Sreenath

    How can my users access this new report in AX2012? My users will not have access to the AOT

  13. dawson Says:

    Hi Sreenath,
    Excellent Post !! It really gave a good understanding about the SSRS reports however I am trying edit an existing SSRS report ie CustAccountStatementExt. I need to add a column which will be coming from CustInvoiceTable. Using your method I can create a query to do so… but not able to it in this report CustAccountStatement.
    Will appreciate your help!

  14. Isaac Says:

    Hi screenath,
    I need to open a report using a display menu item for instance in production control, display a “customized” production work order report when the work order is started. Kindly advice.
    Regards,
    Isaac

  15. somantri87 Says:

    Hi sreenath,

    nice article it helpfull..

    regards

  16. social media campaigns Says:

    Hi there, You have done a great job. I’ll definitely digg it and personally suggest to my friends. I’m confident they will be benefited from this web site.


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

Follow

Get every new post delivered to your Inbox.

Join 221 other followers

%d bloggers like this: