Bojensen Blogs

Microsoft Dynamics Ax, SQL Reporting Services

Dynamics Ax Internals: Quick walk-through of developing a report in Ax2012

by  • 

The following is a quick-and-dirty approach to building a basic SSRS report in Ax2012. This uses an Ax query as the primary datasource, and uses display methods on the table(s) to retrieve additional information.

This is not an approach you should take for all reports, particularly those that require more complex calculations or parameters, but for a lot of requirements this will get the job done fairly quickly.

I’ll be posting another similar walk-through that uses the data-provider approach, which is more flexible but also more time-consuming to develop.

We’ll build a basic report across sales order lines, with additional columns showing a basic margin calculation. The steps are:

Setup the Visual Studio project

Create a new Dynamics Ax Report Model project in Visual Studio, named SalesReportTest1.

Right-click the project within the solution, and add a new report. Name it SalesMarginReport.

Create place-holder method in the table and basic query structure

Create a new Extended Data Type (data-type Real), called SalesMarginAmount.

Add the following display method to SalesLine. For now, it just returns a dummy-value of 88.

public display SalesMarginAmount salesMarginAmount()
{
    return 88;
}

Create a query named SalesMarginReport, with SalesLine as the primary table, and a join to SalesTable. Set the ‘Dynamic’ property on the fields node of each datasource to ‘Yes’ (select all fields). It’s normally better to only select the fields you need, but for simplicity we’ll have the query return everything.

Add ranges for ItemID and SaleStatus (SalesLine), and CustAccount, InvoiceAccount, CustGroup (SalesTable).

Create datasource and table in the report

Add a datasource named SalesMarginDS to the report. Datasource type is Query, and in the ‘Query’ property, pick SalesMarginReport. In the field/method selection screen, pick:

  • SalesLine.SalesID
  • SalesLine.ItemID
  • SalesLine.QtyOrdered
  • SalesLine.SalesStatus
  • SalesLine.SalesPrice
  • SalesLine.LineAmount
  • SalesLine.salesMarginAmount( )
  • SalesTable.DocumentStatus
  • SalesTable.InvoiceAccount
Create a new ‘Auto design’ under the ‘Designs’ node. Set the LayoutTemplate to ReportLayoutStyleTemplate. Set the title to “Sales margin report”.
Create a new Table under the auto-design. Set the Style template to “TableStyleTemplate”, and the Dataset to “SalesMarginDS”. Under the ‘Data’ section of the table, if the fields aren’t already present drag them from the datasource.

Build and deploy

Build the project. Right-click the project and select “Add to AOT”. Go back to Ax and expand Visual Studio projects / Dynamics AX Model projects. If you don’t see your project name there, right-click and ‘Refresh’. All going well you should see a new entry for your reporting project. 
 
If you expand AOT / SSRS Reports / Reports, you should see a corresponding entry for your report definition. Right-click the report and select ‘Deploy’.
 

Under some environments (possibly with missing/incomplete security setup), this may not work from the AOT directly. If you have problems doing it like that, do the following:

  • First off, ensure the SSRS service is running and is accessible.
  • From your Windows desktop, open Administrative tools / Microsoft Dynamics Ax 2012 Management shell. (Right-click and “Run as administrator”)
  • NB this may be a separate step when installing the Ax client/server.
  • In the console, type Publish-AxReport -reportname SalesMarginReport.
  • If you get any errors from that, first off make sure your business connector configuration is pointing to the right environment. 

Create menu-item for the report

Create a new ‘Output’ menu item called SalesMarginReport. Set the caption, and object type to “SSRSReport”, and the object to “SalesMarginReport”. It will automatically select the first design but this can be overridden if you have separate designs within the same report.
At this point you should be able to run the menu item (right-click, open). The all-familiar Ax query prompt will be shown, then the report can be run as normal, giving you:

Better margin calculation

We’ll now change the margin calculation to something a bit more meaningful.  To calculate the margin we’ll take the cost of inventory per piece and multiply it by the order quantity. Modify SalesLine.salesMarginAmount as follows:
public display SalesMarginAmount salesMarginAmount()
{
    // Rough margin calculation - Cost/piece of item
    // multiplied by order quantity.

    InventDimParm       dimParm;
    InventOnhand        onHand;
    Amount              inventoryValue;
    ;
    dimParm.initFromInventDim(this.inventDim());
    onHand = InventOnhand::newItemDim(this.ItemId,this.inventDim(),dimParm);

    inventoryValue = this.QtyOrdered * onHand.costPricePcs();
    return this.LineAmount - inventoryValue;
}

Now re-run the report and you should see the updated margin amount.

It’s sometimes (although not always) the case that SSRS doesn’t pick up the relevant code changes. If that happens restarting the reporting service will do the job, even though it’s not a great solution.

As mentioned this the simplest approach to adding calculated/extended information to a query-based report. For more complicated scenarios you’ll need to use the Data Provider framework, which I’ll provide a follow-up post on soon.

Comments are closed.