Thursday, 15 December 2011

Quick walk-through of developing a report in Ax2012

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.

8 comments:

  1. Nice post. Thanks for sharing :)

    ReplyDelete
  2. I wonder how people experience the performance of reports in SSRS. So far, they seem a lot slower than the good old MorphX-reports.

    ReplyDelete
  3. Skaue - That's a fair comment and I agree, at least anecdotally. I also think the general effort and cost to develop/maintain reports using SSRS is a lot higher than MorphX, particularly where there is significant data manipulation. Unfortunately we're stuck with it now though.

    ReplyDelete
  4. Hi Dan,
    As always a very help post on your blog. I am in the middle of creating a similar Sales Margin report as you described. But i need an additional parameter for Summary/Detailed. So we are talking of multiple designs. Any idea how this could be incorporated in the above report. So a user can select a check box and depending on his selection a design will be run. I have seen the Report data provider way of reporting but the whole bunch of classes overwhelm me. The old way works good enough :)

    ReplyDelete
  5. Hi Aman - The simplest approach is really to create two reports, or even two designs within the same report, unless you have a good reason for needing it as a parameter instead. Alternatively you'd probably have to go with the DataProvider approach (I posted an example recently), then have two sections that are either hidden or shown based on the value of that parameter.

    ReplyDelete
  6. Thanks for the reply Dan. Lets say i go with query report and create two designs (instead of 2 reports), how can a user open his chosen design with a single menu item. Creating two reports will be my last option. I would prefer two designs and some way of giving a checkbox parameter to user in query prompt. In X++ reports we could pass EnumParameter and use args. Not sure about AX 2012. Any help is appreciated.

    ReplyDelete
    Replies
    1. This can be done with the help of controller class I suppose. We can set parmReport value on the preRunModified method of the same class.
      EX:
      protected void preRunModifyContract()
      {
      CustAgingReportContract contract = this.parmReportContract().parmRdpContract() as CustAgingReportContract;
      this.parmReportContract().parmReportName(this.getReportName(contract));
      super();
      }




      private str getReportName(CustAgingReportContract _contract)
      {
      str reportNameLocal;

      if (_contract.parmDetailed())
      {
      if (_contract.parmIncludeAmountCur())
      {
      reportNameLocal = ssrsReportStr(CustAgingReport, DesignWithDetailAndWithTransactionCur);
      }
      else
      {
      reportNameLocal = ssrsReportStr(CustAgingReport, DesignWithDetailAndNoTransactionCur);
      }
      }
      else
      {
      if (_contract.parmIncludeAmountCur())
      {
      reportNameLocal = ssrsReportStr(CustAgingReport, DesignWithNoDetailAndWithTransactionCur);
      }
      else
      {
      reportNameLocal = ssrsReportStr(CustAgingReport, DesignWithNoDetailAndNoTransactionCur);
      }
      }

      return reportNameLocal;
      }

      Delete
  7. With the single report, 2 design option, you'd need two menu items (the menu-item lets you choose the report and then the design within that report). Of course it depends on the requirement, but that's the way I'd be pushing for, unless there's a very good reason for needing it as a parameter. I don't want to give you the wrong advice though, because it really does come down to the structure of the report(s) and how they intend to use them.

    ReplyDelete