Description

In this article I am going to explain how to build an SSRS report for AX 2012 that displays customer open transactions using a data provider class (A standard Ax report exists to retrieve open transactions but the intention is to demonstrate how to create a sample report).
Our report will have the following parameters:

  • Customer group
  • Customer account
  • From date
  • To date
  • Group by customer group (checkbox)

This sample report will include some common features frequently required:

  • Dependant lookup parameters (Select customer group as first parameter and in the second parameters select the account, but just be able to see the customers that belongs to the customer group selected in the first parameter).
  • Add a validation to a parameter field. (To demonstrate how to validate fields From date and To date are going to be mandatory and a custom message will be displayed if dates are not entered)
  • Have different report designs and be able to select which design will be displayed based on a parameter selected by the user. (the report will have 2 Auto designs, the first design will group by customer group and second design will not )
Steps:
  1. Create the necessary data dictionary objects
  2. Create the Contract, UIBuilder and Data provider classes
  3. Create the report and the designs
  4. Create de Controller class and Menu item to run the report
1. Create the necessary data dictionary objects
  1. Create a table of the type TempDB with the necessary fields to display in the report
    1
2. Create the necessary classes and menu item to execute the report
  1. Create a contract Class for the report parametersIn the class attribute SysOperationContractProcessingAttribute we need to indicate the UIBuilder class we are going to develop after this one, so leave that part pending, and write it after the class CustTransOpenRptUIBuilder is complete.
    [
        DataContractAttribute,
        SysOperationContractProcessingAttribute(classStr(CustTransOpenRptUIBuilder))
    ]
    class CustTransOpenRptContract
    {
        CustGroupId     custGroup;
        CustAccount     accountNum;
        TransDate       fromDate;
        ToDate          toDate;
        NoYes           groupByCustGroup;
    }
    [
        DataMemberAttribute('CustGroup')
    ]
    public CustGroupId parmCustGroup(CustGroupId _custGroup = custGroup)
    {
        custGroup = _custGroup;
        return custGroup;
    }
    [
        DataMemberAttribute('AccountNum')
    ]
    public CustAccount parmAccountNum(CustAccount _accountNum = accountNum)
    {
        accountNum = _accountNum;
        return accountNum;
    }
    [
        DataMemberAttribute('FromDate'),
        SysOperationLabelAttribute("@SYS5209"),    //From date
        SysOperationHelpTextAttribute("@SYS26930") //From date interval in date interval
    ]
    public TransDate parmFromDate(TransDate _fromDate = fromDate)
    {
        fromDate = _fromDate;
        return fromDate;
    }
    [
        DataMemberAttribute('ToDate'),
        SysOperationLabelAttribute("@SYS14656"),   //To date
        SysOperationHelpTextAttribute("@SYS26929") //To date interval in date interval
    ]
    public TransDate parmToDate(TransDate _toDate = toDate)
    {
        toDate = _toDate;
        return toDate;
    }
    [
        DataMemberAttribute('GroupByCustGroup'),
        SysOperationHelpTextAttribute("Group by cutomer group") //Use label
    ]
    public NoYes parmGroupByCustGroup(NoYes _groupByCustGroup = groupByCustGroup)
    {
        groupByCustGroup = _groupByCustGroup;
        return groupByCustGroup;
    }
  2. Then we create the UIBuilder class to manipulate the user interface when the user enters the parameters.

    Here we are going to create a custom lookup for the customer account to filter by customer group and we are going to override the control lookup to show the user just the accounts that belongs to the group selected.

    We also are going to create a method to reset the customer account field when the value of customer group is modified and we will override the modified method with this one. This is also a good example on how to override other control methods if necessary.

    class CustTransOpenRptUIBuilder extends SrsReportDataContractUIBuilder
    {
        DialogField     dialogCustGroup;
        DialogField     dialogAccountNum;
        DialogField     dialogFromDate;
        DialogField     dialogToDate;
        DialogField     dialogGroupByCustGroup;
    }

    Associate a dialog field with each parameter

    public void build()
    {
        CustTransOpenRptContract rdpContract = this.dataContractObject() as CustTransOpenRptContract;
    
        dialogCustGroup        = this.addDialogField(methodStr(CustTransOpenRptContract, parmCustGroup), rdpContract);
        dialogAccountNum       = this.addDialogField(methodStr(CustTransOpenRptContract, parmAccountNum), rdpContract);
        dialogFromDate         = this.addDialogField(methodStr(CustTransOpenRptContract, parmFromDate), rdpContract);
        dialogToDate           = this.addDialogField(methodStr(CustTransOpenRptContract, parmToDate), rdpContract);
        dialogGroupByCustGroup = this.addDialogField(methodStr(CustTransOpenRptContract, parmGroupByCustGroup), rdpContract);
    }

    Custom lookup method to filter the customers by the selected customer group

    public void accountNumLookup(FormStringControl _ctrl)
    {
        SysTableLookup       sysTableLookup ;
        Query                q;
        QueryBuildDataSource qbds;
        QueryBuildRange      qbr;
        ;
    
        sysTableLookup = SysTableLookup::newParameters(tableNum(CustTableCube), _ctrl);
    
        q = new Query();
        qbds = q.addDataSource(tableNum(CustTableCube));
    
        qbr = q.dataSourceTable(tableNum(CustTableCube)).addRange(fieldNum(CustTableCube, CustGroup));
        qbr.value(dialogCustGroup.value()); //Selected customer group
    
        sysTableLookup.addLookupfield(fieldNum(CustTableCube, AccountNum));
        sysTableLookup.addLookupfield(fieldNum(CustTableCube, Name));
        sysTableLookup.addLookupfield(fieldNum(CustTableCube, City));
        sysTableLookup.addLookupfield(fieldNum(CustTableCube, State));
        sysTableLookup.addLookupfield(fieldNum(CustTableCube, ZipCode));
        sysTableLookup.addLookupfield(fieldNum(CustTableCube, CountryRegionId));
    
        sysTableLookup.parmQuery(q);
        sysTableLookup.performFormLookup();
    
    }

    Custom modified method to reset customer account if a customer group is selected

    public void dialogCustGroupModified(FormStringControl _ctrl)
    {
    ;
        _ctrl.modified();
    
        if(dialogCustGroup.value())
        {
            dialogAccountNum.value("");
        }
    }

    Override the methods of the dialog fields

    public void postRun()
    {
        Dialog          dialogLocal = this.dialog();
        DialogField     dialogCustGroupPR;
        DialogField     dialogAccountNumPR;
    
        super();
    
        dialogLocal.dialogForm().formRun().controlMethodOverload(false);
        dialogLocal.dialogForm().formRun().design().caption("Customer open transactions"); //Use a label
    
        dialogCustGroupPR = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(CustTransOpenRptContract, parmCustGroup));
        dialogCustGroupPR.registerOverrideMethod(methodstr(FormStringControl, modified), methodstr(CustTransOpenRptUIBuilder,  dialogCustGroupModified), this);
    
        dialogAccountNumPR = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(CustTransOpenRptContract, parmAccountNum));
        dialogAccountNumPR.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(CustTransOpenRptUIBuilder, accountNumLookup), this);
    
    }
  3. Create the data provider class
    [
        SRSReportParameterAttribute(classstr(CustTransOpenRptContract))
    ]
    class CustTransOpenRptDP extends SRSReportDataProviderBase
    {
        CustTransOpenRptTmp custTransOpenRptTmp;
    }
    [
        SRSReportDataSetAttribute(tableStr(CustTransOpenRptTmp))
    ]
    public CustTransOpenRptTmp getCustTransOpenRptTmp()
    {
        select * from custTransOpenRptTmp;
        return custTransOpenRptTmp;
    }
    [SysEntryPointAttribute]
    public void processReport()
    {
        CustTransOpenRptContract contract;
        Query                    q;
        QueryRun                 qr;
        CustTrans                custTrans;
        CustTable                custTable;
    
        CustGroupId              parmCustGroup;
        CustAccount              parmAccountNum;
        TransDate                parmFromDate;
        TransDate                parmToDate;
        ;
    
        contract = this.parmDataContract() as CustTransOpenRptContract;
    
        parmCustGroup  = contract.parmCustGroup();
        parmAccountNum = contract.parmAccountNum();
        parmFromDate   = contract.parmFromDate();
        parmToDate     = contract.parmToDate();
    
        q = new Query();
    
        q.addDataSource(tableNum(CustTable));
        q.dataSourceTable(tableNum(CustTable)).addDataSource(tableNum(CustTrans));
        q.dataSourceTable(tableNum(CustTrans)).joinMode(JoinMode::OuterJoin);
        q.dataSourceTable(tableNum(CustTrans)).addLink(fieldNum(CustTable, AccountNum), fieldNum(CustTrans, AccountNum));
    
        q.dataSourceTable(tableNum(CustTable)).addRange(fieldNum(CustTable, CustGroup)).value(parmCustGroup);
        q.dataSourceTable(tableNum(CustTable)).addRange(fieldNum(CustTable, AccountNum)).value(parmAccountNum);
    
        q.dataSourceTable(tableNum(CustTrans)).addRange(fieldNum(CustTrans, Closed)).value(SysQuery::value(dateNull()));
        q.dataSourceTable(tableNum(CustTrans)).addRange(fieldNum(CustTrans, TransDate)).value(queryRange(parmFromDate, parmToDate));
    
        qr = new QueryRun(q);
    
        while(qr.next())
        {
            custTable = qr.get(tableNum(CustTable));
            custTrans = qr.get(tableNum(CustTrans));
    
            custTransOpenRptTmp.clear();
            custTransOpenRptTmp.AccountNum = custTable.AccountNum;
            custTransOpenRptTmp.CustGroup  = custTable.CustGroup;
            custTransOpenRptTmp.Name       = custTable.name();
            custTransOpenRptTmp.Voucher    = custTrans.Voucher;
            custTransOpenRptTmp.Invoice    = custTrans.Invoice;
            custTransOpenRptTmp.TransDate  = custTrans.TransDate;
            custTransOpenRptTmp.AmountCur  = custTrans.AmountCur;
            custTransOpenRptTmp.SettleAmountCur = custTrans.SettleAmountCur;
            custTransOpenRptTmp.Balance    = custTrans.AmountCur - custTrans.SettleAmountCur;
            custTransOpenRptTmp.insert();
    
        }
    
    }
3. Create the report and the designs
  1. Open Visual Studio and create a new report model project
    2
  2. Add a new report to the project
    3
  3. Add a new Data set to the project
    4
  4. Select Report Data Provider as data source type and click the property query to view the available classes
    5
  5. Select the created class and click OK to confirm the fields needed in our data set
    6
  6. Create the design, for our example we will use 2 precision designs named: Report and ReportNonGrouped
    7
  7. Edit the designs as needed
    8
  8. Deploy the solution
    10
4. Create de Controller class and Menu item to run the report
  1. Create a Controller class to add validation to the fields and select a design based on the selected parameters.
    class CustTransOpenRptController extends SrsReportRunController{}
    public static void main(Args _args){ CustTransOpenRptController controller = new CustTransOpenRptController(); controller.parmReportName(ssrsReportStr(CustTransOpenRpt, Report)); //Default design controller.parmArgs(_args); controller.startOperation();}

    Here the design is selected based on the parameter selected

    protected void preRunModifyContract(){ CustTransOpenRptContract contract = this.parmReportContract().parmRdpContract() as CustTransOpenRptContract; if(contract.parmGroupByCustGroup()) { this.parmReportContract().parmReportName(ssrsReportStr(CustTransOpenRpt, Report)); } else { this.parmReportContract().parmReportName(ssrsReportStr(CustTransOpenRpt, ReportNotGrouped)); } super();}

    In this method validations can be done to the parameters entered

    protected void preRunModifyContract(){ CustTransOpenRptContract contract = this.parmReportContract().parmRdpContract() as CustTransOpenRptContract; if(contract.parmGroupByCustGroup()) { this.parmReportContract().parmReportName(ssrsReportStr(CustTransOpenRpt, Report)); } else { this.parmReportContract().parmReportName(ssrsReportStr(CustTransOpenRpt, ReportNotGrouped)); } super();}
  2. Create the Menu item to call the controller class, add label, help text and security as needed. Use the Menu item to call the report.
    11

    12

    13