Inventory Value Query

Inventory Value Query

Inventory value in MS Dynamics AX 2012 reports depends on the InventTrans table. However, it doesn't directly retrieve data from InventTrans, rather it reads from a cascade of views based on it.
The main view upon which the inventory value reports is based, is InventValueReportView which has semi-calculated inventory values which can be used to calculate the inventory value for items as of a certain date with simple query filtering and aggregation functions.
The simple query below results in the inventory value (quantity and amount) of 2 items as of a date:
DECLARE    @ASOFDATE        DATE
DECLARE    @DATENULL        DATE
DECLARE    @DATEMAX        DATE
DECLARE    @YEARSTART        DATE
DECLARE    @DATAAREAID        NVARCHAR(4)
DECLARE    @PARTITION        BIGINT
   
SET        @ASOFDATE        = '2017-07-31'
SET        @DATENULL        = '1900-01-01'
SET        @DATEMAX        = '2154-12-31'
SET        @DATAAREAID        = 'YIAC'
SET        @PARTITION        = 5637144576
   
SELECT
    IVR.PARTITION
,    IVR.DATAAREAID
,    IVR.ITEMID
,    SUM(IVR.QTY) QTY
,    SUM(IVR.AMOUNT) AMOUNT
FROM    
    INVENTVALUEREPORTVIEW IVR
WHERE    
    1 = 1
AND    IVR.PARTITION = @PARTITION
AND    IVR.DATAAREAID = @DATAAREAID
AND    IVR.TRANSDATE < @DATEMAX
AND    IVR.TRANSDATE < @ASOFDATE
AND    IVR.ITEMID IN ('PHABN0095N', 'JJCRD4244030WN')
GROUP BY
    IVR.PARTITION
,    IVR.DATAAREAID
,    IVR.ITEMID
ORDER BY
    IVR.ITEMID
   
You can see from the previous query that by simply putting a date filter (in the where clause) and aggregating by ItemId we get the result of the inventory value for the items in the where clause (or any items you choose for that matter).
The use of the PARTITION and DATAAREAID fields are for the purpose of enhancing the performance since they are used in the table index.
While this is enough to get the inventory value, it is almost always required to get more information along with the item (item name, dimensions, group ... etc.). In this aim, we utilize joins between the related tables to the item table in order to get the required data.
A very interesting requirement here is to emulate the function of dimension display in Dynamics AX can be implemented by using a technique that combines switching (CASE), aggregation and nesting to achieve the result.
The idea is to join the query records with their corresponding inventory dimensions InventDim. The CASE statement will be used to choose whether to project the inventory dimension field value or a blank instead (emulating dimension display is discussed in another post).
The final result is a query that implements the inventory value calculation algorithm, dimension display emulation, and joins with product information tables to get a complete report on inventory value, as in the below example:
DECLARE    @ASOFDATE            DATE            = '2017-08-29'
DECLARE    @DATENULL            DATE            = '1900-01-01'
DECLARE    @DATEMAX            DATE            = '2154-12-31'
DECLARE    @DATAAREAID    NVARCHAR(4)        = 'YIAC'
DECLARE    @PARTITION        BIGINT            = 5637144576
DECLARE    @BYIT                BIT                = 1
DECLARE    @BYWH                BIT                = 0
DECLARE    @BYLC                BIT                = 0
DECLARE    @BYBH                BIT                = 0
DECLARE @BYSN             BIT                = 0
DECLARE    @SHOWZERO            BIT                = 1
DECLARE    @DIMATTRSEG       BIGINT            = 5637145326
DECLARE    @DIMATTRDPT       BIGINT            = 5637145327
DECLARE    @DIMATTRDIV       BIGINT            = 5637145328
DECLARE    @DIMATTRPCP       BIGINT            = 5637145329

SELECT
    IVR.PARTITION
,    IVR.DATAAREAID
,    IVR.ITEMID
,    TXT.NAME
,    PCP.DISPLAYVALUE PRINCIPAL
,    IVR.WAREHOUSE
,    IVR.LOCATION
,    IVR.BATCH
,    IVR.SERIAL
,    IVR.QTY
,    IVR.AMOUNT
/*
    Nesting the query so that grouping occurs only in the inner query
and joins are done on the outer query
*/
FROM    (
SELECT
    IVR.PARTITION
,    IVR.DATAAREAID
/*
    Dimension display emulation with tracking/storage dimension fields
*/
,    CASE
    WHEN    @BYIT = 1 THEN IVR.ITEMID
    ELSE    ''
    END        ITEMID
,    CASE
    WHEN    @BYWH = 1 AND SDG.WAREHOUSE = 1 THEN DIM.INVENTLOCATIONID
    ELSE    ''
    END        WAREHOUSE
,    CASE
    WHEN    @BYLC = 1 AND SDG.LOCATION = 1 THEN DIM.WMSLOCATIONID
    ELSE    ''
    END        LOCATION
,    CASE
    WHEN    @BYBH = 1 AND TDG.BATCH = 1 THEN DIM.INVENTBATCHID
    ELSE    ''
    END        BATCH
,    CASE
    WHEN    @BYSN = 1 AND TDG.SERIAL = 1 THEN DIM.INVENTSERIALID
    ELSE    ''
    END        SERIAL
/*
    Quantity and amount aggregated as per the dimension display grouping fields
*/
,    SUM(IVR.QTY) QTY
,    SUM(IVR.AMOUNT) AMOUNT
FROM    
    INVENTVALUEREPORTVIEW IVR
INNER JOIN INVENTDIM DIM
    ON    DIM.PARTITION = IVR.PARTITION
    AND    DIM.DATAAREAID = IVR.DATAAREAID
    AND    DIM.INVENTDIMID = IVR.INVENTDIMID
INNER JOIN ECORESTRACKINGDIMENSIONGROUPITEM TDR
    ON    TDR.PARTITION = IVR.PARTITION
    AND    TDR.ITEMDATAAREAID = IVR.DATAAREAID
    AND    TDR.ITEMID = IVR.ITEMID
INNER JOIN ECORESSTORAGEDIMENSIONGROUPITEM SDR
    ON    SDR.PARTITION = IVR.PARTITION
    AND    SDR.ITEMDATAAREAID = IVR.DATAAREAID
    AND    SDR.ITEMID = IVR.ITEMID
/*
    Storage dimension group finanacial iventory field setup
*/
INNER JOIN (
SELECT    
    PVT.STORAGEDIMENSIONGROUP
,    SUM(ISNULL(PVT.[16], 0)) [SITE]
,    SUM(ISNULL(PVT.[6], 0)) WAREHOUSE
,    SUM(ISNULL(PVT.[3], 0)) LOCATION
FROM
    ECORESSTORAGEDIMENSIONGROUPFLDSETUP SDG
PIVOT (SUM(SDG.ISFINANCIALINVENTORYENABLED) FOR SDG.DIMENSIONFIELDID IN ([3], [6], [16], [20001], [20002])) PVT
WHERE
    PVT.ISACTIVE = 1
GROUP BY
    PVT.STORAGEDIMENSIONGROUP
)    SDG
    ON    SDG.STORAGEDIMENSIONGROUP = SDR.STORAGEDIMENSIONGROUP
/*
    Tracking dimension group finanacial iventory field setup
*/
INNER JOIN (
SELECT    PVT.TRACKINGDIMENSIONGROUP
,        SUM(ISNULL(PVT.[2], 0)) BATCH
,        SUM(ISNULL(PVT.[5], 0)) SERIAL
FROM    ECORESTRACKINGDIMENSIONGROUPFLDSETUP TDG
PIVOT    (SUM(TDG.ISFINANCIALINVENTORYENABLED) FOR TDG.DIMENSIONFIELDID IN ([2], [5])) PVT
WHERE    PVT.ISACTIVE = 1
GROUP    BY PVT.TRACKINGDIMENSIONGROUP
)    TDG
    ON    TDG.TRACKINGDIMENSIONGROUP = TDR.TRACKINGDIMENSIONGROUP
WHERE    
    1 = 1
/*
    1 = 1 is used just as a place holder so that other conditions in the
    where clause can be commented out without breaking the query
*/
AND    IVR.PARTITION = @PARTITION
AND    IVR.DATAAREAID = @DATAAREAID
AND    IVR.TRANSDATE < @DATEMAX
AND    IVR.TRANSDATE < @ASOFDATE
GROUP BY
    IVR.PARTITION
,    IVR.DATAAREAID
/*
    Grouping by dimension display settings combined with tracking/storage dimension group setup
*/
,    CASE
    WHEN    @BYIT = 1 THEN IVR.ITEMID
    ELSE    ''
    END
,    CASE
    WHEN    @BYWH = 1 AND SDG.WAREHOUSE = 1 THEN DIM.INVENTLOCATIONID
    ELSE    ''
    END
,    CASE
    WHEN    @BYLC = 1 AND SDG.LOCATION = 1 THEN DIM.WMSLOCATIONID
    ELSE    ''
    END
,    CASE
    WHEN    @BYBH = 1 AND TDG.BATCH = 1 THEN DIM.INVENTBATCHID
    ELSE    ''
    END
,    CASE
    WHEN    @BYSN = 1 AND TDG.SERIAL = 1 THEN DIM.INVENTSERIALID
    ELSE    ''
    END
)    IVR
/*
    Join with InventTable, EcoResProduct and EcoResProductTransaction to get the item name
*/
INNER    JOIN INVENTTABLE ITM
    ON    ITM.PARTITION = IVR.PARTITION
    AND    ITM.DATAAREAID = IVR.DATAAREAID
    AND    ITM.ITEMID = IVR.ITEMID
INNER JOIN ECORESPRODUCT PRD
    ON    PRD.PARTITION = ITM.PARTITION
    AND    PRD.DISPLAYPRODUCTNUMBER = ITM.ITEMID
INNER JOIN ECORESPRODUCTTRANSLATION TXT
    ON    TXT.PARTITION = PRD.PARTITION
    AND    TXT.PRODUCT = PRD.RECID
    AND    TXT.LANGUAGEID = 'EN-US'
/*
    Join with DimensionAttributeValueSetItemView to get the related financial dimension code (principal)
    Other financial dimensions can be joined in the same manner by just
chaning the dimension attribute parameter (e.g. @DIMATTRDIV)
*/
LEFT OUTER JOIN DIMENSIONATTRIBUTEVALUESETITEMVIEW PCP
    ON    PCP.DIMENSIONATTRIBUTEVALUESET = ITM.DEFAULTDIMENSION
    AND    PCP.DIMENSIONATTRIBUTE = @DIMATTRPCP
WHERE
    1 = 1
ORDER BY
    IVR.ITEMID

Applying changes and client readiness

In the best practice of development in MS Dynamics AX™ you would normally work on a development environment, test your customizations on a test environment and eventually apply the customization on the production environment.

It is obvious that following the best practice minimizes (I dare say eliminates) the risks involved in making changes to the system. However, two small issues should be kept in mind when you apply the changes to the production environment:

1. The client object cache.
2. The client usage data.

These two issues are related to changes to classes and to changes to the packed objects respectively. To further explain, when you apply changes to forms for example, you can make the client ready to use these changes by simply closing the client and running it again. But when your changes involve:

1. Changes to classes.
2. Changes to the CurrentList local macro (used when packing report or query parameters).

you need to do some extra work to make the client ready (i.e. get the client to “know about” the new changes).

Symptoms of the problems caused by applying such changes are:

1. The system gives results on the client as if nothing have changed (or even sometimes issue an error message which indicates that your code is missing).
2. You get a run-time error in the pack/unpack methods of a report or a class.

To resolve these issues you need to do the following:

1. Clear the client’s object cache: Go to the users application data folder (C:\Users\<User Id>\AppData\Local) on Vista, Windows 7 or Windows Server 2008, (C:\Documents and settings\<User Id>\Local Settings\Application Data) on XP. Delete the file ax_{GUID}.auc, where GUID is some system generated alphanumeric Id. Note: If you are using terminal services, this folder will be found on the terminal server.
2. Clear the client’s usage data: On Dynamics AX, go to the user options (Dynamics menu –> Tools –> Options). Find the usage data button, click on it and another form should appear. In the “All usage data” tab, find the object that you changed and delete its corresponding usage data. Or you can simply go to the “General” tab and reset all usage data (which I don’t recommend).

One remark that I have to point out here is that these issues (to the best of my knowledge) should be dealt with one client at a time. I currently don’t know how this solution can be mass-deployed. Luckily I didn’t need to do that for more than a few clients.

A tip on BI in MS Dynamics AX™

Recently I’ve been working more closely on BI concepts and SSAS analysis cubes. One thing I can tell you is that when working on analysis cubes it is a good idea to test your cubes (or process it) using a small sample of the data.

Working on a large database (similar to those we find in production environments) can give you a feeling of how the performance would really be. However, in development, this can give you a hard time, because every time you process a cube it takes considerable time during which you’d mostly be idle.

Luckily in MS Dynamics AX™ we have a very easy way to delete the transactions of the system (keeping the master data intact). So, you can export some of the transactions on which you’re going to do your analysis (preferably data whose results are already know to you) , delete the transactions and import the data back in to the system.

For those who haven’t yet used this class, you can find it in the AOT, expand classes and locate the class SysDatabaseTransDelete. Right click on it and click on open on the context menu.

MS Dynamics CRM connector for MS Dynamics AX

I have learnt from MS Dynamics World that Microsoft is going to publish a connector that links Dynamics CRM with Dynamics AX.

This has to be good news for many of the partners and customers out there who had to struggle with the limitations of Dynamics AX’s CRM module, I know it is for me :)

With the CRM module in Dynamics AX, you have full integration with the system, however you’ll end up with many of your wishes for a complete CRM solution unfulfilled.

On the other hand, having both Dynamics CRM and Dynamics AX will throw you in the hassles of integration which might either fail if it is not designed, developed and implemented properly, or it might surpass your cost line or even shatter it.

Having a connector that links the two systems will reap you the benefits of the two worlds, the richness of Dynamics CRM as a leading CRM solution and the hopefully seamless integration with Dynamics AX.

I can’t wait to explore the possibilities that this offering opens the doors for. Experimenting with such a software “merger” can predict the immergence of several scenarios that were on the wish list of many partners and customers for quite sometime.

To ready the article MS Dynamics World, follow this link. (Login required to read the full article)

Using TextBuffer class to decode text

It is sometimes of great benefit to be able to identify certain patterns in a text and be able to extract certain information depending on the patterns found. X++ provides this by the power of the use of regular expressions.

For example, the match function in Dynamics Ax can be used to decide whether an item’s name contains a string that starts with a number followed by a string that contains at least two commas.
This pattern can be formulated as a regular expression in the X++ syntax as “:d+.*, .*, .*”. This patter is used in the example below.

 

match(":d+.*, .*, .*","14\", TV, Fixed stand")

The match function in the previous example returns 1, which means that the pattern was found.

The TextBuffer class is founded on the mechanism of the match function. Basically, the matching is triggered by calling the find method. If the pattern is found, the TextBuffer object stores two values that facilitate access to the matching substring, those being the match position (the beginning of the matching substring) and the match length (the length of the matching substring).

The following code example shows how to use the TextBuffer class to identify the presence of three different patterns with special delimiters in an item’s description, namely:

• conf|.*|: Substring starting with “conf|” and ending with “|”.
• col|.*|: Substring starting with “col|” and ending with “|”.
• size|.*|: Substring starting with “size|” and ending with “|”.

These special delimiters are used in a way to locate the configuration, color and size of an item.

The following three steps describe how this is done:

1. Call the find method to trigger the matching.
2. Use the matchPos and matchLen methods to locate the matching substring (if any).
3. Extract the matching substring.
4. Display the matching substring.
5. Replace the matching substring with an empty string to remove it from the original text.

Finally, the remaining string (which is the description of the item) is displayed. Note that the strrtrim function is used to remove any trailing spaces from the description of the item.

  

Render financial statements into graphs

One of our customers required a visualization of the financial data generated by the system to make it easier for top management to see the trends and indications from the data.

We decided to render the output of the financial statement reports into a spreadsheet activex control similar to that of the "Graph of balance" which could be found in the chart of accounts details form (Balance --> Graph of balance).

Our approach goes as follows:

1. Add a new output type to the financial statement.
2. Create a new form that displays the graph generated by the rendering class.
3. Create a class that performs the rendering.
4. Modify the LedgerBalanceSheetDimPrint class so that the new form is called when the chosen output type is "Graph".

Adding a new output type

This is simply done by adding a new element to the BaseEnum that appears in the form (namely, DimensionsOutputType).

Form to display the generated graph

You can duplicate the "Graph of balance" form, or if you're comfortable with using the Graphics class then you can create a new form. The whole sotry lies in the loadData method of the graph object that you create. You can choose whether columns should be on the XAxis and rows on the YAxis or vice versa (it is even better to parameterize this option) in a way such that financial statment column labels and row labels are used as axis labels (make sure to truncate the names because they are too long and would impair the display of the graph).

Class to manage the graph rendering

We have created a new class similar to LedgerBalanceSheetDimFile (the class used to export the financial statement into files). This way it becomes easier to manage the output of the graph in terms of graph options with relation to the financial statement parameters (which column goes to which axis, what type of graph ... etc.).

Modify the LedgerBalanceSheetDimPrint class

This is where everything starts. However, the changes that need to be made here are minimal (which is very convenient in the case of an upgrade). The changes that will be made to this class occur in the run method and a new method to call the form that displays the graph.

In the run method add a new case to the switch statement that decides what output type has the user chosen for the financial statement.

The runGraph method should be similar to the runReport method the only difference being that it calls a form instead of a report.

Now, your financial statement is ready to display a simple financial statement in nice looking graph.

Please follow this link for the code sample

Introduction

Hi all,

My name is Fato. I work as MS Dynamics AX developer since 2007. During this time, I have been through good and bad days, and in the bad days I was many times saved by the knowledge of a generous person whom I didn't know and who is willing to share. I have grown tremendous appreciation for them and thought that the least I could do to honor their work is to extend it myself. Hence, I'm starting this blog for the hope that someday, somewhere, someone will make use of what I have to share.

Regards,