Comparing AX 2012 Reporting with Standard SSRS

I have been a Business Intelligence (BI) consultant for a couple of years, and I have worked with the whole suite of Microsoft BI products:  Reporting Services (SSRS), Integration Services (SSIS) and Analysis Services (SSAS).  Dynamics AX 2012; however, was an alien entity to me.  When I was asked to do reporting in Dynamics AX, I thought that since this is a Microsoft product that uses SSRS and a SQL Server database, this should be a pretty standard task based on my experience.  While I was correct from a front-end design point of view, actually getting the data to the report was quite a different task.

The first noticeable difference is the setup.  AX 2012 uses Visual Studio 2010 as opposed to Business Intelligence Development Studio (BIDS) to setup its reports.  BIDS leverages Visual Studio 2008 to launch and run SSRS and you make all of your connections, data sets and parameters directly in the report designer.  Visual Studio 2010 uses a tree view to manage all your data sets and parameters.  These objects must be added, modified or refreshed in the tree to translate over to the report designer. Changes can be made in report designer like in BIDS but they will not translate back to the tree and the changes will not stick.  BIDS creates an RDL file that can be reopened and edited.  With Dynamics AX 2012, everything is stored in the AOT and brought to the local machine by using temporary folders.

The biggest difference is how to gather the data in your report.  Normally, I would just make a query or stored procedure on my database and then create a data set to connect to that object.  However, AX 2012 does not like that method, even if I do.  The AX 2012 preferred way to grab the data for your report is through either query objects or class objects.  Query objects are a sort of drag and drop tree structure where you drag tables into the tree and relate them using the “relationship” node and filter using the “range” node (this node is where parameters are created).  There are thousands of tables in AX 2012 and they are not quite relational, more quasi-relational.  This means, as someone who had no AX experience, I had a hard time navigating through all the tables to try and create the query object I wanted.  The issue I had, as a BI consultant, was that this method lacks some flexibility.  I wanted to have a pretty complex query with large case statements and aggregates with upwards of nine table joins.  The case statement, as far as I found, was impossible for a query object and the large number of tables would theoretically have made the object very slow (typically query objects shouldn’t exceed 4 or 5 tables). 

Query Object Tree

The AX preferred solution to this is to create AX class objects.  These are X++ coded classes that can basically do anything in AX if you possess the knowhow.  You can write the logic directly in code or even call a stored procedure from a class and have a menu item call a class that calls a report that is filled by another class.  This can be a much more complicated solution than the traditional method. 

I was brought on the project being told that these reports were just simple reports and no code was required.  After some research, I found out that it was still possible to build the data sets using stored procedure.  Naturally, being a BI consultant, I jumped at using the BI friendly solution.  It is much easier to test your data set by querying directly in SQL Server Management Studio SSMS using a query than building a class and deploying, then testing.  Stored procedures are also much faster than a query object or class object could hope to be, especially with complex logic.  Unfortunately, AX doesn’t like this solution as much so there are a couple of workarounds required.  First, you need to create a separate schema to house all of your stored procedures.  This is because if AX is wiped and recreated it will delete all your procedures.  In the report, there is no way to have a stored procedure data set from the “DynamicsAX” connection, so you need to create a report data source that connects to the same database.  The new data source has an option to connect to stored procedures.  Lastly, all query objects automatically filter by the current user’s company.  You must manually write this filter into your stored procedure.  To do this, you have to filter your queries on the “DataAreaId” field and your first two parameters for your procedure must be “@AX_CompanyName” (which corresponds to “DataAreaId”) and “@AX_UserContext” in that order.  The parameters created will be automatically configured to be hidden and filled automatically when the report is launched.  After all these modifications, you should have a very fast report built off stored procedures and a new report and data source will be deployed to both the report server and the AOT.  However, here is the rub, the report’s security is now based on the data source’s security.  It would be very hard to get anything out of it, but that data source technically has full access to the database and normally everything is locked up tight for each user.  The two possible solutions to this is to modify permissions directly on the schema you created because that is where all your procedures are, or always run the data source as a proxy user and keep the user’s permissions on the report level.  On my project, we chose the latter.  You must be aware that security becomes a concern using this method and it is not necessarily best practice.

Designing a report in AX is essentially the same as outside of AX.  You still use your tablix objects, and rectangles and headers and footers.  Getting the data into the report is vastly different.  AX best practices are to use query or class objects.  Query objects can be slow and confined by a lot of rules, whereas the class objects are far more flexible.  Reporting based off stored procedures is still very possible and creates the fastest and most flexible solution; however, you must be aware of the extra objects and security considerations you are adding to your implementation of AX.