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)

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

Like (0)

Report

What should the relation represent? In other words, what are you trying to achieve?

Was this reply helpful? Yes No
Suggested answer

André Arnaud de Cal... 301,146 Super User 2025 Season 2 on at

Like (0)

Report

Hi Peter,

The data model is complex on the financial dimensions. You can try a view which is available in standard AX. Try one of the views DimensionAttributeLevelValueAllView, DimensionAttributeLevelValueView or DimensionAttributeSetItemView.

Was this reply helpful? Yes No
Peter_R on at

Like (0)

Report

Hi Martin,

the goal is to create a report that will find freight accruals that are stored outside of AX and then upload them into AX via Excel. The accounting controller wants me to link inventory items with 5 financial dimensions. Apparently, these freight accruals need to hit our financial dimensions. Hope that helps?

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

Like (0)

Report

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
Suggested answer

Peter_R on at

Like (0)

Report

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
Peter_R on at

Like (0)

Report

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
Verified answer

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

Like (0)

Report

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

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 237,976 Most Valuable Professional on at

Like (0)

Report

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

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