SSRS Overview in Dynamics Ax 2012

One of the easier ways to develop SSRS reports in Ax 2012 is through using the MVC pattern.  This gives the advantage of using a consistent programming approach while also be able to run the report immediately or as a batch job. My personal preference is to create a stored procedure to retrieve data instead of using AX queries.  This allows for better opportunities for tuning the underlying queries without redeploying model stores.

The typical order of development for a report goes:

1. Write a stored procedure to return the required data (an Ax query can be used, but it does tend to be a lot slower).
2. Develop an “In Memory” table as a container for the results.
3. Write the class to retrieve the data.
4. Write the contract to provide the parameters that will be used.
5. Mock up the controller class to pass the parameters to the data provider class.
6. Write a UI builder class to provide non-default labels or custom drop downs for your parameters.
7. Write the SSRS report in Visual Studio.
8. Complete the controller class by using the “ssrsReportStr” for the report and design used.
9. Run the report.

Class Model

Writing a stored procedure in SQL Server and writing a corresponding table is straightforward.  The only notes that I would make is that the table type should be set to “InMemory”.  In addition, the execute permission must be granted to the user used to run the AOT.  I maintain a role that is granted all stored procedure execute grants and the AOT user(s) are assigned to the role. That way, the data retrieve will not be posted to the database and different users can call the same report at the same time.

By convention, all classes are named with the report name as the report followed by a suffix to describe its purpose.  So, if there you report is called “The Daily Stock Receipt Report”.  The related classes would be name as follows.

• DailyStockReceiptReportDP (Data Provider)
• DailyStockReceiptReportContract (Parameter class)
• DailyStockReceiptReportController (Invokes the Parameter Form, calls the data method and invokes the report).
• DailyStockReceiptReportUIBuilder (Customizes the Parameter Form)

The Contract Class

The Contract Class is the simplest of the elements to develop.  The declaration must be decorated with a Data Contract Attribute.  If a UI Builder is used, then a second attribute, Sys Operation Contract Processing Attribute must be used to associate the contract with the UI Builder Class.  The class must derive from SysOperationValidatable. The only members needed are used as parameters.  If a UI builder class does not override the labels, then the labels defined for the EDT will be used on the parameter form.

[DataContractAttribute,SysOperationContractProcessingAttribute(classstr(DailyStockReceiptReportUIBuilder))]

public class DailyStockReceiptReportContract implements SysOperationValidatable
{
    FromDate startDate;
    ToDate endDate;
    DirPartyName apContact;
}

Each parm method must be decorated with a Data Member Attribute that identifies the EDT used and a Sys Operations Display Order Attribute that places the parameter in a specific order. It may also have a Sys Operation Help Text Attribute.  Otherwise, it is a standard parm method.

[

    DataMemberAttribute(identifierStr(FromDate)),
    SysOperationHelpTextAttribute(literalStr('@SYS35856')),
    SysOperationDisplayOrderAttribute('1')
]
public FromDate parmStartDate(FromDate _startDate = startDate)
{
    startDate = _startDate;
    return startDate;
}

[
    DataMemberAttribute(identifierStr(ToDate)),
    SysOperationHelpTextAttribute(literalStr('@SYS35858')),
    SysOperationDisplayOrderAttribute('2')
]
public ToDate parmEndDate(ToDate _endDate = endDate)
{
    endDate = _endDate;
    return endDate;
}

[
    DataMemberAttribute(identifierStr(DirPartyName)),
    SysOperationHelpTextAttribute(literalStr('@PCA153')),
    SysOperationDisplayOrderAttribute('3')
]
public DirPartyName parmAPContact(DirPartyName _apContact = apContact)
{
    apContact = _apContact;
    return apContact;
}

Since the class implements SysOperationValidatable, it is possible to override the validate method.  In this case, you could assure that the “From Date” occurs before the “End Date” or the that AP Contact is not empty.  However, this was not necessary here.

public boolean validate()

{
    boolean ret = true;

    return ret;
}

The Data Provider

The Data Provider class has 3 major elements.  The class declaration must have an SRS Report Parameter Attribute pointing to the Contract class.  It must have a method that returns the “In Memory” table that is decorated with an SRS Report Dataset Attribute.  Finally, it must have a data retrieval method decorated with a Sys Entry Point Attribute.  This method calls a server static method that actually retrieves the data.

The class declaration for a report is very straightforward.  The only member is (typically) is a table buffer to the “In Memory” table. As mentioned above, it must be decorated with an SRS Report Parameter Attribute that points to the contract class.

[SRSReportParameterAttribute(classStr(DailyStockReceiptReportContract))]
public class DailyStockReceiptReportDP extends SRSReportDataProviderBase
{
    DailyStockReceiptReportTmp dailyStockReceiptReportTmp;
}

The method to retrieve the table is equally straightforward.  It is decorated with an SRS Report Dataset Attribute that points to the “In Memory” table.  This is the method that SSRS will use to populate the data in the report.

[SRSReportDataSetAttribute(tableStr(DailyStockReceiptReportTmp))]
public DailyStockReceiptReportTmp getDailyStockReceiptReportTmp()
{
    select dailyStockReceiptReportTmp;
    return dailyStockReceiptReportTmp;
}

By convention, I name the data retrieval method processReport.  It must be decorated with a Sys Entry Point Attribute.  The process retrieves the contract, extracts the parameters and calls the data population method.

[SysEntryPointAttribute(true)]
public void processReport()
{
    FromDate startDate;
    ToDate endDate;
    DirPartyName apContact;
    DailyStockReceiptReportContract contract;
    ;
    contract = this.parmDataContract() as DailyStockReceiptReportContract;
    startDate = contract.parmStartDate();
    endDate = contract.parmEndDate();
    apContact = contract.parmAPContact();
    dailyStockReceiptReportTmp = DailyStockReceiptReportDP::getDailyStockReceiptReport(startDate,
                                     endDate, apContact);

}

The final method calls the stored procedure and populates the table.  It needs to be set to server and static in order to run.  Otherwise, an error is thrown if it is run in batch mode.  The method is fairly simple.  A direct connection is opened to the database, an “strFmt” statement formats the SQL string and parameters.   Code Access Secuirty is invoked to call the stored procedure and a loop is used to populate the table.  A store procedure (or function) is always preferred to retrieve data.  The normal caveats apply about using in-line SQL (SQL injection and fragility).  The only point that may be at issue is that the method tightly couples the stored procedure output to the structure of the “In Memory” table.

server static DailyStockReceiptReportTmp getDailyStockReceiptReport(FromDate _startDate, ToDate

                                                              _endDate, DirPartyName _apContact)
{
    Connection con;
    Statement statement;
    str sqlCode;
    SqlStatementExecutePermission permission;
    ResultSet result;
    DailyStockReceiptReportTmp dailyStockReceiptReportTmp;
    int i;
    ;
    con = new Connection();
    statement = con.createStatement();

    sqlCode = strFmt("EXEC dbo.GetDailyStockReceipts '%1', '%2', '%3'", _startDate, _endDate, _apContact);

    permission = new SqlStatementExecutePermission(sqlCode);
    permission.assert();
    result = statement.executeQuery(sqlCode);
    CodeAccessPermission::revertAssert();

    while (result.next())
    {
        i = 1;
        dailyStockReceiptReportTmp.DeliveryDate = result.getDate(i);
        i++;
        dailyStockReceiptReportTmp.OrderAccount = result.getString(i);
        i++;
        dailyStockReceiptReportTmp.Name = result.getString(i);
        i++;
        dailyStockReceiptReportTmp.PurchId = result.getString(i);
        i++;
        dailyStockReceiptReportTmp.PackingSlipId = result.getString(i);
        i++;
        dailyStockReceiptReportTmp.InventLocationId = result.getString(i);
        i++;
        dailyStockReceiptReportTmp.ItemId = result.getString(i);
        i++;
        dailyStockReceiptReportTmp.NameAlias = result.getString(i);
        i++;
        dailyStockReceiptReportTmp.PurchPrice = result.getReal(i);
        i++;
        dailyStockReceiptReportTmp.Qty = result.getReal(i);
        i++;
        dailyStockReceiptReportTmp.Amount = result.getReal(i);
        i++;
        dailyStockReceiptReportTmp.APContact = result.getString(i);

        dailyStockReceiptReportTmp.insert();
    }

    return dailyStockReceiptReportTmp;
}

UI Builder

The UI Builder class allows you to customize the parameter form, set defaults for parameters, provide alternative parameter labels and override data provided in a lookup. The class declaration must extend Sys Operation Automatic UI Builder.  Typically, the members will consist of Dialog Field for each parameter in the contract and a member that holds an instance of the contract.

class DailyStockReceiptReportUIBuilder extends SysOperationAutomaticUIBuilder

{
    DialogField dialogStartDate;
    DialogField dialogEndDate;
    DialogField dialogAPContact;
    DailyStockReceiptReportContract contract;
}

The build method allows you to assign the contract and dialogfields to the parameter form.

public void build()
{
    contract = this.dataContractObject() as DailyStockReceiptReportContract;
    dialogStartDate = this.addDialogField(methodStr(DailyStockReceiptReportContract, parmStartDate), contract);
    dialogEndDate = this.addDialogField(methodStr(DailyStockReceiptReportContract, parmEndDate), contract);
    dialogAPContact = this.addDialogField(methodStr(DailyStockReceiptReportContract, parmAPContact), contract);
}

The getFromDialog methods also retrieval of parameter values from the contract instance.

public void getFromDialog()
{
    contract = this.dataContractObject();
    super();
//Access parameters
}

The initializeFiles method allows you to set any default values required. This will override Ax’s caching of parameter values.  In order to access the parameter values, the data contract must also be accessed.

public void initializeFields()
{
    contract = this.dataContractObject();
//Access parameters
}

The postBuild method allows you to override parameter form elements.  New labels can be assigned and new lookup data sets can be called.  In this case, we are interested in retrieving only those employees who are actually assigned as AP Contacts (Main Contact Worker on the VendTable) to our Vendors.

public void postBuild()

{
    ;

    super();

    dialogAPContact = this.bindInfo().getDialogField(this.dataContractObject(),
                  methodStr(DailyStockReceiptReportContract,parmAPContact));
    dialogAPContact.registerOverrideMethod(methodStr(FormStringControl, lookup),
                  methodStr(DailyStockReceiptReportUIBuilder,apContactLookup), this);
    dialogAPContact.lookupButton(2);
    dialogAPContact.label('@PCA153');
}

Overriding a parameter’s label is simply a call to the label method on a dialog field. The call to lookup button must have 2 as the parameter.  I have never found a satisfactory reason of why.  In order to override the default lookup list on a drop down (that is based on the EDT of the field), access to the binding information is required.  Once it is acquired, an override method can be registered.  In this case, it is the apContactLookup method in the DailyStockReceiptUIBuilder Class.  This method simply defines a query of all workers who have a link to the Vendtable’s MainContactWorkerField.

private void apContactLookup(FormStringControl _control)
{
    Query query = new Query();
    QueryBuildDataSource qbds_DirPartyTable, qdbs_HCMWorker, qdbs_VendTable;
    QueryBuildRange qbr;
    SysTableLookup sysTableLookup;
    str apContact = "AP Contact";
    ;
   
    sysTableLookup = SysTableLookup::newParameters(tableNum(DirPartyTable), _control);
    sysTableLookup.addLookupfield(fieldNum(DirPartyTable,Name));
    qbds_DirPartyTable = query.addDataSource(tableNum(DirPartyTable));
    qbds_DirPartyTable.addSortField(fieldNum(DirPartyTable, Name));
    qbds_DirPartyTable.orderMode(OrderMode::GroupBy);

    qdbs_HCMWorker = qbds_DirPartyTable.addDataSource(tableNum(HCMWorker));
    qdbs_HCMWorker.joinMode(JoinMode::InnerJoin);
    qdbs_HCMWorker.relations(false);
    qdbs_HCMWorker.addLink(fieldNum(HCMWorker, Person), fieldNum(DirPartyTable, RecId));

    qdbs_VendTable = qdbs_HCMWorker.addDataSource(tableNum(VendTable));
    qdbs_VendTable.joinMode(JoinMode::ExistsJoin);
    qdbs_VendTable.relations(false);
    qdbs_VendTable.addLink(fieldNum(VendTable, MainContactWorker), fieldNum(HcmWorker, RecId));


    sysTableLookup.parmQuery(query);
    // Perform the lookup
    sysTableLookup.performFormLookup();

}

Controller Class

The controller class is straightforward.  The declaration must extend SRS Report Run Controller.

public class DailyStockReceiptReportController extends SrsReportRunController
{
}

The main method connects the controller to the report.  Note the Report Name cannot be filled until the SSRS report is completed.

public static void main(Args _args)
{
    DailyStockReceiptReportController controller = new DailyStockReceiptReportController();
    controller.parmArgs(_args);
    controller.parmReportName(ssrsReportStr(DailyStockReport, pddDailyStockReport));
    controller.startOperation();
}

The “prePromptModifyContract” provides another opportunity to initialize parameter values.  In this case, the Start and End Dates are populated with the current date if they are not already populated.

protected void prePromptModifyContract()
{
    PC_DailyStockReceiptReportContract contract;
    date currentdate = today();
    date yesterday;

    super();

    contract = this.parmReportContract().parmRdpContract() as PC_DailyStockReceiptReportContract;

    if(!contract.parmStartDate())
    {
        yesterday = DateTimeUtil::date(DateTimeUtil::addDays(DateTimeUtil::newDateTime(currentdate, 0), -1));
        contract.parmStartDate(yesterday);
    }

    if(!contract.parmEndDate())
    {
        contract.parmEndDate(currentdate);
    }
}

SSRS Report

Developing the report is straightforward.  Once you create a dataset, you need to set its type to “Report Data Provider”.  Then, when you select your query, you will be presented a list of all the SSRS data providers in the AOT.  Selecting “DailyStockReceiptReportDP.DailyStockReceiptReportTmp” (the temp table created at the beginning, will bring join the AOT to your report.  Any parameters created will be brought in automatically.

Afterwards, create and name the design.

Once this is done, you can finish formatting your report.  Return to the controller class and use the indicated values in the “parmReportName” statement of the main method.

controller.parmReportName(ssrsReportStr(DailyStockReport, pddDailyStockReport));

The last step is to, Compile, Incremental CIL Compile and Deploy the report.  Right click on the SSRS report in the AOT and select deploy element.  This may not work if you have not started your instance as an administrator.

Now the report will run by selecting open on the Controller class.

After selecting the parameters, you may select to run the report in batch mode.  If you do, reroute the destination from the Screen to a Printer, Email or File.

Conclusion

This concludes the summary of how to integrate SSRS Reporting into Ax 2012.  With practice, a complete report and supporting classes can be written in about a 1-2 days. This also includes setting menu items, menu placement and relevant security access.