Creating a dynamic financial dimension lookup

The attached project demonstrates how to build a dynamic financial dimension lookup in AX2012.
Once the dimension attribute is selected, then the dimension value lookup will display the correct values based on the selected dimension attribute.
This could be used, for example to build for security policy by dimension value where you could set which dimensions that users should be able to see.

Microsoft white paper on securing data by dimension value using XDS

Download the xpo project here

Restricting trial balance inquiry/report based on the financial dimensions via security policy

When trying to restrict trial balance inquiry/report based on the financial dimensions via security policy,  you would need to restrict on DimensionFocusBalance table.
The table would be used whenever the summary trial balance is calculated.

However in AX2012, because the standard codes use double not-exists joins in LedgerTrialBalanceDP.populateTmpTransSummary, the security policy restriction doesn't work properly.

One of the way to make it work is to get the codes from R3 onward, especially for the LedgerTrialBalanceDP class, and change the populateTmpTransSummary method.
You should see this statement if (wasDimCriteriaCreated) multiple times in the method, which depends on if the operator put dimension criteria on the report dialog or not.
So the idea is to not do the dimension filter (through those double not-exists joins) for all of the insert_recordset statements, and use another table buffer to hold the records (at this point, the dimensionFocusBalance records would have been restricted by the security policy).
After all records have been inserted, then do another insert_recordset to the correct temporary table buffer while doing the double not-exists joins to filter by the dimension criteria entered on the report dialog.

I opted to create a new method like below and change the processReportSummary method to call my new method instead:

private void ECL_populateTmpTransSummary(
    LedgerTrialBalanceStagingTmp _ledgerTrialBalanceStagingTmp,
    DimensionHierarchy _primaryDimensionSet,
    Map _dimensionRangeMap,
    TransDate _startDate,
    TransDate _endDate,
    TransDate _dividedStartDate,
    TransDate _dividedEndDate,
    boolean _includeOpeningInDetail,
    boolean _includeClosing)
{
    LedgerTrialBalanceTmpFocus          tmpFocus;
    LedgerTrialBalanceTmp               ledgerTrialBalanceTmpLocal;
    DimensionAttributeValueCombination  dimensionAttributeValueCombination;
    DimensionAttributeLevelValueView    dimensionAttributeLevelValueView;
    date                                periodStartDate;
    FiscalPeriodType                    opening = FiscalPeriodType::Opening;
    FiscalPeriodType                    operating = FiscalPeriodType::Operating;
    FiscalPeriodType                    closing = FiscalPeriodType::Closing;
    DimensionFocusBalance               dimensionFocusBalance;
    DetailSummary                       summary = DetailSummary::Summary;
    NoYes                               yes = NoYes::Yes;
    LedgerTurnoverTmpDimensionCriteria  tmpDimCriteria;

    this.setUserConnection(tmpFocus);
    this.setUserConnection(tmpDimCriteria);
    this.setUserConnection(ledgerTrialBalanceTmpLocal);

    periodStartDate = FiscalCalendars::findOpeningStartDateByDate(CompanyInfo::fiscalCalendarRecId(), _startDate);

    // Get the temporary dimension criteria
    this.populateSummaryDimensionCriteria(tmpDimCriteria, _primaryDimensionSet, _dimensionRangeMap);

    // Insert all operating trans records
    insert_recordset ledgerTrialBalanceTmpLocal
        (AccountingDate,
        LedgerDimension,
        DetailSummary,
        AmountDebit,
        AmountCredit,
        PostingLayer,
        TransactionType,
        PrimaryFocus)
    select minOf(AccountingDate), FocusLedgerDimension, summary, sum(DebitAccountingCurrencyAmount), sum(CreditAccountingCurrencyAmount), PostingLayer, operating
    from dimensionFocusBalance
                group by dimensionFocusBalance.FocusLedgerDimension, dimensionFocusBalance.PostingLayer, dimensionAttributeValueCombination.DisplayValue
        where
            dimensionFocusBalance.FocusDimensionHierarchy == _primaryDimensionSet.RecId &&
            dimensionFocusBalance.Ledger == Ledger::current() &&
            ((dimensionFocusBalance.FiscalCalendarPeriodType == FiscalPeriodType::Operating && dimensionFocusBalance.AccountingDate >= _startDate) ||
                (dimensionFocusBalance.FiscalCalendarPeriodType == FiscalPeriodType::Opening && dimensionFocusBalance.AccountingDate == _startDate && _includeOpeningInDetail))  &&
            dimensionFocusBalance.AccountingDate <= _endDate
    join DisplayValue from dimensionAttributeValueCombination
        where dimensionAttributeValueCombination.RecId == dimensionFocusBalance.FocusLedgerDimension;

    // Insert all closing trans records
    if (_includeClosing)
    {
        insert_recordset ledgerTrialBalanceTmpLocal
            (AccountingDate,
            LedgerDimension,
            DetailSummary,
            AmountDebit,
            AmountCredit,
            PostingLayer,
            TransactionType,
            PrimaryFocus)
        select minOf(AccountingDate), FocusLedgerDimension, summary, sum(DebitAccountingCurrencyAmount), sum(CreditAccountingCurrencyAmount), PostingLayer, closing
        from dimensionFocusBalance
                    group by dimensionFocusBalance.FocusLedgerDimension, dimensionFocusBalance.PostingLayer, dimensionAttributeValueCombination.DisplayValue
                    where
                        dimensionFocusBalance.FocusDimensionHierarchy == _primaryDimensionSet.RecId &&
                        dimensionFocusBalance.AccountingDate >= _startDate &&
                        dimensionFocusBalance.AccountingDate <= _endDate &&
                        dimensionFocusBalance.Ledger == Ledger::current() &&
                        dimensionFocusBalance.FiscalCalendarPeriodType == FiscalPeriodType::Closing &&
                        dimensionFocusBalance.IsSystemGeneratedUltimo  == NoYes::No
        join DisplayValue from dimensionAttributeValueCombination
            where dimensionAttributeValueCombination.RecId == dimensionFocusBalance.FocusLedgerDimension;
    }

    // Insert transactions prior to start date as Opening transactions
    insert_recordset ledgerTrialBalanceTmpLocal
        (AccountingDate,
        LedgerDimension,
        DetailSummary,
        AmountDebit,
        AmountCredit,
        PostingLayer,
        TransactionType,
        PrimaryFocus)
        select minOf(AccountingDate), FocusLedgerDimension, summary, sum(DebitAccountingCurrencyAmount), sum(CreditAccountingCurrencyAmount), PostingLayer, opening
        from dimensionFocusBalance
                    group by dimensionFocusBalance.FocusLedgerDimension, dimensionFocusBalance.PostingLayer, dimensionAttributeValueCombination.DisplayValue
            where
                dimensionFocusBalance.FocusDimensionHierarchy == _primaryDimensionSet.RecId &&
                ((dimensionFocusBalance.AccountingDate < _startDate && dimensionFocusBalance.AccountingDate >= periodStartDate) ||
                    (dimensionFocusBalance.AccountingDate >= periodStartDate && dimensionFocusBalance.FiscalCalendarPeriodType == FiscalPeriodType::Opening && !_includeOpeningInDetail)) &&                    dimensionFocusBalance.Ledger == Ledger::current() &&
                dimensionFocusBalance.AccountingDate <= _endDate &&
                dimensionFocusBalance.IsSystemGeneratedUltimo == NoYes::No &&
                dimensionFocusBalance.Ledger == Ledger::current()
        join DisplayValue from dimensionAttributeValueCombination where
            dimensionAttributeValueCombination.RecId == dimensionFocusBalance.FocusLedgerDimension;

    // Add in divided trial balance records if applicable
    if (_dividedStartDate)
    {
        insert_recordset ledgerTrialBalanceTmpLocal
            (AccountingDate,
            LedgerDimension,
            DetailSummary,
            DividedTrialBalanceAmountDebit,
            DividedTrialBalanceAmountCredit,
            PostingLayer,
            TransactionType,
            IsDividedTrialBalance,
            PrimaryFocus)
        select minOf(AccountingDate), FocusLedgerDimension, summary, sum(DebitAccountingCurrencyAmount), sum(CreditAccountingCurrencyAmount), PostingLayer, FiscalCalendarPeriodType, yes
        from dimensionFocusBalance
                    group by dimensionFocusBalance.FocusLedgerDimension, dimensionFocusBalance.PostingLayer, dimensionFocusBalance.FiscalCalendarPeriodType, dimensionAttributeValueCombination.DisplayValue
            where
                dimensionFocusBalance.FocusDimensionHierarchy == _primaryDimensionSet.RecId &&
                dimensionFocusBalance.AccountingDate >= _dividedStartDate &&
                dimensionFocusBalance.AccountingDate <= _dividedEndDate &&
                dimensionFocusBalance.IsSystemGeneratedUltimo == NoYes::No &&
                dimensionFocusBalance.Ledger == Ledger::current() &&
                dimensionFocusBalance.FiscalCalendarPeriodType == FiscalPeriodType::Operating
        join DisplayValue from dimensionAttributeValueCombination
            where dimensionAttributeValueCombination.RecId == dimensionFocusBalance.FocusLedgerDimension;
    }

    if (wasDimCriteriaCreated)
    {
        insert_recordset _ledgerTrialBalanceStagingTmp
            (AccountingDate,
            LedgerDimension,
            DetailSummary,
            AmountDebit,
            AmountCredit,
            DividedTrialBalanceAmountDebit,
            DividedTrialBalanceAmountCredit,
            PostingLayer,
            TransactionType,
            IsDividedTrialBalance,
            PrimaryFocus)
            select AccountingDate, LedgerDimension, DetailSummary, AmountDebit, AmountCredit, DividedTrialBalanceAmountDebit, DividedTrialBalanceAmountCredit, PostingLayer, TransactionType, IsDividedTrialBalance, PrimaryFocus
            from ledgerTrialBalanceTmpLocal

            // Filter by dimension criteria
            notExists join dimensionAttributeLevelValueView
                where dimensionAttributeLevelValueView.ValueCombinationRecId == ledgerTrialBalanceTmpLocal.LedgerDimension
            notExists join tmpDimCriteria
                where
                    (tmpDimCriteria.DimensionAttributeRecId == dimensionAttributeLevelValueView.DimensionAttribute &&
                     tmpDimCriteria.DimensionAttributeValueRecId == dimensionAttributeLevelValueView.AttributeValueRecId)
                    ||
                     (tmpDimCriteria.DimensionAttributeRecId == dimensionAttributeLevelValueView.DimensionAttribute &&
                    tmpDimCriteria.IsOpenCriteria == NoYes::Yes);

        // Extra records may have been added in cases where
        // the ledger dimension has a blank, since blanks
        // are not filtered out by the double-not exists join
        // above, so delete those extra records
        delete_from _ledgerTrialBalanceStagingTmp
        exists join tmpDimCriteria
            where tmpDimCriteria.IsOpenCriteria == false
        notExists join dimensionAttributeLevelValueView where
            dimensionAttributeLevelValueView.ValueCombinationRecId == _ledgerTrialBalanceStagingTmp.LedgerDimension &&
            dimensionAttributeLevelValueView.DimensionAttribute == tmpDimCriteria.DimensionAttributeRecId;
    }
    else
    {
        insert_recordset _ledgerTrialBalanceStagingTmp
            (AccountingDate,
            LedgerDimension,
            DetailSummary,
            AmountDebit,
            AmountCredit,
            DividedTrialBalanceAmountDebit,
            DividedTrialBalanceAmountCredit,
            PostingLayer,
            TransactionType,
            IsDividedTrialBalance,
            PrimaryFocus)
            select AccountingDate, LedgerDimension, DetailSummary, AmountDebit, AmountCredit, DividedTrialBalanceAmountDebit, DividedTrialBalanceAmountCredit, PostingLayer, TransactionType, IsDividedTrialBalance, PrimaryFocus
            from ledgerTrialBalanceTmpLocal;
    }

    // Reverse the sign on the credit amounts since they are stored as a negative value in the focus table
    update_recordSet _ledgerTrialBalanceStagingTmp setting
        AmountCredit = _ledgerTrialBalanceStagingTmp.AmountCredit * -1,
        DividedTrialBalanceAmountCredit = _ledgerTrialBalanceStagingTmp.DividedTrialBalanceAmountCredit * -1;
}