CODEWITHSUNDEEP
axaptadynamics-ax-2012dynamics-ax-2012-r2
user3289427
user3289427

Reputation: 11

AX 2012 R2 outerjoin in query

I have created a query in AOT with SalesLine as the Main Datasource and CustInvoiceTrans as DataSource under SalesLine.I have given OuterJoin as Join Mode. Now I am trying to develop SSRS Report as follows:

SalesId ItemId OrderedQty RemainingQty InvoicedQty InvoiceId InvoiceDate

My Requirement is to display All SalesOrders in the given Range and also their Respective InvoiceIds and Dates if Invoiced and if not Invoiced their recpective InvoiceIds and Dates as Null.

Kindly help me out in cracking the challenge.

Upvotes: 1

Views: 1521

Answers (2)

lurksponge
lurksponge

Reputation: 128

In addition to the recommendations above, looking at the relations on the CustInvoiceTrans table in the AOT should show you a link between SalesLine on the InventTransId field. With this, it's a pretty simple query:

While select SalesLine where SalesLine.ItemId = 'AnItemId' outer join CustInvoiceTrans where CustInvoiceTrans.InventTransId ==
SalesLine.InventTransId { //build your report data source here }

Sorry for the formatting, typing this from my phone. The above example will filter the SalesLine table for all records containing the item specified. You'll probably need to refine the logic to get what you need, but it's a start.

Upvotes: 2

Jan B. Kjeldsen
Jan B. Kjeldsen

Reputation: 18061

It seems you need some basic reporting skills, but lets do some googling first to help you out:

If you are more of a visual guy there is a youtube channel to keep you going for hours.

Upvotes: 1

Related Questions