Saturday, 22 August 2015

Generate SQL query for GL transactions [AX 2012]

In my last blog I did a demonstration of using an SQL query to build a PowerBI report for the general ledger transactions.

I hard coded the RecId for the financial dimensions. It might be cumbersome to build the query manual. So here I wrote a job you could run to get the query. It will spit out an infolog which you should be able to copy and paste.

static void navax_generateGLQuery(Args _args)
{
    DimensionAttribute  dimensionAttribute;
    str                 sqlStatement, dimNameStr, dimMaxStr, name;
    sqlStatement =
    @'
        SELECT
        GeneralJournalCube.*
        %1
        FROM GeneralJournalCube INNER JOIN
        (
            SELECT
            DAVC.RecId as CombinationId
            %2
            FROM
            DIMENSIONATTRIBUTEVALUECOMBINATION DAVC INNER JOIN
            DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION DAVGC ON
            DAVC.RECID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION INNER JOIN
            DIMENSIONATTRIBUTELEVELVALUE AS DAVL ON
            DAVL.DIMENSIONATTRIBUTEVALUEGROUP = DAVGC.DIMENSIONATTRIBUTEVALUEGROUP INNER JOIN
            DIMENSIONATTRIBUTEVALUE AS DAV ON DAV.RECID = DAVL.DIMENSIONATTRIBUTEVALUE INNER JOIN
            DIMENSIONATTRIBUTE DA ON DA.RECID = DAV.DIMENSIONATTRIBUTE
            GROUP BY DAVC.RECID
        )
        Dimensions ON
        Dimensions.CombinationId = GeneralJournalCube.LEDGERDIMENSION
    ';
    while select ViewName, RecId, Type, Name from dimensionAttribute
    {
        if (dimensionAttribute.Type == DimensionAttributeType::CustomList)
        {
            name = dimensionAttribute.Name;
        }
        else
        {
            name = dimensionAttribute.ViewName;
        }
        dimNameStr += strFmt(',%1\n', name);
        dimMaxStr += strFmt(',MAX(CASE WHEN DA.RECID = %1 THEN DAV.ENTITYINSTANCE ELSE NULL END) AS %2\n', dimensionAttribute.RecId, name);
    }
    sqlStatement = strFmt(sqlStatement, dimNameStr, dimMaxStr);
    info(sqlStatement);
}

Its a bit crude but at least you get all the dimensions.

image

No comments: