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

Advertisements

Über erpcoder
Software Engineer and Dynamics AX 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

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: