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