Ax 2012 - Queries and OData

Yesterday, I had an assignment to export a data table to Excel.  The current recommendations were to use either a list page or an SSRS report.  I had heard a number of references to OData, but did not know much about it.  It turns out that it was really easy to set up a feed.  Along the way, I learned something nice about AOT Query objects.

After a little reading, I found that any AOT Query object can be exposed as an OData Source.  My only issue was that the data that I needed came from a complex collection of tables (read unrolling dimensions again).  As a result, I decided to create an in-memory table and populate it using a stored procedure from the database.  That part was simple enough (just remember that the database calling method must be marked as server.

In general, I prefer to work with in-memory temp tables.  This is because tempDB is one of the busiest places in SQL Server.  From what I've gathered, every query that is executed against more that 1 table is resolved there.  So, the general advice is, if  you don't have to use tempDB, don't. 

The only issue that I had was how to use a query on using an empty temp table as a data source.  It turns out that AOT Query objects are based on QueryRun.  Literally, the class declaration reads:

public class QueryRun extends ObjectRun

So, to populate the query, all that you need is to call setCursor() on the init method.

public void init()
{
    super();
    this.setCursor(MyInMeomryTempTable::populate());
}


From there, exposing the Query was a simple matter of going to Organization Administration -> Setup -> Document Management and opening Document data sources.  Once there, simple add a new document data source.  The module is arbitrary and is just used for organizing the list of data sources.  Data Source Type needs to be Query Reference.  Then, select the name of the Query Object that you created and check Activated.  Description is optional.

Links: 

https://blogs.msdn.microsoft.com/aif/2011/08/23/odata-query-service/

http://immerhier.com/connect-microsoft-power-bi-to-ax-2012-odata-query-service/

http://www.uxceclipse.com/odata-powerquery-and-microsoft-dynamics-ax-2012-data-sources/

Dynamics 365 - Extending Sales and Purchase Documents

I almost got it right on my first attempt.  I was recently asked to update the PO Report in Dynamics 365.  In the extension model, a report is updated by doing the following:

1. Create table extensions if extra data is required
2. Create a copy of the report you wish to update
3. Create a derived class of the controller that handles the report you are updating.
4. Change the main method to point to the new report.
5. Create a derived class of the Data Provider and override the processReport method to gather any new data required (don't forget to call super() to perform the original queries).
6. Repoint the report's data sources to the derived data provider.
7. Reformat the report as required.
8. Extend all menu items that point to the original controller.  Repoint them to the new controller (object property).
9. Compile the project, deploy the report and perform a synchronization.

In most cases, this is all that would be required to update an existing report.  However, the Purchasing and Sales Documents maintain a reference to the original document under print management.  So, even if you process the correct controller and data provider an you are point to the correct report; Dynamics 365 will return a blank report in the original format.

In order to define the correct default, you need to update the print management setting for the document type.  The process is the same for both Sales and Purchasing

Write a delegate that adds your extended report to the list of available reports for the document type.

public class CPPrintMgtDocTypeHandlersExt
{
    [SubscribesTo(classstr(PrintMgmtDocType), delegatestr(PrintMgmtDocType, getDefaultReportFormatDelegate))]
    public static void getDefaultReportFormatDelegate(PrintMgmtDocumentType _docType, EventHandlerResult _result)
    {
        switch (_docType)
        {
            case PrintMgmtDocumentType::PurchaseOrderRequisition:
                _result.result(ssrsReportStr(CPPurchPurchaseOrder, Report));
                break;
        }
    }

}

In this case, an extended report is added to the Purchase Orders list.

Then set the report type as default for both the original and copy.  For Purchase Orders, go to Accounts Payable -> Setup -> Forms -> Form Setup. 

Then click Print management setup.  Once inside, select your document and right click.  Select New from the menu.  Fill in the other details and open the Report Format drop down.  If the delegate was set up correctly, then the new report format should appear.  Repeat these steps to add a default format for the copy as well.

I found out how do this from: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/custom-designs-business-docs

For Ax 2012: https://blogs.msdn.microsoft.com/axsupport/2012/05/24/dynamics-ax-2012-reporting-how-to-specify-new-design-for-sales-order-confirmation/