After some research I understood the behavior of the tables involved in AX 2012 dimension combination when you have a LedgerDimension, the behavior when you have a DefaultDimension is a bit diferent.
So basing in Jacob Hjørnet Sørensen code and some own research, I think I could explain how I solved the issue of getting all the combinations of a dimension, account, department, project...
-----------------------------------------------------------------------------------------------
First, the tables involved:
DimensionAttributeValueSetItem
DimensionAttributeValue
DimensionAttributeLevelValue
DimensionAttributeValueGroup
DimensionAttributeValueGroupCombination
DimensionAttributeValueCombination
-----------------------------------------------------------------------------------------------
The relations between these tables are the following:
DimensionAttributeValueSetItem.DimesionAttributeValue = DimensionAttributeValue.RecId
DimensionAttributeValue.RecId = DimensionAttributeLevelValue.DimensionAttributeValue
DimensionAttributeLevelValue.DimensionAttributeValueGroup = DimensionAttributeValueGroup.RecId
DimensionAttributeValueGroup.RecId = DimensionAttributeValueGroupCombination.DImensionAttributeValueGroup
DimensionAttributeValueGroupCombination.DimensionAttributeValueCombination = DimensionAttributeValueCombination.RecId
-----------------------------------------------------------------------------------------------
Because of the above you can exclude some tables and remake the relations getting this:
DimensionAttributeValueSetItem.DimesionAttributeValue = DimensionAttributeLevelValue.DimensionAttributeValue
DimensionAttributeLevelValue.DimensionAttributeValueGroup = DimensionAttributeValueGroupCombination.DimensionAttributeValueGroup
DimensionAttributeValueGroupCombination.DimensionAttributeValueCombination = DimensionAttributeValueCombination.RecId
-----------------------------------------------------------------------------------------------
Also the LedgerDimension field in a table is a RecId so you can change "DimensionAttributeValueCombination.RecId" with "yourTableWithLedgerDimension.LedgerDimension"
For example if you use generalJournalAccountEntry, the last relation would be like
DimensionAttributeValueGroupCombination.DimensionAttributeValueCombination = generalJournalAccountEntry.LedgerDimension
-----------------------------------------------------------------------------------------------
After understood this, we have only 2 steps left:
-----------------------------------------------------------------------------------------------
- Know where we have to filter: this is probably the "hardest" of the 2 steps, because you have 2 ways to do this. You have to filter using the DisplayValue field wich is in DimensionAttributeValueSetItem and DimensionAttributeLevelValue after few tests I discover that if you use DimensionAttributeValueSetItem you get one record less than using DimensionAttributeValueCombination, and the combination that you won't get is the combination that has only the dimension you are looking for.
(bellow the post is the SQL code that prove that, if someone can test in another environment to confirm that is a general curiosity and it doesn't happen only in my company because of the dimesion setup)
- Now, after discard the DimensionAttributeValueSetItem table, you have only to write your X++ code, below code is a way to get all the invoices from projects that are directly invoiced without a purchase order or purch requisition.
-----------------------------------------------------------------------------------------------
insert_recordset XXX_MyTMPTable( VendAccount, CurrencyCode, InvoiceId, InvoiceDate, TotalPrice, VendName)
select InvoiceAccount, CurrencyCode, InvoiceId, InvoiceDate
from vendInvoiceJour
where !vendInvoiceJour.PurchId
&& vendInvoiceJour.InvoiceDate >= fromDate
&& vendInvoiceJour.InvoiceDate <= toDate
join generalJournalEntry
where generalJournalEntry.SubledgerVoucher == vendInvoiceJour.LedgerVoucher
&& generalJournalEntry.SubledgerVoucherDataAreaId == vendInvoiceJour.dataAreaId
join AccountingCurrencyAmount
from generalJournalAccountEntry
where generalJournalAccountEntry.GeneralJournalEntry == GeneralJournalEntry.RecId
join DimensionAttributeValueGroupCombination
where DimensionAttributeValueGroupCombination.DimensionAttributeValueCombination == generalJournalAccountEntry.LedgerDimension
join dimensionAttributeLevelValue
where dimensionAttributeLevelValue.DisplayValue == _DimensionDisplayValue
&& DimensionAttributeLevelValue.DimensionAttributeValueGroup == DimensionAttributeValueGroupCombination.DimensionAttributeValueGroup
join vendTable
where vendTable.AccountNum == vendInvoiceJour.InvoiceAccount
join Name
from dirPartyTable
where dirPartyTable.RecId == vendTable.Party;
-----------------------------------------------------------------------------------------------
And in the end the SQL code for prove how DimensionAttributeValueSetItem and DimensionAttributeLevelValue show you different records. You can copy and paste it directly in your sql server to test it.
-----------------------------------------------------------------------------------------------
--Use DimensionAttributeValueSetItem:
-----------------------------------------------------------------------------------------------
select DIMENSIONATTRIBUTEVALUECOMBINATION.recid, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE from
DIMENSIONATTRIBUTEVALUESETITEM
inner join DIMENSIONATTRIBUTELEVELVALUE on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE= DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUE
inner join DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION on DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUEGROUP = DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUEGroup
inner join DIMENSIONATTRIBUTEVALUECOMBINATION on DIMENSIONATTRIBUTEVALUECOMBINATION.RECID = DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUECOMBINATION
where DIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE = 'YourDimensionDisplayValue'
group by DIMENSIONATTRIBUTEVALUECOMBINATION.RECID, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE
-----------------------------------------------------------------------------------------------
--Use DimensionAttributeLevelValue
-----------------------------------------------------------------------------------------------
select DIMENSIONATTRIBUTEVALUECOMBINATION.recid, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE from DIMENSIONATTRIBUTELEVELVALUE
inner join DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION
on DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUEGROUP = DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUEGroup
inner join DIMENSIONATTRIBUTEVALUECOMBINATION on DIMENSIONATTRIBUTEVALUECOMBINATION.RECID = DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUECOMBINATION
where DIMENSIONATTRIBUTELEVELVALUE.DISPLAYVALUE = 'YourDimensionDisplayValue'
group by DIMENSIONATTRIBUTEVALUECOMBINATION.RECID, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE
-----------------------------------------------------------------------------------------------
--Show the differences between 2 above queries
-----------------------------------------------------------------------------------------------
select DIMENSIONATTRIBUTEVALUECOMBINATION.recid, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE from DIMENSIONATTRIBUTELEVELVALUE
inner join DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION
on DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUEGROUP = DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUEGroup
inner join DIMENSIONATTRIBUTEVALUECOMBINATION on DIMENSIONATTRIBUTEVALUECOMBINATION.RECID = DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUECOMBINATION
where DIMENSIONATTRIBUTELEVELVALUE.DISPLAYVALUE = 'YourDimensionDisplayValue'
and DIMENSIONATTRIBUTEVALUECOMBINATION.recid not in(select DIMENSIONATTRIBUTEVALUECOMBINATION.recid from
DIMENSIONATTRIBUTEVALUESETITEM
inner join DIMENSIONATTRIBUTELEVELVALUE on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE= DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUE
inner join DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION on DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUEGROUP = DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUEGroup
inner join DIMENSIONATTRIBUTEVALUECOMBINATION on DIMENSIONATTRIBUTEVALUECOMBINATION.RECID = DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUECOMBINATION
where DIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE = 'YourDimensionDisplayValue'
group by DIMENSIONATTRIBUTEVALUECOMBINATION.RECID, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE)
group by DIMENSIONATTRIBUTEVALUECOMBINATION.RECID, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE