Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

How to get data of financial dimensions from journal lines inventory form in AX 2012

Posted on by Microsoft Employee

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.

FinancialDimensionInStockRequest.jpg

  • Josan_ec Profile Picture
    Josan_ec 45 on at
    RE: How to get data of financial dimensions from journal lines inventory form in AX 2012

    Hi, how can I get to do the opposite? That is, from the dimensions reach the value of the DefaultDimension field?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to get data of financial dimensions from journal lines inventory form in AX 2012

    Hi Hariharan,

    Thanks so much for your code. It a great help for me to build dataset for my report.

    Thanks

    Thai

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to get data of financial dimensions from journal lines inventory form in AX 2012

    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

    BU_2600_CostCentreInInventJournalTrans.jpg

    Note: '11' & '55' are my business units & '01' is my cost centre that setup in my AX.

    Thanks,

    Thai

  • Verified answer
    Hariharans87 Profile Picture
    Hariharans87 4,971 on at
    RE: How to get data of financial dimensions from journal lines inventory form in AX 2012

    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);
        }
    }


  • Verified answer
    5400 Profile Picture
    5400 7,160 on at
    RE: How to get data of financial dimensions from journal lines inventory form in AX 2012

    Hi 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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to get data of financial dimensions from journal lines inventory form in AX 2012

    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

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 288,420 Super User on at
    RE: How to get data of financial dimensions from journal lines inventory form in AX 2012

    Hi Thai,

    Can you tell what exactly you would need? One, more or all dimension values? Also depending on setup per legal entity?

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,420 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,672 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans