I am new to Dynamics AX and need to write some queries for reporting, The AOT query tool is good enough for some but my back ground is in SQL so i would like to be able to use the complex SQL joins needed for some reports. I cant see how to accomplish this in the AOT query and I dont want to start writing classes in X++ or is this my only option? Any help or advice gratefully received
Could you elaborate on the pseudo query you want to create? there are some limitations when using the Query in AOT making X++ the only viable option.
I have a query that is tracking timesheets - the owner of the time sheet is in a team. this team membership is defined by a start and finish date so any given timesheet keeper may have been a member of a number of teams over time. so the join is against the personal identifier and a date range. The transaction table only contains the transaction date and personal identifier so we have in pseudo SQL
From Tranasaction_Table TT
join Personnel_Table on PT on TT.personid = PT.personid
join Team_Table T2T on TT.personid = T2T.personid AND TT.transdate between T2T.from_date and T2T.to_date
Should be possible to create this in a query. :-)
Maybe you can reorder the datasources, so on top is transactions, then team table with its relations and finally personell table under team table with that single relation. So a three level deep join.
Making queries are pretty easy (msdn.microsoft.com/.../bb314753.aspx). :-)
After the query is done, make the report and use the query as datasource
That should be very doable. As you work on AOT queries, I find it beneficial to create a view using this query to test the data coming back. Much like running the query in SQL as you are building it.