Category: Sql

Fetch RemitTo address

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

AX 2012 dimension sql code snippet

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)

A duplicate attribute key has been found

A fairly common error in SSAS: Analysis Services Execute DDL Task: Errors in the OLAP storage engine: A duplicate attribute key has been found when processing

Reason? The most common one is special characters in a dimension attribute string. The solution is to modify the sql code that loads your data to remove such strings like this:

REPLACE(REPLACE(REPLACE(ColumnName, CHAR(9), ‘’), CHAR(10),’’), CHAR(13),’’)