Reporting Capabilities of MS Dynamics Ax 2009 – Part 3 (Visual Studio 2008 & SSRS)

This article is from a series of articles describing Dynamics AX reporting capabilities. In this article we will see how developers can create SQL Server Reporting Services (SSRS) reports using Dynamics AX and Visual Studio 2008.

You must have Dynamics AX Reporting tools installed on client from where you want to create and deploy report. You must also have access to the SSRS report server.
To know more about Dynamics AX installation requirement and procedure, follow this link… Microsoft Dynamics Ax 2009.

Before we see how to create Dynamics AX SSRS reports, lets make it clear in our mind that Dynamics AX model-based SSRS reports are actually a superset of standard SSRS reports. It means you have everything you see in standard SSRS plus more advanced reporting features if you are creating SSRS report based on Dynamics AX SSRS approach.

Advantages using AX SSRS Reporting Tool over directly creating SSRS reports in SQL Server:

  • This tool provides a model-based approach for creating reports by providing project templates and modeling tools that are incorporated into the Visual Studio development environment.
  • Reporting projects can be customized, upgraded, and patched using the built-in layering support in Dynamics AX. Reporting projects are added to the AOD in a packaged format in Report Libraries, this makes deployment easier. You can easily move them from development environment to production. These reports live with your AX code base.
  • Default report Data Sources are Dynamics AX (for AX data) and Dynamics AX OLAP (for AX Analysis Services data), BUT it is not limited to this, you can use ANY KIND of data source you want. There are more options to do so then usual SSRS approach. For example; you can access other DBs and stored procedures, Excel, text files, RSS / ATOM feeds or any kind of data source you can think of and display data from these data sources on your report. How about displaying live Currency Exchange Rates along with other AX data on report? :). OR you might want to use your own SQL Stored Procedure for some reason or the other to display data on your report. Options are only limited to your knowledge.
  • Same standard Dynamics AX Security is available while running report including record-level security. NOTE: this feature is available to you, only if you are using default Dynamics AX datasources on report. If you are using your own stored procedures etc then there are some tricks that you can use to enforce AX security.
  • It’s really easy to write business logic in report using C# / VB.Net. This makes it possible for you to have full .Net framework access and utilize the power of .Net in whatever manner you want and this is all re-usable in other reports. It also means you can access Dynamics AX business logic in your report, and sometimes much needed X++ calculated business logic fields. On the other hand, If you are creating SSRS reports directly and not using Dynamics AX SSRS tools then you would have to ‘re-create the wheel’ and duplicate that AX logic through T-SQL or SQL Scalar Functions and manage it if business requirement changes. 🙂
  • Reports can be displayed anywhere and not only limited to the Dynamics AX rich client, Role Centers (Dashboards) and Enterprise Portal.
  • Rapid report development. Using this approach, you can create reports using auto-design feature. This feature automatically creates a report design based on the report data. OR for more precise design you can use SQL Report Designer.
  • Use style templates to give your reports consistent look and feel.
  • Report localization using standard .Net practice.
  • See preview of your reports at design time in Visual Studio.
If you still have reasons to use standard SQL SSRS to build reports for AX and dont want to go with this cool advanced Dynamics AX SSRS approach then in this case I cannot help you guys. Others who are still with me and want to utilize Dynamics AX SSRS approach, continue reading. 🙂
In this article we will see how to create a simple Dynamics AX SSRS report using Visual Studio 2008 Dynamics AX reporting tools. Instead of jumping on AX data to see how it looks in SSRS, lets see how things work in this new AX SSRS model. In this way, you will be able to create reports based on AX or any other data repository in minutes.Create Report

Step 1: Create Report Project in VS

  • Launch VS 2008.
  • File – New – Project. The New Project dialog will open. Under Project Types, select Visual C#  / Dynamics. Under Templates select Dynamics AX Reporting Project. Rename project if you want.
    • New report project is created with one blank report named Report1.moxl. Rename report if you want. This is actually your Report Model. Model is a container for all of your report elements and defines your report definition, with file extension .moxl. To open model editor double click on .moxl file in solution explorer. A model contains various elements that define report. Most importantly you can re-use model on another report model. The model is contained in an assembly named on your project, in our case it would be ‘DynamicsReportsLibrary1.Model.dll’.

Model Elements:

ReportThis is your report definition. A report definition contains a collection of elements such as datasets, parameters, images, and report designs.
Report Data SourceThis is a source of data for your report’s dataset. Default Dynamics AX data source gives you access to the Dynamics AX application data. Again, you can define your own data source to connect to other data repositories. Default data sources are, Dynamics AX and DynamicsAXOLAP (requires SharedLibrary and Components from AOTReport Libraries). Custom data sources can be defined against an existing DB/OLAP.
DatasetA dataset identifies data that is displayed in a report. Every report must have one or more datasets. Once you define dataset, you also need to tell how you want to fetch data. Dynamics AX data source can be of two types, Query (AX Query) or Business Logic (Data Methods). DynamicsAXOLAP data source can execute your MDX query against AX. Custom data sources can be of type TSQL / Stored Procedure / MDX.
Data MethodHere you will write code in C# / VB.Net to retrieve or process your data. Also all other type of data that you can not fetch using your data source, can be retrieved through these methods.
The issue you should be aware of with a data method is that it has to construct a full DataTable for the report. If your datasets are huge, then your DataTable will be huge and it may affect the performance of your reports.
Data RegionA data region is an area within a report that displays data. Data can be displayed in table, list, matrix, or chart formats.
ParameterYou define report parameter here.
FilterRestrict the data that is displayed in a report.
GroupingRecords Grouping.
SortingRecords Sorting.
ImageImage display.
Auto DesignAn auto design is a report design that is automatically generated based on the report data. An auto design is created by dragging and dropping, and selecting elements using Model Editor.
Precision DesignA precision design is a report design that is created using SQL Report Designer. Precision designs are useful when a very precise layout is required.
Layout TemplateA layout template specifies the styles that are applied to the header, footer, and body of a report. One layout template can be applied to many reports.
Style TemplateA style template specifies the styles that are applied to a data region.

Step 2: Create Data Method

  • Create a Data Method that will provide data to our DataSet.
  • In model editor, right click on Data Methods node under Report1 and select ‘Add Data Methods‘. This will add a new data methods names DataMethod1.
  • To write code in this method, double click or right click and select ‘View Code‘. It will open a C# code file for you to edit named ‘Report1.BusinessLogic.cs‘. The logic is saved in an assembly named on your project, in our case it would be ‘DynamicsReportsLibrary1.BusinessLogic.dll‘. So, this can also be reused in other projects as well.
  • Change code as below for your method so that we can return some dummy rows. you can also use this trick if you want to return data from a data source that can not be handled by your report data source, such as RSS feeds:

public partial class Report1
[DataMethod(), AxSessionPermission(SecurityAction.Assert)]
public static DataTable DataMethod1()
//create a table structure as we want to display it on report table
var dt = new DataTable();
dt.Columns.Add(“CustID”, typeof(string));
dt.Columns.Add(“Name”, typeof(string));
dt.Columns.Add(“Age”, typeof(int));

//add data. you can use any source to get your data.
DataRow dr;

//add new row
     dr = dt.NewRow();

//Columns required for data
dr[“CustID”] = “R001”;
dr[“Name”] = “Rahul Sharma”;
dr[“Age”] = 30;

//add another row
dr = dt.NewRow();
dr[“CustID”] = “R002”;
dr[“Name”] = “Rocky Sharma”;
dr[“Age”] = 30;

//return table with records
return dt;

Step 3: Create Dataset
  • Create a dataset for your report. Right click on your Datasets node in model editor and ‘Add Dataset‘. Here you will specify which report data source you gonna use for data. In our case, we are using a DataMethod to return a table with rows. Change Data Source Type property to ‘Business Logic‘ and Query property to ‘DataMethod1‘.

You can see that the Dataset has picked up the fields from the DataTable automatically.

Step 4: Create Design
    • Now there are various ways to create you report design but the simple one is to utilize ‘Auto Design‘ feature. Most of the reports can be built this way.
    • In model editor, drag the dataset created in step 2, to Design node of your report. After this your model should like look this.
  • That’s it, you are done with your AX SSRS report.
Step 5: Preview Report
    • Right click on your ‘AutoDesign1’ and select preview.
  • Design has some validation warning as we are not using any style or template to bind our report design.

This was a very simple report to start with but now you have an idea what you gonna do next and how.

AX SSRS Report with AX Query data source:

If you want to create a report that fetches data from AX Query then use the following steps:

    • Create an AX Query
    • Create a new AX Reports Project and a new report in that project
    • Create a dataset in the report bound to that query
    • Create a design to report to render the dataset
    • Preview the design
    • Deploy the design to SSRS
    • View the report via IE
    • Save the Report project back into AX. Right click on your project in solution explorer and select save to AOD. This process uses your current Ax client configuration to determine where to save your report package and on which layer.
  • Edit the report saved in the AOT. Open AX – AOT/ Report Library/ReportProject and right click and select ‘Edit in Visual Studio‘.

Use non-AX data source:

If you want to fetch data from other DBs or from a stored procedure use this.

    • Right click on your project in solution explorer, and select ‘Add – New Item‘. Select ‘Report Data Source‘.
    • Select provider SQL / OLAP.
    • Specify Connection string.
    • Open your report and create a dataset.
    • Select data source you created above and how you want to use it. Specify if you want to use MDX, T-SQL or Stored Procedure with this data source.
  • Use this on your report. 🙂

That’s it for now. Check this space again for more on AX SSRS Reporting.

Feel free to post your feedback / comments / queries here.

Reporting Capabilities of MS Dynamics Ax 2009 – Part 1 (Ax Report Wizard)
Reporting Capabilities of MS Dynamics Ax 2009 – Part 2 (SSRS Report Builder)
ActiveX Gantt Chart control in Dynamics Ax
How to be productive with SSRS and Dynamics AX 2009: A new approach

If you liked the story and want to see more of this kind of content, please follow our twitter handle @CursorRun or FB page @CursorRun
Share your love
Default image
Rahul Sharma
Rahul is a technology enthusiast, solutions architect, trainer, and blogger, working on various Microsoft and open source solutions with more than 18 years of industry experience. He specially takes interest in designing enterprise applications, cloud integrations, IoT, and other architecture rich business solutions. Rahul is a guest author with us and shares his knowledge through technology blog posts.
Articles: 59


Enter your email address below to subscribe to our website. We will never flood your inbox with unwanted emails & you can always unsubscribe anytime.


  1. Hi!
    So how to create Dynamics Ax SSRS reports throw VS?

  2. Here you go… I have updated this post to include example.

  3. Hi,

    I've just created an AX SSRS Report with AX Query that shows the AccountNum and AmountMST from CustTrans and i would like to add the name of the customer with a data method like u can do in Axapta with a display method. Is it possible?

    Thank you

  4. @Anonymous: First of all consider fetching customer name with your main AX Query itself. Add CustTable to your AX query in AX and join them appropriately.

    Coming to your question, yes you can do the same thing here in Data Method in two ways. First, in AX SSRS you fetch data from DataSets, so modify your DS to contain one more field and handle it in DataMethod making manual calls to your AX Query and another query or AX Class/Table static method for customer name.
    Second approach is, add a column in your design and use ssrs expressions to fetch data through a data method that takes CustId and calls AX static method.

    I hope this helps.

  5. Hello Rahul,
    First of all thank you for answering me.
    I know I can do it easily modifying the Ax query but i'm just emproving my skills and i'm doing this just to learn how to do it that's why i did a very easy example.
    I understand the first solution but not the second one. The problem is i don't know how to take the custid for calling a ax static method.

  6. Hi Rahul!

    We created a SSRS report using "non-AX data source" … I have a problem now with "migration" of this report … First of all this report is deployed on "AX Test" environment … and later all appl folder and SSRS reports will be moved to "AX Work/Production" environment … So, every time I need to change the connection string or make deploy. It's not a good way for me …
    Do you know another way to "identify" the connection string?

    Thank You!
    With Respect, Victor.

  7. @Jordi: Sorry, for getting on this one so late.
    You should create a DataMethod which calls AX static method to give you required result. And then use this DataMethod in your report's control expression.

    A quick example:
    [DataMethod(), AxSessionPermission(SecurityAction.Assert)]
    public static byte[] GetCompanyLogo(string CompanyName)
    AxaptaWrapper axSession = SessionManager.GetSession();
    return (byte[]) axSession.CallStaticClassMethod("FormLetter", "companyLogo", null);


    Then on your control's expression call it with the parameter.


    I hope this helps.

  8. @Victor: I know this is frustrating and currently non-AX report datasource does not support DSN connection strings etc. But we still have one workaround for this situation. Instead of using non-AX report datasource, create a Data Method that returns DataTable. In this DataMethod, write standard C# code to access external datasource using DSN connection string and then return DataTable that contains data from the external source.
    This way on each deployment you will only create a DSN on the SSRS server with the same name that you used in your Data Method.

    I hope this helps.

  9. Wonderful goods from you, man. I’ve understand your stuff previous to and you’re just too excellent.

    I really like what you’ve acquired here, certainly like what you’re saying and the way in which you say it.
    You make it entertaining and you still take care of to keep it smart.

    I can not wait to read much more from you. This is actually a wonderful web

  10. I was able to find good information from your articles.

Leave a Reply

%d bloggers like this: