Dynamics Community Forum Thread Details

Cross-company queries where linked table records are in different companies

Hi all

Running AX 2012 R3 CU9. Couldn't find any documentation on this subject

This is my situation:

Our AX environment has two legal entities - I'll call them LEA and LEB for this example.

We often create purchase orders in LEA which automatically create sales orders in LEB. In order to report on this, we've been using an external database connection to our SQL Server - however I'm trying to consolidate this report onto an AX SSRS report.

Created an example cross-company query. I can be in LEA and run an SSRS report which uses this query, and return SalesTable and SalesLine records in LEB.

However, when I try to link with PurchLine (PurchLine.PurchId == SalesLine.PurchOrderFormNum) (PurchLine.LineNumber == SalesLine.LineNum) - it doesn't return any values. To repeat, this link definitely works both in external databases, and in the SQL Server instance on the server running the SQL service for the environment.

When I change the query link to OuterJoin, it still returns all the SalesLine records as expected, but with no PurchLine attached. From this I've taken to mean that it can't see the associated record even though it's there in the other company. I've also tried running this from both companies with the same results.

Is this a known issue, and is there a workaround? Or am I missing something?

Thanks very much in advance for your help

Cheers

Luke

Quick Links