Transactional Report Writing for end-users?

  • Comments 2
The Problem
The process of writing transactional reports within Dynamics AX2012 requires a developer and as a developer, I can safely say that it can be a little cumbersome (and flakey) to say the least..! Dynamics, simply doesn’t have all the equivalent querying functionality you get with standard SQL (subqueries, type-casting, common-table-expressions… etc) which means that you end up manually writing (or utilising) code to perform any enhanced funcitonality. This is further hindered by the fact that you cannot incorporate this code as part of the query itself (i.e. X++ method calls as query-display-fields). As a result, the only route left available to you is the RDP (Report Data Provider) route when generating and developing all your end-user-reports. Annoyingly, this makes the process of report-writing a developer pre-requisite for the customer and further makes it impossible for standard-users to generate their own reports.

·         Other ERP Systems (like Oracle Peoplesoft) have utilised easy-to-use transctional report writers for the end-users for many years. I remember (many moons ago) using the DREAM-writer tools (Data Record Extraction and Management) within the One World sofwtare for end-user transactional and reporting analysis. It was a simple and easy tool for end-users and most impotantly it saved on ISV customisation time and money.

The RDP route itself is cumbersome for developers and a simple report can often take days to write simply because you need to define the query, the temporary table, the underlying EDT’s, the data contracts, the report data provider classes, the RDL report-defintion and finally any layout classes for report parameter interdependencies. Believe me, I’ve done this a few times now and the whole thing doesn’t hang together as seamlessly as the promotional videos would have you believe.
Often the RDL definition or the data contract gets cached and you end up running “out-of-date” report versions (leading to the obvious self-insanity checking while testing). The parameters on the RDL seem to lose linkage with the data contracts very easily and most annoyingly the report tries to render before mandatory parameter checking has taken place… WHY!
The whole thing just seems to have been hammered into the product like a misfitting jigsaw piece by Microsoft who have simply stated that you will use SSRS (like it or not). Hopefully, some of these very fundamental issues will be addressed in subsequent releases, however, in the meantime I grow weary and impatient and I’m considering a better (quicker) report writer which is targeted at the end-user and has a simpler design based on my experiences with other ERP systems.
The Solution
At the most basic level, users need to be able to select queries from the AOT. After query selection, they then need to be able to define criteria (and sorting) and then have the ability to select (or drag-and-drop) the query-fields onto a design surface. And obviously, there needs to be other nice things like page setup, titles, calculations, sub-totals (etc)… but these are non-functional items which we can leave to the end.
This all sounds like a lot of work…! But in actual fact, I gave-it-a-go and it and it wasn’t that technically challenging. The end result created a solution where the end-user could generate a transactional report in a fraction of the time it was taking me using the conventional development routes discussed above (5 min compared to 2 days).
The functional concept of the solution is as follows:

1.)   Allow the user to select a query from the AOT (or a pre-determined list of allowed queries).

2.)   Allow the users to set criteria on that query (to sub-select the data).

3.)   Allow the user to determine any sort order for the results.

4.)   Allow the user to select the fields required for report output.

5.)   Allow the user to define any calculations or summarisations required.

6.)   Execute the query and generate a .Net XML dataset.

7.)   Combine the XML dataset with the RDL definition of a standard pre-defined report.

8.)   Render the combined file within a local ReportViewer control (inside the Rich-Client).

There’s way too much to cover here in one article so I’ll be breaking it down into chunks and going through the technical elements of the solution over the next couple of weeks…
  • Interesting approach.  What's your opinion on using SSRS' Report Builder?

  • The "Report Builder" product (distributed free with SQL Server) is a powerful and flexible report writer for "end-users" but you don't normally give direct access to the OLTP database to end-users in this fashion. If you did this then you would bypass the framework (and security). SSRS Report Builder is fine for the OLAP database (cubes) but it has bugs with date handling. For transactional reporting you must use Visual Studio for report development or a solution like the one that I'm proposing that allows end-users to re-use the queries within the AOT.