Notifications
Announcements
No record found.
Hi,
For reporting purpose, I'm long a way to get data of financial dimensions from journal lines inventory form in AX 2012. The main table that I'm using for my report is INVENTTRANS.
*This post is locked for comments
Hi Thai,
Can you tell what exactly you would need? One, more or all dimension values? Also depending on setup per legal entity?
Hi Andre,
I have a need to make a list of stock request that lists all items that warehouse issues to departments (that are specified via financial dimensions). At present, I have most basic info of issue-item transactions (including issued items, issued date, quantity, etc.) by using table INVENTTRANS or INVENJOURNALTRANS; but I don't know how to get what departments who receive these items (i.e. what table(s) stores financial dimensions for these transactions).
Thanks
Thai
It is nothing but default dimension. You will get detal value under below table
\Data Dictionary\Tables\DimensionAttributeValueSet
\Data Dictionary\Tables\DimensionAttributeValueSetItem
You will get defaultDimension as recid in inventJournalTrans which is mapped with DimensionAttributeValueSet table recid.
Detail value department, cost center etc will be stored under DimensionAttributeValueSetItem with relation as recid with DimensionAttributeValueSet table.
Query Exp:
select inventJournalTrans
join DimensionAttributeValueSet where DimensionAttributeValueSet.recid = inventJournalTrans.defaultDiemnsion
join DimensionAttributeValueSetItem where DimensionAttributeValueSetItem .DimensionAttributeValueSet == DimensionAttributeValueSet.recid
Please refer my code. I have hard coded for voucher no and line num.
static void Hari_GetFinancialDim(Args _args) { InventJournalTrans inventJournalTrans; DimensionAttributeValueSetItem setItem; DimensionAttributeValue dimAttrValue; DimensionAttribute dimAttribute; ; while select inventJournalTrans join RecId, DisplayValue from setItem where setItem.DimensionAttributeValueSet == inventJournalTrans.DefaultDimension && inventJournalTrans.LineNum == 4 && inventJournalTrans.Voucher == 'IV-0000003' join dimAttrValue where dimAttrValue.RecId == setItem.DimensionAttributeValue && dimAttrValue.IsDeleted == false join dimAttribute where dimAttrValue.DimensionAttribute == dimAttribute.RecId { info(dimAttribute.Name + ': ' + setItem.DisplayValue); } }
Hi Bhaskar Roy,
Thanks so much for the query. In my case, in table inventJournalTrans beside inventory records there are 02 more records for each item (one for business unit & one for cost centre). So my final SQL statement looks like below:
Select *
From InventJournalTrans IJT
Inner Join DimensionAttributeValueSetItem DAVSI On AVSI.DimensionAttributeValueSet=IJT.DefaultDimension
Inner Join DimensionAttributeValue DAV On DAV.IsDeleted=0 And AV.RecID=DAVSI.DimensionAttributeValue
Inner Join DimensionAttribute DA On DA.RecID=DAV.DIMENSIONATTRIBUTE
Where DAVSI.DISPLAYVALUE Not In ('11', '55', '01')
Order by IJT.JOURNALID, IJT.LINENUM, DAVSI.DimensionAttributeValueSet, DAVSI.DISPLAYVALUE
Note: '11' & '55' are my business units & '01' is my cost centre that setup in my AX.
Thanks,
Hi Hariharan,
Thanks so much for your code. It a great help for me to build dataset for my report.
Hi, how can I get to do the opposite? That is, from the dimensions reach the value of the DefaultDimension field?
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Martin Dráb 4 Most Valuable Professional
Priya_K 4
MyDynamicsNAV 2
