Question Status

Unanswered
Martin65 asked a question on 13 Sep 2013 5:00 AM

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

Reply
Tommy Skaue responded on 13 Sep 2013 6:27 AM

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.

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Martin65 responded on 18 Sep 2013 3:18 AM

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

Select *

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

Martin

Reply
Tommy Skaue responded on 18 Sep 2013 3:29 AM

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

www.dynamics101.com/.../developing-ssrs-report-using-query-in-microsoft-dynamics-ax-2012

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Andy Adamak responded on 27 Sep 2013 8:57 AM

Martin,

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.  

Reply