Skip to main content

Notifications

Announcements

🌸 Community Spring Festival 2025 Challenge & Updates🌸
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Microsoft Dynamics Axapta Workshop / ‘Using sub queries in Dynam...

‘Using sub queries in Dynamics Ax views

Fawad Hassan Profile Picture Fawad Hassan 337

I had a chance to implement yearToDate functionality in Dynamics Ax using views and I thought it would be helpful to share the code for that. This article required reading article mentioned in post at the bottom. The sub queries in View requires fetching of aliases that Ax creates during conversion of x++ to sql, before writing ‘transact sql ‘ using strfmt.

Ax code:

DictView dv = new DictView(tableNum(PayrollPaySTMTLinesViewTotalsYTD_TYL));str sReturn;

str PayrollPayStatementPaymentDateAlias = SysComputedColumn::returnField(

tableStr(PayrollPaySTMTLinesViewTotalsYTD_TYL),

identifierStr(PayrollPayStatement),

fieldStr(PayrollPayStatement, PaymentDate));

str PayrollAggregateViewAlias = SysComputedColumn::returnField(

tableStr(PayrollPaySTMTLinesViewTotalsYTD_TYL),

identifierStr(PayrollPayStatementLinesViewTotals_TYL_1),

fieldStr(PayrollPayStatementLinesViewTotals_TYL, Code));

str joinPaystatementTableAlias = substr(PayrollPayStatementPaymentDateAlias, 1, strscan(PayrollPayStatementPaymentDateAlias, ‘.’, 1, 100) –1);

str joinAgrregateViewAlias = substr(PayrollAggregateViewAlias, 1, strscan(PayrollAggregateViewAlias, ‘.’, 1, 100) –1);

sReturn = strFmt(‘(select SUM([SUMOFACCOUNTINGCURRENCYAMOUNT]) from’ +

‘[dbo].[PAYROLLPAYSTATEMENTLINESVIEWTOTALS_TYL] child ‘ +

‘join PAYROLLPAYSTATEMENT childPayStatement ‘ +

‘ON childPayStatement.RecId = child.PAYSTATEMENT ‘ +

//’where childPayStatement.PAYMENTDATE>=DATEFROMPARTS (YEAR(ParentPayStatement.PAYMENTDATE), 01, 01)

‘where childPayStatement.PAYMENTDATE>=DATEFROMPARTS (YEAR(%1), 01, 01) ‘ +

//’AND childPayStatement.PAYMENTDATE <= ParentPayStatement.PAYMENTDATE’

‘AND childPayStatement.PAYMENTDATE <= %1 ‘ +

//’AND childPayStatement.WORKER = ParentPayStatement.Worker

‘AND childPayStatement.WORKER = %2 ‘ +

‘AND child.DATAAREAID = %3’ +

‘AND child.PARTITION = %4’ +

‘AND child.CODE = %5’ +

‘AND child.IsEmployer = %6’ +

‘)’,

joinPaystatementTableAlias + ‘.PAYMENTDATE’,

joinPaystatementTableAlias + ‘.Worker’,

joinAgrregateViewAlias + ‘.DATAAREAID ‘,

joinAgrregateViewAlias + ‘.PARTITION ‘,

joinAgrregateViewAlias + ‘.Code ‘,

joinAgrregateViewAlias + ‘.IsEmployer’ );

return sReturn;

Sql Generated

Cast (((SELECT Sum([sumofaccountingcurrencyamount]) FROM [dbo].[payrollpaystatementlinesviewtotals_tyl] child

JOIN payrollpaystatement childPayStatement

ON childPayStatement.recid = child.paystatement

WHERE childPayStatement.paymentdate >=

Datefromparts (Year(T2.paymentdate),

01, 01)

AND childPayStatement.paymentdate <= T2.paymentdate

AND childPayStatement.worker = T2.worker

AND child.dataareaid = T1.dataareaid

AND child.partition = T1.partition

AND child.code = T1.code

AND child.isemployer = T1.isemployer)) AS

NUMERIC(32, 16)) ) AS

YTDVALUE

FROM payrollpaystatementlinesviewtotals_tyl T1

CROSS JOIN payrollpaystatement T2

WHERE ( T1.paystatement = T2.recid

AND ( T1.dataareaid = T2.dataareaid )

AND ( T1.partition = T2.partition ) )

For going through background using sub queries in Views in dynamics, read the article below

http://community.dynamics.com/ax/b/goshoom/archive/2012/10/11/ax2012-subqueries-in-views.aspx


This was originally posted here.

Comments

*This post is locked for comments