AX 7. How to create new excel template for general journal lines.

Open lines in Excel is a new cool feature in AX 7 that allows you to edit general journal lines using excel.

OpenInExcel

But unfortunately it supports only two account types out of the box: ledger account and customer account. Today we will create a new one for bank.

First of all, we need to create a new Data Entity. There is no difference between Data Entities for account types so I simply copied LedgerJournalLine entity, renamed it and changed AccountType range value to “Bank”.

DataEntity

Public collection name and public entity name properties should be changed, relation entity roles should be updated as well.

Next we need to create a new Excel template file.  I used Excel workbook designer in AX to generate new template based on the new Data Entity. I made my design pretty similar to existing templates for general journal.

Excel

Please note that template has two data entities: one for header and another for lines. As you can see I took the same header entity as other general journal templates has.

This template file should be added to AOT into resources node.

Finally, we need to create a new class. This class should be derived from DocuTemplateRegistrationBase and implement LedgerIJournalExcelTemplate.

 

using Microsoft.Dynamics.Platform.Integration.Office;

/// <summary>
/// The <c>BankJournalExcelTemplate</c> is the supporting class for the Bank Journal Template.
/// </summary>
class BankJournalExcelTemplate extends DocuTemplateRegistrationBase implements LedgerIJournalExcelTemplate
{
    // resource that contains xlsx file.
    private const DocuTemplateName ExcelTemplateName = resourceStr(BankJournalTemplate);
    // lines Data Entity
    private const EntityName LineEntityName = tableStr(BankJournalLineEntity);
    private const FieldName LineEntityJournalNum = fieldStr(BankJournalLineEntity, JournalBatchNumber);
    private const FieldName LineEntityDataAreaId = fieldStr(BankJournalLineEntity, dataAreaId);
    private const FieldName HeaderEntityName = tableStr(LedgerJournalHeaderEntity);
    private const FieldName HeaderEntityJournalNum = fieldStr(LedgerJournalHeaderEntity, JournalBatchNumber);
    // header Data Entity.
    private const FieldName HeaderEntityDataAreaId = fieldStr(LedgerJournalHeaderEntity, dataAreaId);

    public void registerTemplates()
    {
        this.addTemplate(OfficeAppApplicationType::Excel,
                         ExcelTemplateName,
                         ExcelTemplateName,
                         "@GeneralLedger:LedgerJournalLineEntryTemplateDescription",
                         "@MyLabels:BankLedgerDailyJournalTemplateName",
                         NoYes::No,
                         NoYes::No);
    }

    public boolean isJournalTypeSupported(LedgerJournalType _ledgerJournalType)
    {
	// only daily journals are supported in this template.
        return _ledgerJournalType == LedgerJournalType::Daily;
    }

    public DocuTemplateName documentTemplateName()
    {
        return ExcelTemplateName;
    }

    public Set supportedAccountTypes()
    {
	// Set with supported account types.
        Set accountSetTypes = new Set(Types::Integer);

        accountSetTypes.add(LedgerJournalACType::Bank);
        accountSetTypes.add(LedgerJournalACType::Ledger);

        return accountSetTypes;
    }

    public Set supportedOffsetAccountTypes()
    {
        // Set with supported offset account types.
        Set offsetAccountTypeSet = new Set(Types::Integer);

        offsetAccountTypeSet.add(LedgerJournalACType::Ledger);

        return offsetAccountTypeSet;
    }

    public boolean validateJournalForTemplate(LedgerJournalTable _ledgerJournalTable)
    {
        return LedgerJournalExcelTemplate::validateJournalForTemplate(_ledgerJournalTable, this);
    }

    public EntityName headerEntityName()
    {
        return HeaderEntityName;
    }

    public EntityName lineEntityName()
    {
        return LineEntityName;
    }

    public FieldName headerJournalBatchNumberFieldName()
    {
        return HeaderEntityJournalNum;
    }

    public FieldName headerDataAreaFieldName()
    {
        return HeaderEntityDataAreaId;
    }

    public FieldName lineJournalBatchNumberFieldName()
    {
        return LineEntityJournalNum;
    }

    public FieldName lineDataAreaFieldName()
    {
        return LineEntityDataAreaId;
    }

    public FilterCollectionNode appendHeaderEntityFilters(FilterCollectionNode _headerFilter, ExportToExcelFilterTreeBuilder _headerFilterBuilder)
    {
        return _headerFilter;
    }

    public FilterCollectionNode appendLineEntityFilters(FilterCollectionNode _lineFilter, ExportToExcelFilterTreeBuilder _lineFilterBuilder)
    {
	// creates excel filter to show only lines that have Bank account type
        // and Ledger offset account type
        FilterCollectionNode lineFilter = _lineFilterBuilder.and(
            _lineFilterBuilder.areEqual(fieldStr(CustInvoiceJournalLineEntity, AccountType), LedgerJournalACType::Bank),
            _lineFilterBuilder.areEqual(fieldStr(CustInvoiceJournalLineEntity, OffsetAccountType), LedgerJournalACType::Ledger));

        return _lineFilterBuilder.and(_lineFilter, lineFilter);
    }
}

As you can see, code is pretty straightforward and there is only one interesting method appendLineEntityFilters(). It uses FilterCollectionNode to create filters for template, so this template will show only journal lines that has account type bank and offset account type ledger.

When development is done, we need to reload system templates. Go to Common -> Office integration -> Document templates and click “Reload system templates”.

ReloadSystemTemplates.jpg

After that, our new template should appear under Open lines in Excel button.

OpenLinesInExsel.jpg

AX 7. How to add financial dimensions as separate columns to Data Entity.

Everyone who is working with financial dimensions in AX would like to see them in separate columns in Excel and today we will look how to achieve this for all Data Entities.

1

It’s also awesome because we have wizard that will do all the job for us!

In the Visual Studio under Dynamics AX > Addins select “Add financial dimensions for OData…”

2

Now we need to enter dimensions we want to expose. As you can see from a help text this dimensions should be setup in AX on Data Entities tab under General ledger > Chart of accounts > Dimensions > Financial dimension configuration for integrating applications.

3

Also we need to specify a model for new objects. Please note that the model should have reference to the Dimensions model, however you can add reference afterwards.

After clicking “Apply”, VS will show new project creation dialog. New project will be created. It will contain two Data Entity Extensions:

4

Please note that you cannot rename them because internal code works only with entity extensions that have name = Data Entity Name + “DimensionIntegration”.

Both entities has identical changes, the only difference is that one show default dimensions and another one ledger dimensions.

Let’s looks at DimensionSetEntity extension:

5

It has 3 new fields, one for each financial dimensions we specified in the wizard.

This fields are computed columns, each of them use same data method “getValueColumnForDimensionAttributeSql”.

6

It is quite interesting and has some smarties inside but hardcoded values as well.

That all we need to do. After compilation and synchronization we can open Data Entity in Excel and will see new fields in data connector designer.

7

How does it work? That the most exciting part. All the magic is inside DimensionAttributeValueSet table. If we look at it in AOT – nothing is really changed since AX 2012, but if we go to SQL management studio we will see bunch of new columns:

8

For each financial dimensions we have 2 columns: one contains value and another RecId.

Each computed column in Dimensions entity select value from this table.

So, if you are doing BI with 3rd party tools, now you can use  DimensionAttributeValueSet and DimensionAttributeValueCombination tables to query all financial dimensions directly.