We are writing a web service that needs to query a somewhat complex dataset from MS Dynamics. By "somewhat complex", I mean it needs to query multiple levels of the entity hierarchy. The Dynamics oData implementation limits the depth of oData queries to two (i.e. you can query a parent and a child - but not that child's child).
One way of moving forward would be to walk the entity hierarchy by making thousands of oData calls, but this is not at all appealing (kind of defeats the entire purpose of server side databases).
Another wrinkle is that we need to interact with many different Dynamics systems, none of which will allow us to load a custom .NET plugin. It probably doesn't matter, but we are writing our client-side code in Java, and really do need to use regular REST type calls.
I was thinking that if there were a way to predefine a data report in Dynamics that hits the complex query, that we could then maybe query that report via the REST interface. So far, I haven't found a way to do this, and I thought I'd toss this out to the community to see if anyone has any suggestions.
you can create a Query object in NAV and publish the Query Object as a Webservice If the Query Object is not available in NAV (NAV version < 2013) you can create codeunit, generate the dataset in there and publish the codeunit as a webservice. The third option is if the NAV is running on SQL server you can create a View/TempTable/Stored Procedure in there to create the dataset; or use SQL Analysis Services
Cheers!