Sunday, February 05, 2017

Read a given segment value for a given account from the GL

Assuming that one has to read all the values from a MainAccount '20151026' where the Employee dimension 'D6_Employee' = 'E0028'

Below is a sample job for the same 
static void Job10(Args _args)
{
    GeneralJournalAccountEntry          GJAE;
    GeneralJournalEntry                 GJE;
    FiscalCalendarPeriod                FCP;
    SubledgerVoucherGeneralJournalEntry SLVGJE;
   
    DimensionAttributeValueGroupCombination DAVGC;
    DimensionAttributeLevelValue            DALV;
    DimensionAttributeValue                 DAV;
    DimensionAttribute                      DA;
    SPYAmount                               claimAmount;
    HcmPersonnelNumberId                    personnelNumber = HcmWorker::find(5637146086).PersonnelNumber;
   
    while select *
    from GJAE
    join GJE   
    where GJE.RecId == GJAE.GeneralJournalEntry       
        && GJE.AccountingDate >= mkDate(1,1,2017)
        && GJE.AccountingDate <= mkDate(31,1,2017)
        && GJAE.MainAccount == MainAccount::findByMainAccountId('20151026').RecId
    join SLVGJE
        where SLVGJE.GeneralJournalEntry == GJE.RecId
        && SLVGJE.VoucherDataAreaId ==  curext()  
        //reading the individual segments of the entry
    join DAVGC
        where DAVGC.DimensionAttributeValueCombination == GJAE.ledgerDimension
    join DALV
        where DALV.DimensionAttributeValueGroup == DAVGC.DimensionAttributeValueGroup                   
        && DALV.DisplayValue == personnelNumber
    join DAV
        where DAV.RECID == DALV.DimensionAttributeValue
    join DA
        where DA.RecID == DAV.DimensionAttribute       
        && DA.Name == 'D6_Employee'        
    {       
        claimAmount += GJAE.AccountingCurrencyAmount;
    }
}

Saturday, January 28, 2017

AX 2012 Financial Dimension Storage

For the sake of an example, let's consider the following LedgerDimension value:
1200120-INR2001

The above display value has multiple parts, each seperated by a (-) hyphen. Lets call each part a Segment and the value of a segment as the segment value.

When we look at the display value of a dimension then only the segmentValues are visible, however the segment names are not known. The segment names are derived from the account structure that has been enabled on the concerned account.

As it is possible to change the account structure in AX, it is also possible that the segment names currently active are different from the ones which were active when a particular LedgerDimension value was entered.

The details below will help us understand this concept better.

Firstly dimensions are of two types: -
  1. Lookup Dimenions: These are lookup to an existing master in AX. The dimensions are stored in two tables namely, DimensionAttribute (for dimension name) and DimensionAttributeValue (for dimension values, There is a EntityInstance field in this table. That’s the relation to the value original table.) 
  2. Custom : These are custom defined values and do not exist elsewhere within AX. These are stores in two custom tables FinancialTagCategory (for dimension name) and DimensionFinancialTag (for dimension values)

As we know in AX the dimension combinations are uniquely created and assigned a RecID which is then reused on records with the same dimension value. The dimension combinations are of two types
  1. DefaultDimension : Default dimension is stored in 
    1. DimensionAttributeValueSet : A grouping of unique values denoting a combination.
    2. DimensionAttributeValueSetItem : individual items in a combination 
Consider the below SQL statement:

select DAVSI.DimensionAttributeValueSet, DA.Name, DAVSI.DisplayValue
from DimensionAttributeValueSetItem DAVSI
inner join DimensionAttributeValue DAV
    on DAV.RecID = DAVSI.DimensionAttributeValue
inner join DimensionAttribute DA
    on DA.RecID = DAV.DimensionAttribute

the above statement will return each CombinationID, SegmentName, SegmentValue
  1. LedgerDimension: Ledger dimension is stored in 
    1. DimensionAttributeValueCombination: Stores a combination of unique values denoting a ledger dimension.
    2. DimensionAttributeValueGroup: Stores dimension group
    3. DimensionAttributeValueGroupCombination : Store relation of DimensionAttributeValueGroup and DimensionAttributeValueCombination
    4. DimensionAttributeLevelValue: Stores dimension value of ledger dimension
Consider the below SQL statement

select DAVGI.DimensionAttributeValueCombination, DA.Name, DALV.DisplayValue
from dimensionAttributeValueGroupCombination DAVGI
inner join dimensionAttributeLevelValue  DALV
    on DALV.DimensionAttributeValueGroup = DAVGI.DimensionAttributeValueGroup
inner join dimensionAttributeValue DAV
    on DAV.RECID = DALV.DimensionAttributeValue
inner join DimensionAttribute DA
    on DA.RecID = DAV.DimensionAttribute
order by DAVGI.DimensionAttributeValueCombination, DALV.Ordinal

the above statement will return each CombinationID for LedgerDimension, segmentName,  segmentValue

Sunday, November 20, 2016

SSRS Report UI Builder


UI Builder report is used to manipulate the request form rendered by the AX framework before a report is executed. The request form for a report is generated by the framework based on the meta data provided by the contract class.

 Below are the steps that are required to manage the User Interface of a report
  1. Create a UI builder class by extending the class SrsReportDataContractUIBuilder.
  2. We will have to create event handlers for the control on the report contract. 
  3. The event handlers created in step 2 will have to be attached to the control created  by the reporting framework using the PostBuild method of the UIBuilder class.
  4. The contract class has to be modified to include a reference to the UIBuilder class. This is done by including the SysOperationContractProcessingAttribute on the contact class declaration.
    [
        DataContractAttribute
        ,SysOperationContractProcessingAttribute(classstr(STPLedgerBalanceReportUIBuilder))
    ]
     

class STPLedgerBalanceReportUIBuilder extends SrsReportDataContractUIBuilder
{
    STPLedgerBalanceReportContract contract ;
}

public void build()
{
    super();

    Contract      = this.dataContractObject();  
}

public void lookup(FormStringControl _control)
{
    container cnt;

    Query query = new Query();
    QueryBuildDataSource    qbds, qbdsCI;
    QueryBuildRange         range, rangeDA;

    qbds = query.addDataSource(tableNum(USERDATAAREAFILTER));
    range = qbds.addRange( fieldNum( UserDataAreaFilter, User) );
    range.value( SysQuery::value( curUserId())) ;

    qbdsCI = qbds.addDataSource( tableNum( CompanyInfo) );
    qbdsCI.addLink( fieldNum( UserDataAreaFilter, DataArea) , fieldNum( CompanyInfo, DataArea) );
    qbdsCI.joinMode(JoinMode::InnerJoin);

    if ( Global::hasTableAccess( tableNum( STPRowDefinitionLine) , AccessType::Delete) == false )
    {
        rangeDA = qbds.addRange( fieldNum( UserDataAreaFilter, DataArea) );
        rangeDA.value( SysQuery::value( curext()) );
    }

    qbds.fields().clearFieldList();
    qbds.fields().addField(fieldNum(USERDATAAREAFILTER, DataArea));
    qbdsCI.fields().addField ( fieldNum( CompanyInfo,Name) ) ;


    SysLookupMultiSelectGrid::lookup(query, _control, _control, cnt);
}

public void postBuild()
{
    DialogField     companyList;
    super();

    companyList = this.bindInfo().getDialogField( this.dataContractObject(),  methodStr(STPLedgerBalanceReportContract, parmCompanyList) ) ;

    companyList.registerOverrideMethod(
          methodStr(FormStringControl, lookup),
          methodStr(STPLedgerBalanceReportUIBuilder,Lookup),
          this);
}

 public void getFromDialog()
{  
    super();
}

Thursday, November 17, 2016

AX2012 invoke menuitem using X++ code

Below is the code to invoke a menu item on a click on command button.

The command button was required to execute some x++ code and then trigger the menu item, hence the menu item was not directly inserted as menu option instead the code was written on the click event.

   Args    _args = new Args();   

    new MenuFunction(identifierstr(STPLedgerBalanceCurrencyTranslation), MenuItemType::Action).run(_args);

Tuesday, November 01, 2016

AX2012 EP Pass Record context beween EP forms

Requirement : Open one modal form from another and pass the context.

The best way to pass the context is using the Dataset init method of the relevant table. In my case i had to pass the InventJournalTrans table's reference from a grid to a new form, where the details would be entered. Below are the steps for the same.

Step 1: Create the init method in the relevant table under the dataset.



The definition of the init method would be as follows:
public void init()
{
    Common              callerRecord = element.args().record();
    super();

    if ( callerRecord.TableId == tablenum(InventJournalTrans) )
    {
      this.query((EPQuery::makeRecIdQuery(this.query(),tablenum(InventJournalTrans),callerRecord.RecId)));
    }
}

Once the above is done we are sure that a filter would be imposed on the database if the calling form is sending the record context of the InventJournalTrans table.

Step 2: Is to ensure that a record context is passed from the source. We first create a new sharepoint page with a new web control and render a presentation that we want to be displayed.

A url type of WebMenuItem would be required to point to the newly created sharepoint page and the new userControl created. Create a new webMenuItem as shown below




Step 3: Attach the menu item in the parent UserControl such that it has the right context. In my case it happens to be a grid control which has a toolbar attached to it. So simply drag the url menu item to the relevant webmenu, which in this case is AFZEPInventJournalLineToolbar