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.
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:
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:
But this time, of course, I only see the retail customers (Customer group = 30).
I like it.