Printing dynamic parameters (AX 2012 / SSRS)

In addition to parameters specified in data contracts, reports can also use queries. It’s very handy, because users can specify filters and sorting in exactly the same way as they’re used to from other places in Dynamics AX, they can use query expressions and so on.

ReportDialog
RangeSelection

A relative common request is printing parameter values on the report itself. It’s easy with parameters defined in data contracts – an expression like =Parameters!UsersToInclude.Value will do the job. But the same approach doesn’t work with dynamic parameters (that’s how query parameters get represented in SSRS).

I recommend the following approach. Use a report data provider class – very often, it’s already in place. Add a new data table to hold information about query ranges and expose it to the report. I’m using an existing table, TmpSysQuery, so we don’t have to discuss the design of the table. In processReport(), we extract ranges from the current query and save them to our temporary table.

class LedgerJournalPostControlDP extends SRSReportDataProviderBase
{
    …
    TmpSysQuery tmpSysQuery;
}
 
[SRSReportDataSetAttribute(tablestr(TmpSysQuery))]
public TmpSysQuery getTmpSysQuery()
{
    select tmpSysQuery;
    return tmpSysQuery;
}
 
public void processReport()
{
    …
    tmpSysQuery = MyQueryUtils::rangesToTable(this.query());
}

Because the logic for extracting ranges from a Query object may be needed from other places as well, I’ve put it into a separate class. This is the content of the method:

public static TmpSysQuery rangesToTable(Query _query)
{
    QueryBuildDataSource    qbds;
    QueryBuildRange         queryBuildRange;
    TmpSysQuery             tmpSysQuery;
    LabelType               tableLabel;
    int                     occurrence;
    int                     dataSourceNo;
    int                     i;
 
    if (!_query)
    {
        return tmpSysQuery;
    }
 
    for (dataSourceNo = 1; dataSourceNo <= _query.dataSourceCount(); dataSourceNo++)
    {
        qbds = _query.dataSourceNo(dataSourceNo);
        if (qbds.enabled())
        {
            occurrence = SysQuery::tableOccurrence(_query, qbds.table(), dataSourceNo);
            tableLabel = tableId2pname(qbds.table()) + SysQuery::tableOccurrenceText(occurrence);
 
            for (i = 1; i <= qbds.rangeCount(); i++)
            {
                queryBuildRange = qbds.range(i);
 
                if (queryBuildRange.value() && queryBuildRange.status() != RangeStatus::Hidden)
                {
                    tmpSysQuery.clear();
                    tmpSysQuery.DataSourceNo = qbds.uniqueId();
                    tmpSysQuery.TableLabel   = tableLabel;
                    tmpSysQuery.FieldLabel   = fieldId2pname(qbds.table(), queryBuildRange.field());
                    tmpSysQuery.RangeValue   = queryBuildRange.value();
                    tmpSysQuery.insert();
                }
            }
        }
    }
 
    return tmpSysQuery;
}

Then we add a new data set in our report and show query ranges in a tablix in exactly the same way as other data:

PrintedRanges

Aggregation of expressions in SSRS

Imagine you have an SSRS report with a tablix, which contains a calculated field. For example, there is a date field (from a data set) and a calculated field showing the difference between the given date and the current date:

TablixWithoutTotal

The Diff field is calculated by the following expression:

=DateDiff("d", Fields!GivenDate.Value, Today)

Now we want to add a field showing the sum of differences. First off all, add a new row below the Details group (i.e. the row with individual dates) using Insert Row > Outside Group – Below.

Tablix

The only question is how to implement the expression of Total diff. You probably know how to summarize values of a dataset field – by =SUM(Fields!SomeField.Value). But we’re not limited to fields, we can use almost any expression there. Therefore we simply put the expression calculating date difference to the SUM function:

=SUM(DateDiff("d", Fields!AccountingDate.Value, Today))

Indeed, the total difference is calculated as expected.

Result

Replacement groups in AX 2012

AX 2012 introduced a new type of form control called “Replacement group”. It’s very handy, nevertheless quite a few developers still don’t know about it or are not sure how to use it effectively.

This post is not going to details; the intention is rather to show something simple, though still from end to end.

Let’s say that we want to create a form showing released products. We create a form with InventTable as the data source and with a grid.

We’re especially interested in the Product field, so we drag it from the data source and drop it to the grid.

Form-Product

Notice that the type of the control is ReferenceGroup – we’ll talk about it very soon.

Without changing anything, we can run the form; it successfully displays product numbers:

Let’s say that users insist on working with product names instead of product codes. Maybe they should have used a better convention for product codes, but that’s another topic. Now we have meet the request.

What should we do? Providing an edit method that would display names and find IDs from names specified by users? No, it’s much simpler with reference groups. Open properties of the reference group and change the value of ReplacementFieldGroup property from AutoIdentification to ProductInformation:

ReplacementFieldGroup

Save the form and open it again – it now shows product names. It’s that simple!

ProductNames

If the product was editable (which is not normally the case in InventTable), you would also get a lookup and would be able to select (or type) product names:

LookupItemName

You could also override the lookup, if you don’t like the default one.

Now we should look more closely at how it works.

First of all, look at the Product field in InventTable. Its base type is Int64 and extended data type is EcoResProductRecId. Properties of the EDT shows that ReferenceTable = EcoResProduct, therefore the Product field contains record IDs of EcoResProduct table.

If we used the Int64Edit control to display the Product field, we would see just numbers, which wouldn’t be very useful.

RecIds

That’s why we have Reference group controls. The reference group in our form is bound to the Product field (set in ReferenceField property), so that’s what gets saved to database. But we don’t display it to users – instead of that, we use a replacement field group. Field groups are defined on tables in AOT – here we can see the groups of InventTable that we used in our example:

InventTable-fieldGroups

AX looks at the field (or even fields) contained in the field group and displays them instead of the record ID. If you change a value of the replacement field, AX finds the corresponding RecId and put it to the underlying field.

As you can see, reference groups allow you to change fields displayed to users by a simple property change. Also notice that it has zero effect to data stored in database – it still refers to a record ID.

There is one last thing I would like to mention, because it often confuses people. You may have a reference group for a worker showing the worker name (e.g. the Sales responsible person in Sales order form). The control uses the AutoIdentification field group of the HcmWorker table. The group contains a single field, Person, which is a RecId of DirPerson table. AutoIdentification group on DirPerson is empty, so where the name comes from? The trick is that DirPerson table inherits from DirPartyTable, which contains the Name field in its AutoIdentification group. AX deals with all this complexity for you.

XML DocType in X++

Let’s say that we want to use X++ to create an XML file with the following DocType definition:

<!DOCTYPE MyType SYSTEM "http://www.validome.org/check/test.dtd">

AX has Xml* classes (e.g. XmlDocument) for such purpose, but let’s build it with .NET classes first (you’ll see why in a moment). This is X++ code calling .NET classes through .NET Interop:

System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.AppendChild(xmlDoc.CreateDocumentType('MyType', null, 'http://www.validome.org/check/test.dtd', null));
info(xmlDoc.get_OuterXml());

The output is exactly what we want.

Now let’s try to rewrite it with native X++ classes:

XmlDocument xmlDoc = new XMLDocument();
xmlDoc.appendChild(xmlDoc.createDocumentType('MyType', '', 'http://www.validome.org/check/test.dtd', ''));
info(xmlDoc.xml());

Notice that it’s very similar to the previous example, but we had to replace null values with empty strings (”), because X++ doesn’t support null-valued strings.

The output is not correct in this case:

<!DOCTYPE MyType PUBLIC "" "http://www.validome.org/check/test.dtd"[]>

We can easily prove that the empty strings are to blame by rewriting our .NET-based example to use empty strings instead of nulls:

System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.AppendChild(xmlDoc.CreateDocumentType('MyType', '', 'http://www.validome.org/check/test.dtd', ''));
info(xmlDoc.get_OuterXml());

The output is identical to what we get from X++.

Let me explain what’s going on.

If you provide a value for publicId (the second parameter), the PUBLIC keyword is used, followed by the URI provided in publicId. That applies to empty URI as well, so we get:

<!DOCTYPE MyType PUBLIC "" (…)>

If you want to get SYSTEM keyword, publicId must be null, not “”. The fourth parameter, internalSubset, is a similar case.

Even if you aren’t familiar with .NET at all, you likely understand the difference between a null reference (x = null) and an empty object (x = new Object()). Unlike in X++, strings in .NET are objects (instances of System.String class) and therefore they can either refer to a value or be without any value at all (= null).

Now what we can do to set null values to XmlDocument.createDocumentType()? Well… nothing. All parameters are X++ strings and null isn’t a valid value for them. It would have to be designed differently, e.g. using strings wrapped in objects or providing an additional flag to say that the value should be ignored.

The workaround is simple – you can use .NET classes (from System.Xml namespace) as in the first code snippet.

Tested in AX 2012 R3.