Advance Structure in AX 2012

In table DimensionAttributeValueGroupCombination , Ordinal field value 1,2,3,4 is version of advance account structure and account structure. First time it will be 1 and  then it will be 2,3....


Select MA.MAINACCOUNTID,DAVGC.ORDINAL,
DALV.DIMENSIONATTRIBUTEVALUEGROUP ,DALV.DISPLAYVALUE, DALV.ORDINAL,
ROW_NUMBER()
Over (Partition
By
DALV.DimensionAttributevaluegroup
Order
by
DALV.Ordinal
asc) as
'For Sorting'
from
DimensionAttributeValueCombination
DAVC
inner
join
DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION
DAVGC
on
DAVC.RecID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION
inner
join
DIMENSIONATTRIBUTEVALUEGROUP
DAVG
on
DAVG.RECID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION
inner
join
DIMENSIONATTRIBUTELEVELVALUE
DALV
on
DAVG.RECID = DALV.DIMENSIONATTRIBUTEVALUEGROUP
join MAINACCOUNT MA
on MA.RECID = DAVC.MAINACCOUNT

--where DAVG.DIMENSIONHIERARCHY = 22565421207
where DAVGC.ORDINAL not in (1)
Group
by
DALV.DIMENSIONATTRIBUTEVALUEGROUP, DALV.DISPLAYVALUE, DALV.ORDINAL,MA.MAINACCOUNTID,DAVGC.ORDINAL

SQL script for Duplicate recid issues

USE ERPLITE_SIT
DECLARE @MaxRecID BIGINT
DECLARE @NextVal BIGINT

SELECT @MaxRecID = MAX(RECID)
FROM PURCHTABLE

SELECT @NextVal = NEXTVAL
FROM SYSTEMSEQUENCES
INNER JOIN SQLDICTIONARY
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = 'PURCHTABLE'
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID

IF (@NextVal > @MaxRecID)
BEGIN
PRINT 'PURCHTABLE did not need to be updated.'
END
ELSE
BEGIN
PRINT 'Updated PURCHTABLE from ' + CONVERT(VARCHAR(MAX), @NextVal) + '' + 'to' + '' + CONVERT(VARCHAR(MAX), @MaxRecID + 1)

UPDATE SYSTEMSEQUENCES
SET NEXTVAL = @MaxRecID + 1
FROM SYSTEMSEQUENCES
INNER JOIN SQLDICTIONARY
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = 'PURCHTABLE'
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID
END 

Reportitems not showing data with saved as PDF

don't set the group property to hide. set individual text (Field) to hide

Date format SSRS

Microsoft Dynamics AX - [Fix] Improve DocuRef Delete Performance

The problem occurred while deleting a Sales Header. But in reality we have the problem every time we remove DocuRef containing a lot of records.

https://community.dynamics.com/ax/b/dynamicsaxexperience/archive/2016/01/23/slow-docuref-delete-performance

How to get sales/purchase tax/GST/VAT info for report?

For posted Sales Invoice report

1 2	select taxTrans where taxTrans.InventTransId == _custInvoiceTrans.InventTransId;     salesInvoiceTmp.TaxValue = taxTrans.TaxValue;

For posted Free Text Invoice report

1 2 3 4

select taxTrans     where taxTrans.Source == TaxModuleType::FreeTxtInvoice         && taxTrans.SourceTableId == tableNum(CustInvoiceTrans)         && taxTrans.SourceRecId == _custInvoiceTrans.RecId;