Menu

Dynamics AX 2012 and SQL Reporting (Rid of X++ nightmare…) Part 2

Before we start going into details of solution we have came up with, we need to understand the current Architecture of BI Reporting in AX 2012. Here is a MSFT link

You can reed through the article about steps to generate a report, but I will try to explain in more technical details couple key points:

Business logic is written in X++. That’s why you have that Query (AOT) on the last section.

SSRS (.rdl – XML) is part of the model

SSRS Server only renders the report and send it back to an AX client. That Customization extension is also responsible for bringing dynamic links and labels.

Basically, each SSRS Server have a set of web end points available link. AX Client has a control – Report Viewer, which is really something very similar to the ReportViwer Control available for Visual Studio. To be able to run a report in the ReportViewer, you will need to set properties of the control: SSRS Server Settings,Report Name, Report Parameters, Renders options etc. After, ReportViewer will make a call to the SSRS web services to get your report rendered and displayed in the ReporViewer.

Exactly the same is happening in AX ReportViewer control. AX makes a big variable (Data Adapter), which have all necessary information about report and report server. Also, before you run a report, AX will show you a nice parameters form. If SSRS server is only responsible to return a complete result to AX, can we make AX to call web services of any SSRS, any report? The answer is yes. So, let’s look closer at different components involved.

AX Parameters Form

I don’t really understand the full picture of why .rdl files become part of the model, as for me it really brings more complication in the development and release process. Changing labels in the report will require a code merge. And if you have complicated release process, users might see changed label in two months time. Now, imagine how users are frustrated, when they ask to change a label in the report and it takes 2 months time to deliver that change 0_O. Anyway, the first big reason why RDL is part of the model is Parameters Form. You will be surprised, but Parameters Form is rendered based on the RDL XML file. Each RDL file has a section – Parameters. AX will read the xml file, parse it and make a form with parameters. At this point AX only knows if parameter is visible, what it’s data type and type (single select, multi select). It doesn’t know if your string multi value parameter is a Customer Number. That’s why you have Contract and UIBuilder in the X++, to bring that extra level of user experience. However, it doesn’t matter how complex your parameter is in AX, it will be always simple data type, which will go to the Reporting Server.

Another reason for keeping RDL files as part of the model is deployment. You can simply deploy reports by running ax cmd. However, I don’t really think its a big advantage. If somebody else, who is reading this post, knows more reasons for RDL to be part of the model, please let me know. I can also add, that there are even some products available, which will allow you to manage visual part of the report outside of AX. Can you imagine, the whole product is just to “move RDL out of AX” to enable quick and simple change to SSRS.

Data Adapter/Contract

As data contract/adapter is just a variable, nothing really stops us to change it before it goes into ReportViwer. What if we change Server, Report Name and Parameters in the contract before it goes into ReportViewer control, wil it work? It will It means that we can create a simple SSRS report with SQL query pointed to any data source (not only AX), deploy the report to any reporting server and before data contract/adapter is passed to the ReportingViewer control in AX, we set our custom settings in it. You will be surprised, but it will work. Just doing this, we will get following benefits:

Reports can be deployed to different reporting servers.

Reports are no longer depend on X++. They can be written in SQL or MDX. improvement in performance. Skillset concerns.

Changes to the reports can be done any time. No release or deployment needed.

What about parameters form? Remember, parameters are coming from the RDL file. which is part of the model. All we need to do is to make system thinks that RDL is still part of the model, but now it can be just stored in the table. We need to provide simple UI for users to be able to edit that XML as filed, not as an object in the system. We also need to make standard classes of reporting in AX to be able to read that XML from field in the table and build the form. Actually, the change is so small, just one extra function in the SRSParse class.

Solution

First, what we would to achieve with our solution:

Better performance

Rapid delivery

Better skill set.

Unique security model for all reports across different system integrated into AX. Simple security model based on user roles.

Reports deployment/release as configuration. No code merge anymore.

Enabling interactive reports (Power BI)

At the same time, we would like to have all available futures of AX. We don’t want to have any trade off. Solution should provide existing functionality + extra. Another thing, we have been collecting some feedback from users and menu items for reports hidden somewhere in the different modules in the trees bring more confusion. Better to have one simple form with all reports, with categorisations and with ability to sort/filter. However, if you want to have a menu item linked to the report, not a problem, framework allows you to do so.

As you can see we have we have two main filters: Streams and Systems. Remember I have mentioned before, that you can run reports from different SSRS servers. Now, users have one place for reports from different systems.

Opening report, will bring standard AX reporting form:

Let’s move into interesting bit: Configuration. How to make new built SSRS reports available in the BI Center Form.

Let’s go through fields:

File Name – is RDL file name, which has been deployed to SSRS server

Report Name – friendly name, which users will see the in BI Center form.

Description – Description of the report

Report Manager URL & Report Server URL – SSRS Server

Stream, System – categories

Power BI – if report is interactive Power BI report (will show in the next post)

You also can see that, for each report you will need to provide RDL file. This is how will know what to display in the Reporting Form

Complex Parameters

Having RDL XML file, AX can only get simple data types, however your string parameter might be a Customer or Financial Dimension. In the framework we also included ability to specify X++ query for the complex parameters. This is a parameters configuration form:

And Query for Branch Financial Dimension:

As you can see, we are able to make multi select, single select parameters + Enum values. Now you even can have multi select enum parameters.

The good thing, you have all awesome feautures of AX and even more + no need to merge/deploy. Once you have completed SSRS report, it can be configured in minutes for users to start using it.

Legal Entities and Language

As now reports are rendered outside of AX, you will need to filter you data sets by user/company/language. Fortunately, AX will pass by default all parameters you need.

For company and language, AX will pass AX_CompanyName and AX_RenderingCulture. After, all you need to do is just to filter your queries by those two parameters.

To sum up, you now have all power of AX controls + no longer depend on X++. You can have a replica DB for reporting and not affecting the performance of live db.

Stay tuned, as I will be showing integration of Power BI report into BI Center in AX.