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.


[SRSReportQueryAttribute(queryStr(VendBalanceReport)),
 SRSReportParameterAttribute(classStr(VendBalanceReportContract))]

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).

[SRSReportDataSetAttribute(tableStr(VendBalanceReportTmp))]
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.        
    if(!toDate)
        toDate = systemDateGet();

    // Run the query        
    qr = new QueryRun(this.parmQuery());
    while(qr.next())
    {
        if(qr.changed(tableNum(vendTable)))
        {
            vendTable   = qr.get(tableNum(vendTable));
            vendGroup   = qr.get(tableNum(VendGroup));

            // Setup up a new report line            
            reportLine.clear();
            reportLine.VendAccount      = vendTable.AccountNum;
            reportLine.VendName         = vendTable.name();
            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;

            if(addLine)
                reportLine.insert();
        }

    }

}

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.

[DataContractAttribute]
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.

[DataMemberAttribute("ToDate"),
 SysOperationLabelAttribute("Transaction 'to' date"),
 SysOperationHelpTextAttribute("Date up to which transaction should be considered"),
 SysOperationDisplayOrderAttribute('1')]
public ToDate parmToDate(ToDate _toDate = toDate)
{
    toDate = _toDate;
    return toDate;
}

Another parm method for includeVendorsWithoutTransactions:

[DataMemberAttribute("IncludeVendorsWithoutTransactions"),
 SysOperationLabelAttribute("Include vendors without transactions"),
 SysOperationHelpTextAttribute("Whether to include vendors that have no transactions against them"),
 SysOperationDisplayOrderAttribute('2')]
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();
    reportRunController.parmReportName('VendBalanceReport.AutoDesign1');
    reportRunController.parmLoadFromSysLastValue(false);

    // Set printer settings (print to file, format, filename, etc).
    contract    = reportRunController.parmReportContract();
    contract.parmPrintSettings().printMediumType(SRSPrintMediumType::File);
    contract.parmPrintSettings().overwriteFile(true);
    contract.parmPrintSettings().fileFormat(SRSReportFileFormat::PDF);
    contract.parmPrintSettings().fileName(outputPath + "VendBalanceReport.pdf");
    
    // Use our custom-defined report contract class
    rdpContract = contract.parmRdpContract() as VendBalanceReportContract;
    rdpContract.parmToDate(systemDateGet());
    rdpContract.parmIncludeVendorsWithoutTransactions(true);    
    
    // Add a range to the query (filter vendors that begin with '3').
    queryContracts = contract.parmQueryContracts();
    mapEnum = queryContracts.getEnumerator();
    while(mapEnum.moveNext())
    {
        // Get the query and update the datasource as required
        query = mapEnum.currentValue();
        range = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(VendTable)),fieldNum(VendTable,AccountNum));
        range.value('3*');
    }

    // Run the report
    reportRunController.runReport();
}


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

6 comments:

  1. Hello Dan,

    Thank you for such a nice post. Really helpful..

    Regards
    Abhi

    ReplyDelete
  2. hi, i am building AX SSRS report using RDP class i am able to see the data in temp table within AX,
    but i am not able to see the data in the SSRS report
    (the report has got three temp table)
    can you please guide

    ReplyDelete
    Replies
    1. That's not enough information to go on, but check that you've defined the datasource properly, that you've assigned the datasource to any relevant controls, etc. Also how are you viewing the contents of the temporary table from within Ax?

      Delete
  3. Hi Dan, thank you for the great article. I'm curious about the performance of the processReport method in the data provider class. Is there any way to avoid looping through every record in our query and filling up a temporary table with our data? I want the custom data contract so I can customize the lookups on the parameters form but my concern is whether performance will be effected when pulling a report for a large amount of data. Is it possible to simply apply the ranges to the query in the processReport
    method and return the query so that SSRS can page the data in accordingly without affecting performance?

    ReplyDelete
    Replies
    1. Hi Adam - Thanks for your comment. The population of the temporary table itself isn't likely to be a bottleneck. Inserts into a table are typically quite fast, especially since you wouldn't need to create indexes on that table unless you're doing some secondary updates/queries after the initial population. If your final output/result set is relatively small (ie anything less than tens of thousands of records), then it is probably better to keep it as 'in memory' instead of a true SQL temporary table, as this reduces the amount of chatter between Ax and the database during population. The main thing you should be looking at is the query over the source data, and ensuring that you're joining tables where possible, instead of selecting additional records in a loop etc, and also that you're filtering/joining based on indexed fields, (not always possible). Sometimes building up a hierarchy of views to use in the query can make things more logical to organize, although this in itself won't help performance. In some circumstances, it's sometimes necessary to modify the core tables and posting processes themselves to better support a report, as long as you're sure that report is worth the trouble. If you send me some details on what you're trying to extract I can offer suggestions about possible approaches, taking performance into account. It's too often ignored (out of time constraints or laziness) so it's good that you're considering it early on.

      Delete