SQL Query fun: Dynamics AX 2012 Main Account – Financial dimension read

Hi,

This is new blog on knowledge share, today just got chance to work with SQL queries; i was just exploring the dimension extract for one of our customer, i produce this sql query which show main account and financial dimensions associated with main account; i though it is worth to share with AX community also.

-- This script is designed to show the values in columns for better read
-- Deleting if tmp table already exists
Begin
drop
table #MainAccountDimensionTmp
End -- creating new Tmp table.
Begin CREATE TABLE #MainAccountDimensionTmp
(
MainAccount nvarchar(100),
DisplayValue nvarchar(100),
Name nvarchar(100)
) End 

-- selecting main accounts and all financial dimensions and storing into tmp table
Begin Insert into #MainAccountDimensionTmp (Name, DisplayValue, MainAccount)
select
ddv.NAME,
ddv.DISPLAYVALUE,
MAINACCOUNT.MAINACCOUNTID
from
MainAccountLegalEntity as MALE
join MAINACCOUNT on MAINACCOUNT.RECID = MALE.MAINACCOUNT
join DEFAULTDIMENSIONVIEW as ddv on MALE.DEFAULTDIMENSION = ddv.DEFAULTDIMENSION --where MAINACCOUNTid = 'test01'
end 

-- reading again from tmp table in way that values are showin in column.
-- please add as many dimesions as you want, like i have added 4 dimensins below.
Select
MainAccount,
MAX(
Case Name When 'Department' then DISPLAYVALUE ELSE '' END
) AS 'Department',
MAX(
Case Name when 'ICP' then DisplayValue ELSE '' END
) AS 'ICP',
MAX(
Case Name when 'MISC' then DisplayValue ELSE '' END
) AS 'MISC',
MAX(
Case Name when 'Expense_Class' then DisplayValue ELSE '' END
) AS 'Expense Class'
from
#MainAccountDimensionTmp Group by MainAccount


--select * from DEFAULTDIMENSIONVIEW

Query results:

 

 

Get default financial dimension values through X++ code

This topic is not new and we have lots of blogs available on it. Yasir Co-blogger also explained this quite well in his blog. The purpose to share this again here that I have did some minor modifications in his job to get both attributes (Value and Name) for Default dimension. Complete code is here.

static void GetDefaultDimensionAttributeName(Args _args)
{
    DimensionAttributeValueSetStorage    dimStorage;
    HcmPositionDefaultDimension          HcmPositionDefaultDimension;
    DimensionAttribute  DimensionAttribute;
    Counter                                               i;
    DimensionAttribute          dimAttr;
    DimensionAttributeValue     dimAttrValue;
    Common                      common;
    DictTable                   dictTable;
    str                         Name;
    str                         value;

    // get the dimension value from position    
    HcmPositionDefaultDimension = HcmPositionDefaultDimension::findByPositionLegalEntity(HcmPosition::findByPosition("000001").RecId,CompanyInfo::find().RecId);

    // make the dimension storage object    
    dimStorage = DimensionAttributeValueSetStorage::find(HcmPositionDefaultDimension.DefaultDimension);

    for (i=1 ; i<= dimStorage.elements() ; i++)
    {
        // get attribute select here.
        select firstonly dimAttrValue
        where dimAttrValue.RecId == dimStorage.getValueByIndex(i)
        join dimAttr
            where dimAttr.RecId == dimAttrValue.DimensionAttribute;

        if (dimAttr && dimAttrValue)
        {
            dictTable = new DictTable(dimAttr.BackingEntityType);
            common = dictTable.makeRecord();

            if (common.TableId)
            {
                select common where common.(dimAttr.KeyAttribute) == dimAttrValue.EntityInstance;
                name = common.(dimAttr.NameAttribute);
                value = common.(dimAttr.ValueAttribute);
            }
        }
        info(dimAttr.Name +"----" +value + "----"+name);
    }
}

output image

we can check the other examples on same topic from Yasir’s Blog – Microsoft Dynamics AX

Job to update customer financial dimension in dynamics ax 2012

Another usefull job for updating the Customer financial dimension in dynamics ax 2012. I hope, that would be useful for Techies.

 static void setFinancialDimensionToCustomer(CustAccount _custAccount)  
 {  
  CustTable custTable;  
  Struct struct = new Struct();  
  container ledgerDimension;  
  DimensionDefault DimensionDefault;  
  ;  
  struct.add('BookingChannel', '30');  
  struct.add('Carrier', '01');  
  struct.add('Department', '30');  
  struct.add('Destination', '01');  
  struct.add('Division', '30');  
  struct.add('Origin', '01');  
  struct.add('Product', '30');  
  ledgerDimension += struct.fields();  
  ledgerDimension += struct.fieldName(1);  
  ledgerDimension += struct.valueIndex(1);  
  ledgerDimension += struct.fieldName(2);  
  ledgerDimension += struct.valueIndex(2);  
  ledgerDimension += struct.fieldName(3);  
  ledgerDimension += struct.valueIndex(3);  
  ledgerDimension += struct.fieldName(4);  
  ledgerDimension += struct.valueIndex(4);  
  ledgerDimension += struct.fieldName(5);  
  ledgerDimension += struct.valueIndex(5);  
  ledgerDimension += struct.fieldName(6);  
  ledgerDimension += struct.valueIndex(6);  
  ledgerDimension += struct.fieldName(7);  
  ledgerDimension += struct.valueIndex(7);  
  ttsBegin;  
  DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);  
  custTable = CustTable::find(_custAccount, true);  
  custTable.DefaultDimension = DimensionDefault;  
  custTable.update();  
  ttsCommit;  
 }  

Financial Dimension – Dynamics AX 2012 Great Enhancement

How to Add a New Financial Dimension in AX 2012

Financial dimensions ia the great featue with lots of enhancement in Dynamics ax 2012, Now AX 2012 supports an unlimited number of financial dimension, and their setup and maintenance has been greatly simplified. This feature is real useful when we trying to see reports on Financial Dimensions. I will explain this blog by creating the new Financial dimenion “Grant” which is also the new feature in AX 2012.

First create the new code. Go to General Ledger > Setup > Financial dimensions > Financial dimensions and create a new financial dimension. After that there you’ll notice that the ‘Use values from’ can be set to <Custom dimension> as above, but also there is a long list existing dimensions as well.

If you use an existing field, then you won’t have to create any values for your new financial dimension – the system will use the values already present in the database, and if you’re going to use ‘Custom values’ for your new financial dimension the next step is to define the valid values which may be entered. Go to General ledger > Setup > Financial dimensions > Financial dimensions > Financial dimension values:

This blog is about creating new Dimension from existing entity so the dimension values can be used from database. like an existing abailable list of dimensions.

To set an entity to be dimensionable, create a view as directed below, Also, to integrate with the dimensions framework when deleting or renaming the natural key of the backing entity, you must write custom code on the backing table’s delete method, and also on either the update or renamePrimaryKey method. See CustTable for an example of the pattern these methods must follow

1. The view name must be DimAttribute[yourentityname]. For example, DimAttributeProjGrant.
2. The view must contain a root data source named BackingEntity that points to your backing table to identify a surrogate key and natural key.
3. The view must contain the following fields named exactly as follows: • Key – Must point to the backing entity’s SK field. For example, an int64 RecId field. • Value – Must point to the backing entity’s NK field. For example, a str30 GrantId field. • Name – Must point to the source of an additional description for the entity. For example, a str60 Description field

List of dimensionable entities are also cached on both the client and server, so we have to execute the following line of code within a job in order to load the new dimension:

static void Job2(Args _args)
{
DimensionCache::clearAllScopes();
info(‘done’);
}
Download the xpo from below link

To add the newly created Dimension to any form, we need to follow below link that explains it very well

http://www.intergen.co.nz/blog/tim-schofield/dates/2011/12/how-to-add-a-financial-dimension-in-ax-2012/