AX report: off by one error with joined datasources

Question

We want to build a report in Microsoft Dynamics AX 2009 to show all employees that worked on a production order.

Into the datasources for this report we drag-n-dropped the
ProdTable (pt) which is inner joined by ProdID and DataAreaID to the
ProdJournalRoute (pjr) which is inner joined by EmplID and DataAreaID to the
EmplTable (et) where we look up the employee's name via the name() method.

This report yields some funny output:

pjr.TransDate pjr.EmplID   et.EmplID    et.name()
2010-07-20    05820
2010-07-20    05820        05820        Doe, John
2010-07-20    05820        05820        Doe, John
2010-07-21    00341        05820        Doe, John
2010-07-21    00007        00341        Snow, Jon
...           ...          ...          ...

(Columns and rows snipped)

See? Somewhere in the join between ProdJournalRoute and EmplTable the EmplID gains a one line offset.

Now I could of course simply copy the name() method from the EmplTable to the ProdJournalRoute table and drop the EmplTable join altogether, but I'm afraid this only postpones the problem: what can I do to get my join to work? Should I use a handcrafted query and use this as datasource for the report?

(PS: could perhaps somebody with the necessary user rights clean up all these
[[[microsoft] dynamics] AX] tags? Thank you!)

Answer 1

Check the relation between the tables ProdJournalRoute and EmplTable.

Set ProdJournalRoute.relations to Yes or add the relations manually.

Answer 2

Got it working.

I'm a little reluctant to admit that the solution was simple: when, out of clues, I rebuild the whole thing from scratch, I added all my ProdJournalRoute fields and EmplTable fields to the EmplTable_Body of the design instead of the ProdJournalRoute_Body like I did the first time around, and that made all the difference.

I still don't quite see how and where a report links the data it displays. I would think that the query should be executed as a whole, joining all the tables involved so that you simply can't get this kind of data discrepancy between tables, but there it is: a datasource <DS> is only refreshed in the <DS>_Body of the design. Using this datasource in the design body of a datasource that is joined further up the query gets weird results: either it is uninitialised, or it shows old data that it got from a join to a prior record.

Thank you again for your thoughts, Mr Kjeldsen.

Answer 3

It seems to me the tables aren't actually joined in the query, but the abstraction level creating the underlying queries are running first a single select on the outmost table, then running a query for the "joined" tables. That might explain why the first row has no data from the EmplTable. My guess is the query to the EmplTable didn't return the data quick enough for the framework. Take a look at the FirstFast property of the datasource and also what it does at MSDN: http://msdn.microsoft.com/en-us/library/aa842737(AX.10).aspx

I might be wrong, though. The only way to really know, is to try snitch the SQL run at the database.