Tuesday, October 18, 2011

Computed View Columns in AX 2012

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 '';
}

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)));
}

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]'));
}
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":
If we go into SQL, this is what our view's design now looks like:

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!

19 comments:

  1. Hi Joris,
    does this mean that if you have a view in your production database with computed columns, you need to synchronise the views on a periodic basis to have the computed columns updated or am I wrong?
    Kind regards,
    Philippe

    ReplyDelete
  2. No, the view does not store the computed columns' values. It stores the calculation itself. Remember a view is just a pre-defined, optimized query. Your computed column (ie the computation) is part of the query.
    The method we add in AX is basically a string containing the computation that will be embedded in the query. Technically you could even created an AX method that returns "(SELECT 'hello world')" and add that as a computed column to your view. A view on the custTable would then appear in SQL's designer as:

    select CustAccount, Name, (SELECT 'hello world') from CustTable

    ReplyDelete
  3. Hi,

    Thanks for the post I was hoping you could help a little. Along the same tack I have created a method to sum a field on the SalesLine. This throws an SQL error when saving and I can't quite figure out why:

    public static server str SumAmount()
    {
    return SysComputedColumn::sum(SysComputedColumn::returnField( tableStr(SalesLineView),
    identifierStr(SalesLine),
    fieldStr(SalesLine, LineAmount)));
    }

    And the error is:
    SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]Column 'SALESLINE.SALESID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Thanks in advance,

    Howard

    ReplyDelete
  4. The issue is that by defining the SysComputedColumn::sum you are doing an aggregate function. If you don't specify what to group by, it will sum your whole query. This implies that the only fields you can select (ie fields that your view has) can be fields that are in the group by.

    For example:
    select SalesId, sum(LineAmount) from SalesLine

    wouldn't work. What salesID would it return if we're summing all saleslines which can have multiple salesids? So:

    select SalesId, sum(LineAmount) from SalesLine group by SalesId

    would work. This will return multiple records, one for each salesID, each with the sum of lineamounts for that salesid...


    Hope that helps.

    ReplyDelete
  5. Hi Joris,

    Thanks for you kind response and sorry for the delay in mine!

    The way I was using it was within a form joined to the sales header, so I assumed that the joing would restrict the results to one sales ID. In addition how do you set the group in the expression? Other than writing the SQL direct I can not see a way.

    I am currectly getting this SQL error:

    SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]Column 'SALESLINE.DATAAREAID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    which is worse because I would have thought that would be included as default?

    however thanks for you contiuned help

    ReplyDelete
    Replies
    1. Hi Howard,

      it's the same issue. You cannot select a column that is not in a group by, unless you use a function such as avg,sum,minof, maxof etc...
      You can just setup the group by on the datasource in your view.

      Delete
  6. Hi Joris,
    Thanks for the post. I want to add two string values returned from two different display methods on a table.Can you please guide how we can achieve them?
    Which function we should we use instead of SysComputedColumn::returnField(). Thanks!!!

    ReplyDelete
    Replies
    1. Priyanka,

      The point of this computed column functionality is to be able to add "code" to your SQL view, in SQL itself. Display methods are an AX concept and they exist only inside AX. So a display method cannot be added to the view as a computed column since they live in different worlds.
      You can review what your display method does and decide if you can re-write this to a SQL statement and try to add that as a computed column. It all depends on what you are doing in this display method of course.

      Delete
  7. HI jors,

    I have a queston I need tow ork on look up colums for the view like my view has custinvoicetranstable for each record I need the customername as i do it in dispaly method as below

    DirPartyTable::find(CustTable::find(SalesTable::find("Current custinvoice trans".salesid))).Name


    Without joing the table to datasource.Could you share me your ideas.

    Thanks,
    Sita.

    ReplyDelete
    Replies
    1. Beside the point a little bit, but nesting these find methods is very inefficient. Remember that the find will fetch ALL fields from the table in the select statement, plus you are effectively making 3 select statements meaning you have three round-trips to the database, each with too many fields in the select list.
      Also, the salesTable has a method called "customerName" which returns the sales order's customer name using only 1 query (if you already have the salestable record though).

      That being said, translating this into your view as a computed column you'd have to create the SQL query for it. Of course this depends on what datasources you already have in your view. But assuming you have the salestable datasource already, you could probably re-use the query used through the salesTable.customerName() method.

      Hope that helps.

      Delete
  8. Hi Joris,

    Your posts have helped tremendously.
    I have something a bit more complex.
    I'm trying to mimic a stored procedure that uses parameters.
    I'd like to display the data in a list page rather than a report.
    Is this even possible? What would be a better approach?


    CASE
    WHEN (CAST(IT.DATEFINANCIAL as date) > '1900-01-01 00:00:00.000' AND CAST(IT.DATEFINANCIAL as date) < @B_StartFiscal) OR
    (CAST(IT.DATEPHYSICAL as date) > '1900-01-01 00:00:00.000' AND CAST(IT.DATEPHYSICAL as date) < @B_StartFiscal AND ITP.ISPOSTED = 1)
    THEN IT.QTY
    END

    ReplyDelete
    Replies
    1. You can't have parameters but you could expose these date fields through computed columns and then put the view on a list page and filter the list page...

      Delete
  9. Hi Joris,

    i want to split the str field and that field i want to display in the view is it possible to do this, if possible please let me know.

    Thanks in Advance

    ReplyDelete
    Replies
    1. Although there is no method on the SysComputedColumn class in AX, there is a function in SQL to split strings ( http://technet.microsoft.com/en-us/library/ms187748.aspx ) so you can use that just fine, yes.

      Delete
  10. I have the following code
    static server str LineWeight()
    {
    return SysComputedColumn::sum( SysComputedColumn::multiply(SysComputedColumn::returnField(tableStr(jgOrderWeight),identifierStr(SalesLine_1),fieldStr(SalesLine,QtyOrdered)),
    SysComputedColumn::returnField(tableStr(jgOrderWeight),identifierStr(InventTable_1),fieldStr(InventTable,NetWeight))))
    ;
    }

    And I have a group by SalesLine_SalesID but it still gives me the error. SalesLine.SalesID is invalid in the select list. Any ideas? I already added the group by clause to my data srouce..

    ReplyDelete
    Replies
    1. I can't see your view of course, but remember if you are grouping by columns, the only columns returned are the columns you are grouping by, and any columns you're using aggregate functions on. My guess is that may be the issue here, that the columns you are trying to multiply are not part of the returned columns.

      Delete
  11. This comment has been removed by the author.

    ReplyDelete
  12. Hi Joris,

    Can I use SysComputedColumn not in view?
    I want to get value of:
    SUM((VENDPACKINGSLIPTRANS.QTY*PURCHLINE.PURCHPRICE) /PURCHLINE.PRICEUNIT)
    and I use a temporary table. How can I get the query for this case?

    Thanks,
    Henny

    ReplyDelete
    Replies
    1. Computed colums in AX are only for views... you'll have to decide whether to add a column to your temporary table, use an X++ display method, etc. based no your scenario.

      Delete