Hello Gurus!
I'm trying to use/ add an SQL query into a report form I'm creating to see employee's duration in a specific departments.
Scenario: Employees work in an organization but can switch between departments. I need the duration of each employee in each department. Basically, deducting the EndDate from StartDate but using DATEDIFFerence within the given DataSet.
The query I'm using works in SQL Server 2014 works fine. I want to use that query as a Column Value in the Expression using Visual Studio Design page.
Even though the query works, Dynamics AX 2012 R3, I kept getting:
1. SELECT is invalid...
2. CAST is invalid...(when I remove the SELECT)
3. Without the "=" (equal sign), all the query syntax just displays on the column when I run it.
Does anyone know I maybe able to use my own query instead of selecting Categories and Items from the Expression dialogue?
= Select (cast(datediff(yy, Fields!startDate.Value, Fields!endDate.Value) as varchar(4)), + ' Years, ' +
cast(dateDiff(mm, dateAdd(yy, Fields!dateDiff.Value(yy, Fields!startDate.Value, Fields!endDate.Value),
...
+ ' Days' as EmploymentDuration
From [TheTableName]
