You’re offline. This is a read only version of the page.
50
I have been an SSRS developer since SSRS 2005 first shipped. I know my way around SSRS and SQL Server 2000 - 2008 pretty well. There are several impediments to using the full power of T-SQL and SSRS though when developing AX 2009 SSRS reports. I drives me crazy that AX and X++ do not allow UNION queries and aliased fields among other X++ query language limitations. You can't even create a proper view in the database to get around these shortcomings because AX will either error out or overwrite any veiw that was not created within the AX development environment.
I came up with an idea that I thought would be a great workaround for this. I'm curious what other AX 2009 developers think of it...
I've created a dummy database that contains no data, only views that point to my AX 2009 databse. For instance, I can use a view that consists of a series of UNION queries for all the Project Transaction tables in order to get all of my transactions in a single query. I can write any valid T-SQL view, and since it is not in the AX 2009 database, it neither bothers AX, nor gets overwritten with a sync DataDictionary. I use the same SQL account and permissions for this 'Views' database as the AX 2009 database uses. Then I create a simple Microsof SQL Server SSRS DataSource to access it in my AX SSRS reports.
The SSRS reports that use this new DataSource are not AX 2009 SSRS reporting projects. They are simply standard SSRS reporting projects created entirely in Visual Studio. One drawback is that you don't have the AX business rules available, and all AX security is bypassed. But this is similar to how normal SSRS reports are written against other databases anyway.
What do you think?
*This post is locked for comments
I have the same question (0)
I do exactly the same as you have suggested, especially in the AX project area.
The AX project module tables need a rethink by Microsoft as they are extremely hard to report from. For example, the transaction tables should be combined into one table.
Also, the fact that you have to view each type of transaction separately in AX forms is archaic and not user friendly.
Share
Report
All responses (
Answers (
