Complex Query Joins for SSRS reports

This question is not answered

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

All Replies
  • 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

  • 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

  • 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

  • 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.