CODEWITHSUNDEEP
sql-serverreporting-servicesaxaptadynamics-ax-2012-r3
friency
friency

Reputation: 1

ax 2012 - direct SQL querying alternatives

I am using a direct SQL query as a data source of my SSRS report:

SELECT 
    emp.EmployeeNumber, FirstName, LastName, MiddleName
    , amts.AmountCode, amts.amt, amts.AmountType
from EmployeeMaster emp
LEFT OUTER JOIN 
(
    SELECT 'Earning' as AmountType, EmployeeNumber, EarningCode as AmountCode
            , SUM(Amount) AS amt 
            FROM EmployeeEarnings GROUP BY EmployeeNumber, EarningCode
    UNION ALL
    SELECT 'Deduction', EmployeeID, DeductionID
            , SUM(Amount) AS amt FROM EmployeeDeduction GROUP BY EmployeeID, DeductionId
) AS amts
ON emp.EmployeeNumber = amts.EmployeeNumber

This query gathers all the earnings and deductions per employee grouped by employee number, type of earning and type of deduction. I am unsure of whether I could do this in x++ or an AOT query. I am wondering if it is alright to do this, or is there be an alternative to produce a result like this query does? I am using RDP class.

Upvotes: 0

Views: 259

Answers (2)

mazzy
mazzy

Reputation: 1105

Yes, Nested joins are not supported in AX. Yes, you can make a view. And you can use a ResultSet class to get result of the sqlStatement with a direct-SQL-query via SQLconnection.

Some time ago I'd created a wrapper class https://github.com/mazzy-ax/SysResultSet. Comments on Russian only. Sorry. Project to import into ax2012 https://github.com/mazzy-ax/SysResultSet/blob/ax2012/AOT/Classes/Class_SysResultSet.xpo

It simplifies the work with a direct-SQL-query. Try it if you know what you are doing.

And, yes, Your SQL is incorrect as it does not consider dataAreaId and Partition. In additional your SQL query ignore Axapta XDS (record level security) and Axapta security subsystem. An AOT query is more adequate I think.

Upvotes: 0

Jan B. Kjeldsen
Jan B. Kjeldsen

Reputation: 18061

Nested joins are not supported in AX. However you can make a union view of EmployeeEarnings and EmployeeDeduction. Then you can select EmployeeMaster and outer join with the view to get the same result.

Your SQL is incorrect as it does not consider dataAreaId and Partition.

Upvotes: 3

Related Questions