Oct 19, 2011
Filed under: #daxmusings #bizapps

One of the new exciting features in AX 2012 which has not received much attention in my opinion, is the possibility of adding computed columns to your views. In case you are unfamiliar with views, views are basically queries that are stored in the database, and the results of which can be accessed as if they were tables themselves. You can check Wikipedia and MSDN.

The basic premise of queries in AX is that the data model matches exactly what is in the AOT. You model a table in the AOT by adding fields, and then you automatically get a table object available in your code with properties representing those fields. Doing computations is impossible, however, since those technically add a virtual column to your SQL result set. This is very apparent if you think about aggregate functions. You can use aggregate functions in AX such as count(), max(), min() etc, but for this to work with the table buffer model, you have to perform the aggregate on a field of the return type you want. It basically re-uses an existing field on your model to be able to present the data back to you.

If you think about views strictly in a SQL sense, aggregates and calculations in virtual columns are a common thing. But they were impossible before AX 2012. When you query a table in AX, your result set consists of your table’s buffer object, with no properties for dynamics fields anywhere. With views however, you are modeling the query up front, so you know exactly what new columns will be added by your calculations. In fact, you have to manually add new columns to the view and then map them to fields from your query, so why not map them to code directly? Well, that’s exactly what we can do! And since those calculated columns are part of the resultset from SQL, you can sort, group, and filter on those computed columns!

Now, a few things to understand. One may think these computed column methods will behave like display methods, on the SQL side. But that is NOT the case. The methods you create for computed columns return a string, which contains the SQL statements that will be added as columns to your view’s query definition. So in that respect, they definitely do not behave like a SQL server-side display method. They are pieces of code that will be executed when AX synchronizes your view to the SQL database, using the output string of the methods and embeds them into the SQL view query. So, we’ll start by adding a new View on the SalesLine table. We’ll call it SalesLineView, and we just add the SalesLine table as a datasource.

So next, let’s try to add a simple computed column that returns the multiplication of the SalesPrice and the SalesQty columns. To start, we need to add a a new method, with modifiers static and server, which returns a str.

static server str NetAmount()
{
    return '';
}

</code> Alright. So now, to build up the string return value for our view’s computed column, we will use the class SysComputedColumn and its static methods. You’ll notice there’s a lot of them, but let’s start with our easy example of multiplying two columns. First, we’ll call SysComputedColumn::multiply which takes two expressions. Expression means this could now in its turn do again multiple calls to SysComputedColumn to create other calculations, etc. If you’ve done any type of calculated columns in Excel for example, you’ll see where this is going. Now, for each of the two expressions we want to multiply, all we want to get is a field from the datasource. For that, SysComputedColumn has a static method “returnField”, which takes the name of the view itself, the name of the datasource, and the name of the field on that datasource. This of course implies it supports multiple datasources, which it does. In any case, according to best practice, we won’t just hardcode the names in here, but use the precompiler *str methods where possible. Unfortunately there is no way of validate the name of the datasource, but we’ll just validate it’s at least a valid identifier. As for the view, tableStr() validates view names as well. The result will look like this (the code lines are long so I tried to put new lines and indentations in to make it more clear):

public static server str NetAmount()
{
    return SysComputedColumn::multiply(
        SysComputedColumn::returnField(
            tableStr(SalesLineView),
            identifierStr(SalesLine),
            fieldStr(SalesLine, SalesPrice)),
        SysComputedColumn::returnField(
            tableStr(SalesLineView),
            identifierStr(SalesLine),
            fieldStr(SalesLine, SalesQty)));
}

</code> Ok, so now that we have the method, let’s add it as a column on the view. Right-click on the view’s “Fields” node and select “Real Computed Column” to indicate this will be a column of type real (since price and qty are real).

On the properties of the new column, change the name to NetAmount (or whatever you want, it does not necessarily have to match the name of the method), and in the “ViewMethod” select the NetAmount method. If you new method doesn’t show up here, make sure it has been saved, and make sure your method is declared static server and returns a str type…

Now, as soon as you save this AX will synchronize your view to the database. So if you open your SQL manager and look at your view there, you should see the following in its design query:

That looks great. If you had problems synchronizing or saving your view, make sure you are not referring to the wrong datasource name, or have some type of type mismatch going on! (you will get the “Synchronize database” dialog which contains the SQL error that was generated… (click on the “Errors” tab to see the error) So remember that if your method compiles, that does not necessarily mean your view can be created without error! So using the *str() (tableStr, fieldStr, identifierStr, etc) precompiler functions will help you with that as much as possible.

Ok, how about something a bit more fancy? As you probably know, enums are stored as integers in the database. Why not “compute” them to the AOT definition name? Let’s use the SalesStatus field for that! I’ll explain what I’m doing, but you’ll have to figure out the details yourself, code example is below.

So, first, we iterate over all the enum values of the SalesStatus enum. to do this, we instantiate the DictEnum class and pass it the enum id of the SalesStatus enum. Next, we iterate the enum’s values (remember AX starts at 1, not 0!), and we fill up a Map class with two strings: as a key, the enum value we want to translate from (the enum’s integer value) and the value we want to translate to (the enum’s name in the AOT, which is the “symbol”). Finally, we call the SysComputedColumn::switch statement. Again we need to specify the name of the view and the name of the datasource. We also pass in our map, which will be used for all the “case” statements in the switch, and finally a default value, for which I return “[unknown value]”.

public static server str SalesStatusName()
{
    DictEnum dictEnum = new DictEnum(enumNum(SalesStatus));
    int enumIdx;
    Map valuesMap = new Map(Types::String, Types::String);

    for (enumIdx = 1; enumIdx <= dictEnum.values(); enumIdx++)
    {
        valuesMap.insert(int2str(dictEnum.index2Value(enumIdx)), SysComputedColumn::returnLiteral(dictEnum.index2Symbol(enumIdx)));
    }

    return SysComputedColumn::switch(
        SysComputedColumn::returnField(tableStr(SalesLineView), identifierStr(SalesLine), fieldStr(SalesLine, SalesStatus)),
        valuesMap,
        SysComputedColumn::returnLiteral('[unknown value]'));
}

</code> The thing to remember here is everything needs to be translated to strings, as you can see… returnLiteral() for string value constants (such as the unknown value, or the symbol name). Once done, we again add that to our Fields definition, this time selecting “String Computed Column”: <div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div>If we go into SQL, this is what our view’s design now looks like: <div class="separator" style="clear: both; text-align: center;"></div> In AX you can use the table browser on the view to check the results (or you can look at the view’s output in SQL). One of the reasons I opted to use “Symbol” (the AX enum name) is because of course there are no labels in SQL. So I’d rather use the developer name for the enum names. Anyway, those are details that have nothing to do with the exercise at hand…

So, this was a good start on the computed columns. There is a lot more to explore on the SysComputedColumn, and there’s always the possibility to add your own methods to this class to generate your own SQL query strings. Since this method is executed when the View is synchronized, it’s not as prone to SQL injections (since it doesn’t deal with user input directly), but depending on how you generate the query str, you can end up with a bad query in your SQL view. So developer beware!

 

There is no comment section here, but I would love to hear your thoughts! Get in touch!