Saturday, March 08, 2008

Find all reports with datasources innerjoined 1:n

I haven't really written much X++ code in the past 5 months, so a work-related task on X++ was like a holiday :)

I needed to find all reports, in which there is a child datasource (on some level) that is inner joined with the parent datasource as 1 to many.

Here is one of the possible solutions of the problem.
I would also want to talk a little bit about different methods used in this scenario, in case some of you are still unfamiliar with those:

static void FindReports(Args _args)
{
#AOT
Report report;
TreeNode treeNode = TreeNode::findNode(#ReportsPath);
TreeNodeIterator iterator = treeNode.AOTiterator();
QueryBuildDataSource qbds;

boolean find1nInnerJoin(QueryBuildDataSource _qbdsParent)
{
int i;
QueryBuildDataSource qbdsChild;
boolean ret;
;
for (i = 1; i <= _qbdsParent.childDataSourceCount(); i++)
{
qbdsChild = _qbdsParent.childDataSourceNo(i);
if (qbdsChild)
{
if (qbdsChild.joinMode() == JoinMode::InnerJoin && qbdsChild.fetchMode() == QueryFetchMode::One2Many)
return true;

if (qbdsChild.childDataSourceCount() > 0 && find1nInnerJoin(qbdsChild))
return true;
}
}
return ret;
}
;

treeNode = iterator.next();
while (treeNode)
{
if (treeNode.sysNodeType() == 202) //Report
{
report = treeNode;
if (report && report.query().dataSourceCount() > 1)
{
qbds = report.query().dataSourceNo(1);
if (find1nInnerJoin(qbds))
info(report.name());
}
}
treeNode = iterator.next();
}
}


Here are some keypoints:

1. Notice the use of TreeNodeIterator class. This is an example of an Iterator applied to the AOT. Very convenient and easy to use. You can read more about this class and its methods on MSDN

2. Notice the use of BaseEnums QueryFetchMode and JoinMode - I have seen many developers specifying integer values instead. I would suggest using these enumerations instead - the code will be much easier to read later on.
I don't know the enum for return value of method treeNode.sysNodeType() though. So if someone does, write a comment - it would be nice to know.

3. Notice the use of an implicit conversion from a base type object (TreeNode class) to a derived type object (Report). This is allowed, because, as you know, X++ type system is a "weak" type system. But beware, because this might lead to run-time errors. For example, if you call a method on the Report class, that is not inherited from the TreeNode class, you must be absolutely sure that the object is actually of type Report. Otherwise, you will get a run-time error.
This is why before the conversion, I verify that only Report objects get through to the following code.

4. The last, but not least: Notice the methods exposed by the QueryBuildDataSource class - basically, it allows to receive a lot of information about a specified query. Again, for more information, refer to MSDN