Dynamics AX 2009 - Reporting Possibilites

So we have talked about BI in the past few months. Went over several Microsoft tehnologies that make up the Microsoft BI platform. This includes SQL Server, Reporting Services, Analysis Services, Integration Services.

This is all the BI platform from Microsoft. And we now have started to look at how this BI platform fits in with and can be used inside Dynamics AX 2009.

The first dive into this was a pratical use for creating custom SSRS reports for a Dynamics AX 2009 instance, and looking into OLAP use for Dynamics AX 2009. (Link: Dynamics AX 2009 BI: Practical use with SSRS and looking at OLAP)

So with that we saw with Dynamics AX 2009, there is a new Visual Studio 2008, Project Template for Dynamics AX 2009 SSRS custom reports.

With showing this, and coming back around to all that we have covered with this so far, I think we are at a point to where we should look at the higher level of reporting options inside Dynamics AX, and get some higher level pros and cons about them.

So with that said, lets look at the reporting options for a Dynamics AX 2009 instance.

The point of reporting is to gain access to data that lives inside a System and it's database. This is the point of reporting with Dynamics AX 2009. To give users access to the different data they may need to perform functions, processes and help make business decisions.

So the reporting options with Dynamics AX addresses a spectrum of reporting. You can look at this as having Procedural based reporting on one end of the spectrum and Analytical reproting at the other end.

On this spectrum, there is a few different ways that reports can be created that span the above mention spectrum. The following is a high level list of these reporting options.:

Standard MorphX reports
The standard MorphX reports are the base reports that come inside Dynamics AX. Most of these are very procedural based in their nature. They are meant to delivery things like invoice journal data, Sales Order Confirmation, Invoices, etc. They serve a purpose to help fulfill a business process.

These kind of reports have the most flexibility for working with business logic, and Dynamics AX objects. They are also the most difficult to develop and modify at times, and are the older way of reporting inside Dynamics AX. (They have been around since the early Axapta days.)

You use X++, and MorphX to create, modify and work with these reports. End users can make use of custom queries and filter possibilities that can stay with that user for as long as their usage data is kept.

So this offers the most flexibility of any of the reporting options for getting at Data inside Dynamics AX. These are very developer driven reports, and super users would not be creating these.

Though this is mostly procedural based reporting, some analyical reports exists in this form, though not many.

Custom SQL Server Reporting Services (SSRS) reports
This reporting option, though could be done to some degree in earlier versions, is really just now a true offering in Dynamics AX 2009.

This makes use of a easier, Visual Studio driven development model, that .Net developer would find familiar to work with. This process creates report definition files, that are used and connect through the .Net Business Conector to work with Dynamics AX data and business logic.

The business logic can be access through using the AxaptaWrapper .Net class, that comes from making use of the .Net Business Connector and the Dynamics AX .Net Reporting Framework.

This is not as complex to create, makes use of a report designer that is easy to use, drag and drop possibility, and real flexible in design and layout possibilities.

This also executes on a SSRS server, which can offload some of the report generation from the AOS itself.

You don't have as much flexibility with this option as a standard report, for example the custom Qeuries for filtering a report, that are user driven can not be used. The developer of these custom SSRS reports must give access to filtering through the report design.

This option offers the ability to create procedural based or analyical based reports in nature. OLAP cubes, KPI's, etc. can be access and added as sources of information for building reports here.

Report Builder / Ad-Hoc Report Creation
This option first came to exists in DAX 4.0, and has been improved in Dynamcis AX 2009.

This makes use of the perspectives node inside the AOT, to expose report models with to the Report Server. This option is targeted at super users who want to get at report data quickly, and in an ad-hoc nature.

This makes use of the SQL Server Reporting Services - Report Builder tool, and based on your instance and the deployed perspectives, will govern what report models your super users can access to look and report on data.

This is very much a drag and drop ability, and meant to be a design so the super users don't have to know the data model.

This is the most limited of the reporting option, and that understandable as this is Ad-hoc, and not meant to give all access to everything. The biggest limit is access to the X++ calculated fields inside Dynamics AX. Those fields that are not just a single table field, but calculated depending on a number of different variables.

The point of this though is to get super user the ability to gain access to around 80% of the reporting data needed. If more is needed, then they can get with thier IT staff to deliver a custom SSRS report for the full amount of data.

This reporting option, like the one before, can offer Procedural and Analytical in nature reports. Access to Cube and Procedural perspectives allow access to different data, and the different data and relations that might exists.

SQL Server BI Development Studio (BIDS)
This is the out of the box tool that most standard Microsoft BI projects use to create reports with. This is also used to create and manage OLAP cubes and OLAP database, and create and managed SSIS packages and logic.

For the report creation this is direct access to the SQL Server database, and now interaction with Dynamics AX itself. So that is the biggest Con with using this to create reports with. No access to those, sometimes much needed X++ calculated business logic fields.

If you choose this option, and need those fields, you would have to 're-create the wheel' and duplicate that logic through T-SQL or SQL Scalar Functions.

This can be very Procedural or Analytical, depending on what your doing with BIDS. Also, BIDS is used, even if not for report creation, to manage a Dynamics AX 2009 instance OLAP cubes. To look at them, modify them, trouble shoot them, etc.


Well that's all for now. I think this is a good start to the reporting options inside Dynamics AX 20009 instance. Moving forward we will dive deeper into looking at each of these, getting some screent shots and real world examples.

See you soon!





"Visit the Dynamics AX Community Page today!"