how can I use DimensionAttributeValueCombination in a view, with dimension attribute name and value

(3) Share

Report

Posted on by CU26111349-0

hi,

I have the following view:

the SC_MainAccountsDim contains dimensions related to each MainAccountId in SC_CategoryMainAccounts
I need to get the RecId of the DimensionAttributeValueCombination for each record in SC_MainAccountsDim(i.e the RecId for the combination of main account id and dimenstion attribute value and name)

the table DimensionAttributeValueCombination has maybe hunderds of records for one combination as follows:

the DimensionAttributeValueCombination dont have fixed fields for dimension attribute name and value, they are dynamically generated as

SystemGeneratedJournalAccount(FieldInt64) and SystemGeneratedJournalAccountValue(FieldString)

So, I cant use range filter when adding the table in the view.

thank you

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (4)

Answers (0)

Martin Dráb 239,029 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Moved from Integration, Dataverse, and general topics forum to Finance | Project Operations, Human Resources, AX, GP, SL forum.

It seems you thought that a main account and a business unit uniquely identify a record in DimensionAttributeValueCombination, but you've already proven that it's not the case. There are many records with the same main account and business unit, but different values of other dimensions. You didn't return them from your query, but you still can see them in DisplayValue. For example, here you can see different values of the third dimension.

Now when you understand the data model, you need to think again about what you want to achieve. If you want to find a single record in DimensionAttributeValueCombination, you must take all dimensions into account, not just a single one. And if you're interested in a single dimension only, you can't find a unique DimensionAttributeValueCombination record.

Was this reply helpful? Yes No
CU26111349-0 18 on at

Like (0)

Report
Copy link

Link copied!

Thank you Martin for your response

Yes I know that. I was asking about how to retrieve all records related with business unit dimension for example.

The DisplayValue field shows the main account - dimensions combinations. I need to retrieve all records from DimensionAttributeValueCombination that (for example) BusinessUnit value = 023 is contained in DisplayValue column.

Is there a way to filter DimensionAttributeValueCombination table in the view to achieve this scenario.

Was this reply helpful? Yes No
Sohaib Cheema 49,677 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

And If you want to understand the SQL and joins for this info, there are many online blogpost, where people have written SQL for this, here are a few examples:

1) https://krishhdax.blogspot.com/2018/10/splitting-dimension-into-dimension.html

2) https://sangeethwiki.blogspot.com/2017/10/get-ledger-default-dimension-all.html

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

Like (1)

Report
Copy link

Link copied!

You may be interested in DimensionAttributeLevelValueView or DimensionAttributeLevelValueAllView.

1 people found this reply helpful.

Was this reply helpful? Yes No