Document data sources for document templates for Dynamics AX 2012


In the previous blog (Document template libraries for Dynamics AX 2012) we showed how a document template can be created so that Word and Excel documents can be generated from within Dynamics AX ā€“ and the generated document contains data from the selected Dynamics AX record.

You link your document template to Dynamics AX by selecting a Document data source:

So you may have wondered where the document sources are managed and whether they can be customised.

The answer, of course, is yes to both.

Document sources

Go to Organisation administration > Setup > Document management > Document sources:

You’ll see that there are three types of document sources:

‘Service’ document sources can be used to update Dynamics AX via the Microsoft Office Add-in ā€“ they’re a bit too technical for me, so we’ll pass over them for now.

‘Query reference’ is a query that can be used by the Microsoft Office Add-in for reading Dynamics AX data (read only).

‘Custom query’ is a filtered view of a query.

Creating new queries isn’t terribly difficult but a quick glance at the AOT shows you that there are already hundreds of queries defined in Dynamics AX:

To add one as a document source just click the New button:

Select your module from the drop-list.

Choose the Data source type ‘Query reference’ and enter/select the name of a query:

Then enter a description.

Click ‘Activate’ and you’re done.

Open Excel (or Word). Log into Dynamics AX and click “Add data’ > ‘Add data’:

Your new document source shows up. Tick to select, and OK:

The Fields pane shows the fields in the records defined for the query:

In Excel drag them to your worksheet (or double-click on a field):

When you’re done, close the Fields pane, and click ‘Refresh’ to read Dynamics AX:

Pretty neat.

Now, let’s say that you want to make things easy for your users by filtering this data.

Back on the Document data sources form click New again, select a module, and this time select ‘Custom query’ as your Data source type:

Again select/enter your query. This time, as you tab out of the Data source name field a filter query form opens:

You know what to do. Define your filer criteria and click OK:

Finally give your customised query a Description, and Activate it:

Back in Excel:

As before:

But this time, of course, I only see the retail customers (Customer group = 30).

I like it.