Category: AX2012

Sql to fetch RemitTo address for a vendor:

select vt.PARTITION, vt.DATAAREAID, vt.ACCOUNTNUM, max(v.Address) RemitToAddress, max(v.LOCATIONNAME) RemitToName
from VENDTABLE vt
join DIRPARTYLOCATION p on p.PARTY = vt.PARTY
join DIRPARTYLOCATIONROLE l on l.PARTYLOCATION = p.RECID
join LOGISTICSLOCATIONROLE r on r.RECID = l.LOCATIONROLE
join DIRPARTYPOSTALADDRESSVIEW v on v.PARTYLOCATION = l.PARTYLOCATION
where r.TYPE = 10–We only want remit to
group by vt.PARTITION, vt.DATAAREAID, vt.ACCOUNTNUM–To ensure only one row per vendor in case there are more than one remit-to addresses

To fetch an AX 2012 dimension into a sql report it is sometimes a good choice to prepare a CTE for each dimension and then use the cte in the main query below:

cteRegion as (
select distinct v.partition, v.valuecombinationrecid, b.value, b.name
from DimAttributeOMBusinessUnit b
left join dimensionattributelevelvalueallview v on
v.dimensionattribute = (select top 1 recid from dimensionattribute WHERE DIMENSIONATTRIBUTE.NAME = ‘Region’)
and v.entityinstance = b.recid)