AX report: off by one error with joined datasources

Christian_Severin

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?

Any suggestion is welcome, and thanks in advance.

Christian_Severin

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.