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.
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’.
|Report||This is your report definition. A report definition contains a collection of elements such as datasets, parameters, images, and report designs.|
|Report Data Source||This 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.|
|Dataset||A 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 Method||Here 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 Region||A data region is an area within a report that displays data. Data can be displayed in table, list, matrix, or chart formats.|
|Parameter||You define report parameter here.|
|Filter||Restrict the data that is displayed in a report.|
|Auto Design||An 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 Design||A 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 Template||A 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 Template||A 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
public static DataTable DataMethod1()
//create a table structure as we want to display it on report table
var dt = new DataTable();
//add data. you can use any source to get your data.
//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
- 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.
- 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.
- 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