Use Aggregation Function in Document Data Source

Dynamics AX 2012 supports to publish a query as document data source e.g. to load data in excel. However, document data sources are very limited. For example aggregation like sum() is not supported. Here is an example

• Create a new query and use the CustInvoiceJour as datasource.
• Set the Dynamic Fields property to No, but add a SUM field for the InvoiceAmoutMST
• Append the LogisticsPostalAddress as datasource to the CustInvoiveJour
• Add a relation and use the predefined InvoicePostalAddress_FK relation
• Set the Dynamic Fields property to No, but add the CoutryRegionId and City
• In the query add CountryRegionId and City as Group By Fields

Go to System Administration > Setup > Document Management > Document data sources > Add a query reference for the newly create query.

When you try to load data from this query using Excel Addins, you will face an error message.

When you open the document data source in a browser, your will see that there are no useful entries in the data feed. By default the URL is http://localhost:8101/DynamicsAx/Services/ODataQueryService

However, one simple workaround is to create a view based on the query.

• Create a new view
• Add the previous create query as data source
• Add the SUM field, CountryRegionId and City to the view fields

Open the view. The result should look like this

Finally, create a second view and use the view as data source. Add a query reference to the second query at the document data sources. The data feed now contains valid data.

Now you can load your data using Excel Addins.