Querying Financial Dimensions from InventTable

(0) Share

Report

Posted on by Peter_R

Hi,

I need to build a SQL query that links Inventory Item ID's (InventTable) with their Financial Dimension values but cannot find the relation between the InventTable and the Financial Dimension tables. The Financial Dimensions are stored in 'DimensionAttribute' table and their values in DimAttributeCustGroup table.

Is this even possible?

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (2)

André Arnaud de Cal... 297,785 Super User 2025 Season 2 on at

Like (0)

Report

RE: Querying Financial Dimensions from InventTable

The dimension flow is as follows:

Customer dimension will be used as default for the sales order header. It would be possible to change the header dimenions before creating lines.

Order line dimensions are inherited from the sales order header. If a specific dimension is empty, it will be defaulted from the product if the product has a value for that dimension. After that you can change the dimensions on line level.

So indeed ask which dimensions should be considered. Probably for the freight invoice you might want to use product defaults as it is a separate process, but the business should confirm.

Was this reply helpful? Yes No
Martin Dráb 235,987 Most Valuable Professional on at

Like (0)

Report

RE: Querying Financial Dimensions from InventTable

If I was you, I would look at invoice lines rather than sales lines. What's important are posted invoices; sales orders can be even deleted after invoicing.

If you're not familiar with financial dimensions, you should learn more about them, because they're very important. In addition to usual documentation, I suggest you look at great blog posts on Global Financial Management team blog.

Please continue asking questions in the forum. Here are many other people who can help you, other users may benefit from the discussion and I don't have capacity to support people on individual basis anyway. You can hire me for some work, though.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Querying Financial Dimensions from InventTable

Agree here. Not sure on how your accounting structures or reporting work but surely you would just take the pre coded sales invoice and accrue the costs based on these dimensions rather than trying to code based on the default product dimensions? This would ensure correct posting when the cost is actualized as the accrual with match the actuals.

Was this reply helpful? Yes No
Peter_R on at

Like (0)

Report

RE: Querying Financial Dimensions from InventTable

Hi Martin,

I will forward your questions to our accountants and the implementation team as the financial dimension concept is new to me (and to them too); we are about to go live with AX soon. Thank you for the suggestion, you were right again; I checked the dimension values in SALESLINE table and they do not match any dimension values in INVENTTABLE.

At least for now I have got the product dimensions from DefaultDimensionView as you suggested and the business requested.

I am good for now and in case I have a follow up question do you mind if I contact you again? If so, should I post the question here or via a private message?

Thanks again!!

Was this reply helpful? Yes No
Verified answer

Martin Dráb 235,987 Most Valuable Professional on at

Like (0)

Report

RE: Querying Financial Dimensions from InventTable

Really? Are you sure that you want to calculate something for sales invoice lines but you'll ignore dimensions there? Do you really want default dimensions of the product, despite the fact that the invoice line can use completely different values?

Was this reply helpful? Yes No
Peter_R on at

Like (0)

Report

RE: Querying Financial Dimensions from InventTable

Thanks for the reply, Martin.

Yes, you are right - it is the Default Financial dimensions; we have 8 of them - sorry for any confusion.

The query should pull sales invoice line details and for each line it should calculate freight costs based on the product weight and weekly shipping rates provided by the trucking company (external data). The resulting 'freight accruals' will be periodically imported into AX. It seems accounting dpt wants to keep a track of these costs using the Default Financial dimensions, as mentioned. They want to see 5 dimensions per invoice line.

Hope this helps?

P.S. I tried the View suggested; I see the dimension codes are in there; I just wasn't able to match them with the Item records as of yet.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 235,987 Most Valuable Professional on at

Like (0)

Report

RE: Querying Financial Dimensions from InventTable

Aha, you mean product's default dimensions, not actual ledger dimensions.

All right, so what you want to do with them? You said you wanted a query, but I'm not sure what the query should do. Do you want only a single dimensions or all of them?

Maybe your requirement can be achieved by a simple join with DefaultDimensionView.

Was this reply helpful? Yes No
Peter_R on at

Like (0)

Report

RE: Querying Financial Dimensions from InventTable

Hi Andre,

thank you for your reply. I can see the links between Items and Dimensions in the Views suggested by you but I am not getting the expected results in 'DisplayValue' column. I was trying to link 'VALUECOMBINATIONRECID' column with either 'PRODUCT' or 'RECID' from InventTable.

Was this reply helpful? Yes No
Suggested answer

Peter_R on at

Like (0)

Report

RE: Querying Financial Dimensions from InventTable

Hi Martin,

Thank you for your reply. Could you please follow up with explanation how it is possible to see the Financial Dimension setup in 'Product information management/Common/Released products>Financial Dimensions' then? AX already knows which Dimensions have been assigned to the Product, so why cannot I export the setup too? See the image. Sorry, I am new to AX and just trying to understand.

Was this reply helpful? Yes No
Martin Dráb 235,987 Most Valuable Professional on at

Like (0)

Report

RE: Querying Financial Dimensions from InventTable

You can't just randomly link items and financial dimensions. You have to define which transactions with items and financial dimensions you want to look at and how you'll work with them.

For example, you can find all customer group used when invoicing a sales line with the given item, or you might want to look at inventory movements, or almost infinite number of other things. You can't implement anything unless you know what logical relation you're looking for.

Was this reply helpful? Yes No