Sunday, 18 November 2012

Code to retrieve dimension value

The following code snippet will return a specific financial dimension value attached to a record. It can be used for example to retrieve the cost center attached to a customer.

DimensionValue getDimensionValue(RefRecID dimensionSetRecID,Name attributeName) 
    DimensionAttributeValueSet      dimAttrValueSet;
    DimensionAttributeValueSetItem  dimAttrValueSetItem;
    DimensionAttributeValue         dimAttrValue;
    DimensionAttribute              dimAttribute;
    dimAttrValueSet = DimensionAttributeValueSet::find(dimensionSetRecID);
    select dimAttrValueSetItem
        where   dimAttrValueSetItem.DimensionAttributeValueSet      == dimAttrValueSet.RecId
    join dimAttrValue    
        where   dimAttrValue.RecId                                  == dimAttrValueSetItem.DimensionAttributeValue
    join dimAttribute        
        where   dimAttribute.RecId                                  == dimAttrValue.DimensionAttribute
        &&      dimAttribute.Name                                   == attributeName;
    return dimAttrValue.getValue();        

And to retrieve the default cost center attached to a customer:


This code shows the general table relations, but you're better off using the DimensionStorage class/API to read and update individual dimension values. A good example can be seen in the responses to this StackOverflow topic:

The following code shows how to check all customers with a specific cost center value:

static void ScanRecordsByDimensionValue(Args _args)

    // Check all customers that have a specific value for cost centre.    
    Name                            attrName = 'CostCenter';
    DimensionValue                  dimValue = 'OU_3566';
    CustTable                       custTable;

    DimensionAttributeValueSet      dimAttrValueSet;
    DimensionAttributeValueSetItem  dimAttrValueSetItem;
    DimensionAttributeValue         dimAttrValue;
    DimensionAttribute              dimAttribute;

    dimAttribute    = DimensionAttribute::findByName('CostCenter');
    dimAttrValueSet = DimensionAttributeValueSet::find(custTable.DefaultDimension);

    while select custTable
    join dimAttrValueSetItem
        where   dimAttrValueSetItem.DimensionAttributeValueSet      == custTable.DefaultDimension
        &&      dimAttrValueSetItem.DisplayValue                    == dimValue
    join dimAttrValue
        where   dimAttrValue.RecId                                  == dimAttrValueSetItem.DimensionAttributeValue
        &&      dimAttrValue.DimensionAttribute                     == dimAttribute.RecId


Sunday, 11 November 2012

SSRS Report example using Data Provider framework

This post will walk through a simple but relatively real-world report example in Ax 2012. In a previous post I covered how to build a report based on an Ax query. That's definitely simpler and less code-intensive, but isn't an option when you need more complicated data aggregation or filtering.

The things that I'll touch on include:
  • Creating a Data Provider and supporting objects in X++
  • Using a report contract class to add extended parameters
  • Creating the report in Visual Studio
  • Adding basic layout and grouping
  • Deploying the report and running it from within Ax

We'll work on the following requirement:
Create a report that shows the balance, last invoice number, and last invoice date of all vendors. The report should be grouped by vendor group, and each vendor group should start on a new page. The user should be able to filter by vendor account and vendor group. They should also have an option to specify the 'to date' for vendor transactions, and a flag to exclude vendors that have had no transactions registered against them.

This is something you'd reasonably expect to come across during an implementation, giving us something that looks like:

First, define a temporary table that will act as a container for the report data.

This contains the field mentioned in the spec, as well as a few others that make sense. Note that we include the vendor group ID and name that we intend to use for grouping / page breaks. We also need the query that defines the primary structure of the report.

There's nothing complicated about it - We just add VendTable as the primary table, and join to the vendor group in case the user wants to filter by group name. Note that for this example we have not defined any sorting, grouping, or aggregation in the query itself, as we'll do that in the report.

VendBalanceReportDP (DataProvider class)

First, the class declaration. Key points here are that we extend SRSReportDataProviderBase, and we make use of two attributes:
  • SRSReportQueryAttribute. This defines the primary query for the report. 
  • SRSReportParameterAttribute. This determines the 'contract' class that defines additional parameters. More info on this below.


class VendBalanceReportDP extends SRSReportDataProviderBase

    VendBalanceReportTmp    reportLine;


The data provider needs at least one method that uses the SRSReportDataSetAttribute attribute. This tells SSRS that the method is one that returns a temporary table buffer that can  be used as a Dataset inside the report. When you add the dataset in the report designer, this method will be exposed (based on the table name).

public VendBalanceReportTmp reportLines()
    select reportLine;
    return reportLine;

The processReport method is where the bulk of the processing takes place. The general approach is that you'll run the query returned by this.parmQuery( ), and use the results to populate the temporary table buffer. (In this example, the 'reportLine' buffer defined in the class declaration). Check the comments for extra information on what's happening.

public void processReport()
    QueryRun                    qr;
    VendTable                   vendTable;
    VendGroup                   vendGroup;
    VendTrans                   vendTrans;

    // Get a reference to the contract for this report. The relevant contract class is      
    // defined by the SRSReportParameterAttribute used in the class declaration.
    VendBalanceReportContract   contract    = this.parmDataContract() as VendBalanceReportContract;
    ToDate                      toDate      = contract.parmToDate();
    boolean                     addLine;

    // If 'toDate' has not been specified (ie left blank), then assume today's date.        
        toDate = systemDateGet();

    // Run the query        
    qr = new QueryRun(this.parmQuery());
            vendTable   = qr.get(tableNum(vendTable));
            vendGroup   = qr.get(tableNum(VendGroup));

            // Setup up a new report line            
            reportLine.VendAccount      = vendTable.AccountNum;
            reportLine.VendName         =;
            reportLine.VendGroupId      = vendTable.VendGroup;
            reportLine.GroupNAme        = vendGroup.Name;
            reportLine.Balance          = vendTable.balancePerDate(toDate);

            // Select the last invoice transaction for the current vendor                            
            select firstonly vendTrans
                order by TransDate desc
                where   vendTrans.AccountNum    == vendTable.AccountNum
                &&      vendTrans.TransType     == LedgerTransType::Purch;

            reportLine.LastInvoiceDate  = vendTrans.TransDate;
            reportLine.LastInvoiceId    = vendTrans.Invoice;

            // Check final conditions for displaying the line
            addLine = true;

            // Check the 'includeVendorsWithoutTransactions' flag - if it has not been set,                        
            // and there is no invoice transaction against the vendor, then don't display this line.
            if(!contract.parmIncludeVendorsWithoutTransactions() && vendTrans.RecId == 0)
                addLine = false;




Note that we don't return anything here. The reportLine buffer is being populated, and will be returned in the 'reportLines' method mentioned above. The processReport method is probably the closest analogy to the old 'fetch' method pre-Ax2012, with the main difference being that instead of 'sending' the records immediately, they're buffered into the temporary table.

VendBalanceReportContract (Report contract class for additional parameters)

The contract class is attached to the data provider (via attribute SRSReportParameterAttribute in the data provider class declaration), and allows us to define additional parameters that don't fit the primary query structure. The class declaration for the contract class should define attribute DataContractAttribute. We also define variables to hold the parameter values. In this example, we have two.

class VendBalanceReportContract
    ToDate          toDate;
    NoYesId         includeVendorsWithoutTransactions;

For each parameter, we need a parm accessor method that sets/gets the value of the corresponding variable. Each must be tagged with at least DataMemberAttribute, as well as others as shown below.

 SysOperationLabelAttribute("Transaction 'to' date"),
 SysOperationHelpTextAttribute("Date up to which transaction should be considered"),
public ToDate parmToDate(ToDate _toDate = toDate)
    toDate = _toDate;
    return toDate;

Another parm method for includeVendorsWithoutTransactions:

 SysOperationLabelAttribute("Include vendors without transactions"),
 SysOperationHelpTextAttribute("Whether to include vendors that have no transactions against them"),
public NoYesId parmIncludeVendorsWithoutTransactions(NoYesId _includeVendorsWithoutTransactions = includeVendorsWithoutTransactions)
    includeVendorsWithoutTransactions = _includeVendorsWithoutTransactions;
    return includeVendorsWithoutTransactions;

Creating the report

OK now that we've got the X++ and AOT elements needed, the next step is building the report. I won't go into too much detail but the main steps are:
  • Open Visual Studio
  • Create a new Ax report model project, named VendBalanceReport
  • Add a new Ax report to the project, named VendBalanceReport
  • Right-click the project and 'Add to AOT'

All going well, this will connect to Ax and create an AOT entry called VendBalanceReport under Visual Studio Projects / Dynamics Ax Model Projects. Now add the dataset.
  • Create a Dataset in the report, named VendBalanceReportDS. In the properties, set the data source type to Report Data Provider, and select the dropdown under Query. This will let you select the VendBalanceReportDP class we defined earlier. NB this step scans the AOT for all classes that derive SRSReportDataProviderBase.
  • Once that wizard closes (make sure all fields are selected), you'll see several parameters added to the dataset. The ones underscored with Ax_ are system parameters that generally remain hidden, the VendBalanceReportDP_DynamicParameter is a place-holder for all ranges defined in the VendBalanceReport query, and you'll see two for the parameters defined in the contract class. If you're missing those, make sure that you've defined all of the attributes mentioned in the previous steps.

Now to add the design:
  • Right-click 'Designs' and add a new auto design. Set the LayoutTemplate to ReportLayoutStyleTemplate, and the title to Vendor Balance Report.
  • Under the new design, right-click and add a new list. Set the Style template to ListStyleTemplate.
  • Create a new entry under 'Groupings' called 'VendGroup'. Set "Page Break at End" to true, and drag field VendGroup from the dataset fields to the 'Group on' element.
  • Add VendGroupID and GroupName to the 'Header' section of the group.
  • Drag fields VendAccount, VendName, LastInvoiceId, LastInvoiceDate, and Balance from the dataset to the 'Data' section of the list.
Build the solution. NB part of the build step saves the changes back to the AOT.

You should end up with something that looks like this (click to expand):

OK, so now we're ready to deploy the report to SSRS. There are a couple of ways of doing this, and your mileage may vary:

  1. Find the report element in the AOT under SSRS Reports / Reports. Right-click VendBalanceReport and 'Deploy'.
  2. My preferred way is to open an Ax powershell console (Start -> Administrative Tools -> Microsoft Dynamics Ax 2012 Management Console). Type: publish-axreport -reportname VendBalanceReport. The reason I prefer doing it this way is because after a change I can quickly switch to the management console, hit up for the last command, then enter to deploy. 
If all goes to plan, we'll have the report definition project in Visual Studio, which has been synchronised with the Ax AOT, and also deployed from the AOT to the reporting server. If you've hit problems to this point, post a comment and I'll try and offer tips to get around it.

Last thing we need is a menu-item in Ax pointing to the report. Under Menu Items / Output, create a new menu-item called VendBalanceReport. Set:
  • ObjectType to SSRSReport
  • Object to VendBalanceReport
  • It should automatically pick up the first design, which in our case will be called AutoDesign1.
Now, right-click and 'Open', and you should be able to run/test the report from within Ax, and that's it for this example. It's a relatively simple report and as you can see not a small amount of work.

In my opinion, developing reports in Ax 2012 is a lot harder than it used to be, and to be honest a lot harder than it should be. The old-style MorphX reports may be considered the ugly-cousin of the reporting world, but they fit the Ax development model a lot more cleanly, and had none of the deployment/configuration problems SSRS and friends bring. [OK.. End of rant].

Running the report via code

As a last step, the following code shows how to run the report from X++ code. Here we set the output to PDF, add a filter to the query, and set the extended parameters. There's another post about running reports here.

private void generateVendBalanceReport()
    SrsReportRunController      reportRunController;
    Map                         queryContracts;
    MapEnumerator               mapEnum;
    Query                       query;
    QueryBuildRange             range;

    SrsReportDataContract       contract;
    VendBalanceReportContract   rdpContract;
    FilePath                    outputPath = "c:\\";

    // Create the report run controller
    reportRunController = new SrsReportRunController();

    // Set printer settings (print to file, format, filename, etc).
    contract    = reportRunController.parmReportContract();
    contract.parmPrintSettings().fileName(outputPath + "VendBalanceReport.pdf");
    // Use our custom-defined report contract class
    rdpContract = contract.parmRdpContract() as VendBalanceReportContract;
    // Add a range to the query (filter vendors that begin with '3').
    queryContracts = contract.parmQueryContracts();
    mapEnum = queryContracts.getEnumerator();
        // Get the query and update the datasource as required
        query = mapEnum.currentValue();
        range = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(VendTable)),fieldNum(VendTable,AccountNum));

    // Run the report

That's it for this post. Feel free to post questions, comments, or corrections..