Building a relationship between SalesLine and InventTrans in an SQL-query

Building a relationship between SalesLine and InventTrans in an SQL-query

This question has suggested answer(s)


I have Dynamics AX 2009 and I'm using PowerPivot as a reporting tool.

I would like to build an OLAP-cube using SalesLine, but the table does not have DateFinancial or DatePhysical -fields in it, which means that I cannot link SalesLine to any useful dates. And without any dates, it is hard to build meaningful reports.

I've tried to use the InventTrans -table, building a relationship with the InventTransID -field. This does not, however, work.

I would be very happy if someone would have an answer on this! I think PowerPivot is a very impressive tool, but if I cannot build this one relationship then it all kind of goes wrong.

Any suggestions?

Thanks in advance!

Pontus Stråhlman


All Replies
  • You could try to use CustInvoiceJour (customer invoices) or CustTrans (customer transactions).  SalesLine.InventTransId == InventTrans.InventTransId so I'm not sure why inventTrans wouldn't work for you.

  • Thanks for an answe AlexanderK, highly appreciated!

    Unfortunately there are multiple counts of InventTransId both in SalesLine and in InventTrans. That is why at least PowerPivot does not build a relationship between SalesLine.InventTransId and InventTrans.InventTransId.

  • Your issue is caused by the fact that you may have several business units ("companies" in AX terminology) in the same database, but with isolated data. Therefore you can have, as in your case, the same transaction ID in multiple companies, while it's still guaranteed to be unique in each particular company. It would make no sense if you were unable to use e.g. some product number just because it's already defined in another company.

    Even if you're not familiar with AX, you can see this principle in the database itself. E.g. if you look to SalesLine table in SQL Server Management Studio, you'll find that it's primary key is composed of DataAreaId and InventTransId. DataAreaId is ID of a company in AX. So just use this composite key.

    To be complete, some tables in AX are shared, i.e. they don't use DataAreaId, and virtual companies can make the mapping between company and DataAreaId more complicated, but I let's keep it simple for now.

    Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

  • Actually Martin, the way AX works is that it associates 1 INVENTTRANSID per SALESLINE, no matter how many ITEMTRANS occasions that SALESLINE has. When we have multiple occasions where we sell one SALESLINE we get 2 or more INVENTTRANSID -lines in ITEMTRANS but only one INVENTTRANSID in SALESLINE. That is why I cannot connect a date directly to SALESLINE - because there can be multiple dates in ITEMTRANS for that SALESLINE's INVENTTRANSID.