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

Dynamics AX Query with SUM aggregation

 

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

Publish Query with aggregation function as document data source

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

Query with aggregation functions fails in Dynamics AX Excel Addin

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

Dynamics AX OData XML feed has no data

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

Create a view for the query

Open the view. The result should look like this

Dynamics AX view displays query data

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.

Dynamics AX OData XML feed with aggregated data

Now you can load your data using Excel Addins.

Dynamics AX Excel Addin with aggregated SUM data

About erpcoder
Azure Cloud Architect and Dynamics 365 enthusiast working in Research & Development for InsideAx

One Response to Use Aggregation Function in Document Data Source

  1. Pingback: Create a Power BI Dashboard for Dynamics AX 2012 Sales | ErpCoder

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: