Publish Queries As OData Document Sources For Users To Query In Excel
If you want to publish out data to the users so that they can create their own reports, or run their own analysis, there is in a simple and secure way built into Dynamics AX. You can register your queries as Document Data Sources and then they automatically become available through the OData service within Dynamics AX. Additionally, all of the default security that you put in place around the data will be respected by the service because you are using Dynamics AX to create the query.
No more ODBC connections, or even worse… unsecured Access databases.
How To Do It…
Click on the Document Data Sources menu item within the Document Management folder of the Setup group within the Organization Administration area page.
Click on the New button within the menu bar to create a new record.
Assign the Document Data Source a Module and then select the Custom query option from the Data Source Type drop down.
Now select the table or query that you want to publish as an OData Query from the Data Source Name field.
The reason why we used the Custom Query Data Source Type is because it allows us to use the Query editor to refine our results that are returned to the user. If you want to add filters you can do it here, and when you’re done, just click on the OK button.
You can now rename your Data Source Name to make is a little more descriptive, and then to finish the process and make it available to the users, check the Activated checkbox.
Then click the Close button to exit from the form.
How It Works…
One way to access the OData query from Excel is to use PowerQuery. To do that, select the From OData Feed menu item from the From Other Sources menu button within the Get External Data group of the POWER QUERY ribbon bar.
When the OData Feed dialog box is displayed, you will want to type in the URL for Dynamics AX’s OData feed service. It will probably be something similar to this:
http://servername:8101/dynamicsax/services/odataqueryservice/
When you are done, click on the OK button.
Now a Navigator Panel will show up within Excel that lists all of the available tables and queries that you can access through the OData Feed. To use it, all you need to do is click on the feed and click the Load button.
This will load the data from the feed into Power Query. To use it in Excel, just click on the Apply & Close button within the Query group of the Home ribbon bar.
Now you will have all of the data from Dynamics AX being fed directly into your worksheet.
Dynamics AX Tip Of The Day 
Reblogged this on A Tinkerers Notebook.
Thank you Murray for the tip! I started using these with Powerpivot and it works nicely. One question though: I tried to query SalesTable and the created date. For some reason it does notvseem to work. Are the system fields somehow special cases in terms of querying? When it comes to sales order reporting, the sales date is pretty crucial.
The created date should be something that you can report off – I wonder why you can’t see it. A tip though is look for the cube views – I think the sales one is called SalesLineCube – all of the data is consolidated in that view for reporting.
Thank you for the reply!! Eventually I found it by selecting new field and then in the related drop-down list in the properties window. Still, I would have expected it to show on the AOT tree view.
Now I have an another issue. I would naturally like to have the records from SalesLine table. I made a similar query for it, but it does not show up on the list of available queries when checking with Explorer + URL or PowerPivot odata sources. Im wondering, if the following is the reason:
“Each record in an OData query response must have a unique primary key. AOT queries with View data sources are therefore not OData query candidates and will not be displayed in an OData metadata request.”
The unique key of SalesLine table is a combination of several fields as far as Ive understood. Could it really be so that it cannot be used with odata?