This is Part 2 of a 4 Part article that covers how to create your own end-user-report writer for Dynamics AX. The previous article can be found here: [https://community.dynamics.com/ax/b/dynamicsax_wpfandnetinnovations/archive/2013/07/14/transactional-report-writing-for-end-users.aspx#.UeplQ43FX3Q]
 
Since we need to walk the user through a series of steps to achieve our goal, the optimum form design will be a wizard or tabbed form like the one illustrated below. On the first tab, we should implement a mechanism to allow the user to select a query from the AOT (and further define any criteria or sorting). I won’t go through the specifics of the form design, suffice to say that it should look something like this:
 
 
The lookup for the Query name string edit field is a standard framework lookup that retrieves a list of list of query objects from the UtilElements table. User-specific criteria can be implemented within this routine to restrict access to certain queries.
 
X++
public void lookup()
{
    Query                   query = new Query();
    QueryBuildDataSource    queryBuildDataSource;
    SysTableLookup          sysTableLookup;
 
    super();
 
    //Create an instance of SysTableLookup with the form control passed in
    sysTableLookup = SysTableLookup::newParameters(tablenum(UtilElements), this);
 
    //create the query datasource
    queryBuildDataSource = query.addDataSource(tablenum(UtilElements));
    sysTableLookup.addLookupfield(fieldnum(UtilElements, Name), true);
    queryBuildDataSource.addRange(fieldNum(UtilElements, recordType)).value(queryValue(UtilElementType::Query));
 
    //add the query to the lookup form
    sysTableLookup.parmQuery(query);
 
    // Perform lookup
    sysTableLookup.performFormLookup();
}
 
 
The Select button will perform a standard query-prompt. After the prompt is closed, the caller needs to examine any user-defined settings that may have been added to the modified query.
 

·         It is important to re-link the Query object to the running instance after the prompt has ended otherwise you only end up accessing the original query and none of the modified options.

 
X++ [classDeclaration]
public class FormRun extends ObjectRun
{
    Query           objQuery;
    QueryRun        objQueryRun;
 
    str             reportCompany, reportQuery, reportCriteria, reportSorting, reportFields;
}
 
X++ [setQueryCriteria]
public void setQueryCriteria()
{
    // query objects
    QueryBuildDataSource    objQueryBuildDataSource;
    QueryBuildRange         objQueryBuildRange;
    QueryFilter             objQueryFilter;
 
    // dictionary objects
    DictTable               dictTable;
    DictField               dictField;
 
    // working variables
    int                     rangeCount, filterCount, i, j, k;
    str                     criteria, sortOrder;
 
    // validate query selected
    if ((select RecId from UtilElements where UtilElements.Name == StringEditQueryName.text() && UtilElements.RecordType == UtilElementType::Query).RecId == 0)
    {
        Box::stop("Query name is invalid or not set.");
        return;
    }
 
    // initialise query objects
    objQuery = new Query(StringEditQueryName.text());
    objQueryRun = new QueryRun(objQuery);
 
    // prompt for query parameters
    element.lock();
    try
    {
        if (!objQueryRun.prompt())
        {
            return; // cancel button pressed
        }
    }
    catch(Exception::Error)
    {
        Box::stop("Unknown error occurred.");
        return;
    }
    element.unLock();
 
    // repoint the query object to the running instance (essential)
    objQuery = objQueryRun.query();
 
    // iterate through all data sources defined on query
    criteria = "";
    for(i = 1; i <= objQuery.dataSourceCount(); i++)
    {
        rangeCount = objQuery.dataSourceNo(i).rangeCount();
        filterCount = 0;
        if(!objQuery.dataSourceNo(i).embedded())
        {
            filterCount = objQuery.queryFilterCount(objQuery.dataSourceNo(i));
        }
        dictTable = new DictTable(objQuery.dataSourceNo(i).table());
 
        // iterate through all filters that have been added to query
        for (k = 1; k <= filterCount; k++)
        {
            try
            {
                objQueryFilter = objQuery.queryFilter(k, objQuery.dataSourceNo(i));
                dictField = new DictField(objQueryFilter.dataSource().table(), fieldname2id(objQueryFilter.dataSource().table(), objQueryFilter.field()));
                if (objQueryFilter.value())
                {
                    criteria += strFmt("%1, %2. Range: %3", tableId2name(objQueryFilter.dataSource().table()), dictField.label(), objQueryFilter.value()) + "\n";
                }
            }
            catch { }
        }
 
        // iterate through all preset ranges on the original query
        for (j = 1; j <= rangeCount; j++)
        {
            try
            {
                objQueryBuildRange = objQueryRun.query().dataSourceNo(i).range(j);
                dictField = new DictField(objQuery.dataSourceNo(i).table(), fieldname2id(objQuery.dataSourceNo(i).table(), objQueryBuildRange.AOTname()));
                if(objQueryBuildRange.value())
                {
                    criteria += strfmt("%1, %2. Range: %3",dictTable.label(), dictField.label(), objQueryBuildRange.value()) + "\n";
                }
            }
            catch { }
        }
    }
    reportCriteria = criteria;
 
    // iterate through all sort fields defined on query
    sortOrder = "";
    for (i = 1; i <= objQuery.orderByFieldCount(); i++)
    {
        if (objQuery.orderByField(i).direction() == sortOrder::Ascending)
        {
            sortOrder += objQuery.orderByField(i).AOTname() + "\n";
        }
        else
        {
            sortOrder += objQuery.orderByField(i).AOTname() + " (Desc)\n";
        }
    }
    reportSorting = sortOrder;
}
 
 
Once we’ve captured the dynamic Query options we have the ability to print them wherever we need to on the report.
 

·         For some ISV solutions, it is essential (especially from a support perspective) to be able to replicate the condtions under which a report was run. Printing the query parameters on a report-cover-sheet allows this.

 
The next thing to do is to offer the user a screen that will allow them to select which fields will appear on the report. A couple of standard ListBoxes and some transfer buttons will do the job:
 
 
The list of avialable fields within the query can be obtained using the following template code:
 
X++
public void populateAvailableFieldList()
{
    // query objects
    QueryBuildFieldList     objQueryBuildFieldList;
 
    // dictionary objects
    DictTable               dictTable;
    DictField               dictField;
 
    // working variables
    int                     fieldCount, i, j, k;
    str                     strWorking;
 
    if (objQuery == null)
    {
        Box::stop("Query name, criteria and sort need to be set on first tab");
        return;
    }
 
    ListboxAvailableFields.clear();
    for(i = 1; i <= objQuery.dataSourceCount(); i++)
    {
        objQueryBuildFieldList = objQuery.dataSourceNo(i).fields();
        fieldCount = objQueryBuildFieldList.fieldCount();
 
        // iterate through all fields within query
        for (k = 1; k <= fieldCount; k++)
        {
            try
            {
                dictField = new DictField(objQuery.dataSourceNo(i).table(), objQueryBuildFieldList.field(k));
                strWorking = tableId2name(objQuery.dataSourceNo(i).table()) + "." + dictField.label() + "[" + dictField.name() + "]";
                if (ListboxAvailableFields.find(strWorking) == 0) ListboxAvailableFields.add(strWorking);
            }
            catch { }
        }
        return;
    }
}
 
 
In the next article, I’ll tackle the tricky (and controversial) issue of how to add calculated fields and expressions to the report.
 
REGARDS