Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 270 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
AX report: off by one error with joined datasources
Christian_Severin
Member Posts: 8
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:
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.
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.
0
Answers
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.