Microsoft Dynamics AX (Archived)

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

(0) Share

Report

Posted on by Pontus Stråhlman

290

Hi,

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

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Alex Kwitny 395 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Pontus Stråhlman 290 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,807 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Pontus Stråhlman 290 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thing change quite a bit in AX2012 :

For AX2012, I believe the Join has to be between SalesLine and InventTransOrigin.

JOIN InventTransOrigin ON SalesLine.InventTransOrigin = InventTransOrigin .InventTransId AND SalesLine.DataAreaId= InventTransOrigin .DataAreaId

Once you have access to InventTransOrigin, you can JOIN onto InventTrans

Source : https://technet.microsoft.com/en-us/library/hh272859.aspx

Was this reply helpful? Yes No