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.