How to add a new operating unit type

For AX2012, you can follow the steps in this link https://msdn.microsoft.com/en-us/library/gg989762.aspx
Couple of important points:

1. The new enum value must use the immediate next number for that enum (you should not skip any number)
2. The new enum name drives the view name that you need to create. AX will look for a view with "DimAttribute" prefix after the enumeration name.
   For example, if the new enum name is OMBranch then the view name must be DimAttributeOMBranch.

For D365, it is quite similar to the steps in AX2012, however in the view it should have a method called registerDimensionEnabledTypeIdentifier and you should use the view name that you just created. This will then add the new operating unit as a dimension type.

Keep in mind that in D365, there are 3 additional operating unit types (branch, rental location, region) that are part of the Fleet Management model, which is actually a sample model and doesn't get installed by default to tier 2 or production environments.

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;
}

Getting list of companies based on the centralized payments organisation hierarchy

AX2012 introduces a new feature called organisation hierarchy which can be used together with the centralised payment feature.

To get the list of companies in the organisational hierarchy that are associated with the centralised payments, please look at the CustVendOpenTransManager.findSharedServiceCompanies() method

Management Reporter 2012 RU6, CU7 integration issue causing TempDb size to blow

In Management reporter 2012, starting from RU6, Microsoft introduces customer and vendor attributes, however under some circumstances this might cause the TempDb size to blow out.

The SQL query that causes the issue is:

set dateformat mdy;

SELECT COUNT(DISTINCT GJAE.RECID) FROM (SELECT

GJAE.RECID, GJAE.Partition as Partition, GJAE.TRANSACTIONCURRENCYAMOUNT, GJAE.ACCOUNTINGCURRENCYAMOUNT, GJAE.REPORTINGCURRENCYAMOUNT,

                                  GJAE.QUANTITY, GJAE.ISCREDIT, GJAE.TRANSACTIONCURRENCYCODE, C.TXT AS CURRENCYNAME, C.SYMBOL AS CURRENCYSYMBOL, GJAE.PAYMENTREFERENCE, GJAE.POSTINGTYPE, GJAE.TEXT,

                                  GJAE.GENERALJOURNALENTRY, GJAE.REASONREF, GJAE.LEDGERDIMENSION,

                                  GJE.ACCOUNTINGDATE, GJE.JOURNALNUMBER, GJE.POSTINGLAYER, GJE.LEDGER, GJE.FISCALCALENDARPERIOD, GJE.ACKNOWLEDGEMENTDATE,

                                  GJE.DOCUMENTDATE, GJE.LEDGERPOSTINGJOURNAL, GJE.DOCUMENTNUMBER, GJE.JOURNALCATEGORY,

                                  DALVV.DIMENSIONATTRIBUTE, DALVV.DISPLAYVALUE, DALVV.ENTITYINSTANCE,

                                  RTR.REASON, RTR.REASONCOMMENT,

                                  LE.CONSOLIDATEDCOMPANY, LE.ISBRIDGINGPOSTING, MA.AccountCategoryRef, VDPT.NAME as VENDORNAME, CDPT.NAME as CUSTOMERNAME,

                                  LJT.NAME as JOURNALDESCRIPTION, LJT.JOURNALTYPE, LJT.JOURNALNAME,

                                  GJE.SUBLEDGERVOUCHER, TRT.TRACENUM, TRT.REVERSED, LJTR.REVERSEENTRY, NULL as MODIFIEDDATETIME, NULL as MODIFIEDBY, LJT.POSTEDDATETIME as JOURNALENTRYDATE, NULL as HISTORICALEXCHANGERATEDATE

FROM GENERALJOURNALACCOUNTENTRY GJAE

                           left outer join GENERALJOURNALENTRY GJE on GJAE.GENERALJOURNALENTRY = GJE.RECID

                           left outer join DIMENSIONATTRIBUTELEVELVALUEVIEW DALVV on GJAE.LEDGERDIMENSION = DALVV.VALUECOMBINATIONRECID

                           left outer join REASONTABLEREF RTR on GJAE.REASONREF = RTR.RECID

                           left outer join LEDGERENTRY LE on GJAE.RECID = LE.GENERALJOURNALACCOUNTENTRY

                           left outer join LEDGERENTRYJOURNAL LEJ on GJE.LEDGERENTRYJOURNAL = LEJ.RECID

                           left outer join LEDGERJOURNALTABLE LJT on LEJ.JOURNALNUMBER = LJT.JOURNALNUM

                           left outer join CURRENCY C on GJAE.TRANSACTIONCURRENCYCODE = C.CURRENCYCODE and C.Partition = GJAE.Partition

                           left outer join VENDTRANS VTR on GJE.SUBLEDGERVOUCHER = VTR.VOUCHER and GJE.DOCUMENTDATE = VTR.DOCUMENTDATE

                           left outer join VENDTABLE VTA on VTR.ACCOUNTNUM = VTA.ACCOUNTNUM

                           left outer join DIRPARTYTABLE VDPT on VTA.PARTY = VDPT.RECID

                           left outer join CUSTTRANS CTR on GJE.SUBLEDGERVOUCHER = CTR.VOUCHER and GJE.DOCUMENTDATE = CTR.DOCUMENTDATE

                           left outer join CUSTTABLE CTA on CTR.ACCOUNTNUM = CTA.ACCOUNTNUM

                           left outer join DIRPARTYTABLE CDPT on CTA.PARTY = CDPT.RECID

                           left outer join TRANSACTIONREVERSALTRANS TRT on GJAE.RECID = TRT.REFRECID and TRT.REFTABLEID = 3119

                           left outer join LEDGERJOURNALTRANS LJTR ON LJTR.REVERSEENTRY = 1 AND GJE.SUBLEDGERVOUCHER = LJTR.VOUCHER AND GJE.ACCOUNTINGDATE = LJTR.TRANSDATE

                           left join DIMENSIONATTRIBUTE DA ON DA.RECID = DALVV.DIMENSIONATTRIBUTE

                           left join MAINACCOUNT MA on MA.RECID = DALVV.ENTITYINSTANCE AND DA.TYPE = 2  LEFT OUTER JOIN CHANGETABLE(CHANGES GENERALJOURNALACCOUNTENTRY, 0) GJAE_CT ON GJAE.RECID = GJAE_CT.RECID

 LEFT OUTER JOIN CHANGETABLE(CHANGES GENERALJOURNALENTRY, 0) GJE_CT ON GJE.RECID = GJE_CT.RECID

 LEFT OUTER JOIN CHANGETABLE(CHANGES LEDGERJOURNALTABLE, 0) LJT_CT ON LJT.JOURNALNUM = LJT_CT.JOURNALNUM

 LEFT OUTER JOIN CHANGETABLE(CHANGES LEDGERJOURNALTRANS, 0) LJTR_CT ON LJTR.RECID = LJTR_CT.RECID

) GJAE WHERE GJAE.POSTINGTYPE <> 19


As you can see the query does not consider partition or dataAreaId when do the joins to the CustTable, CustTrans, VendTable, and VendTrans. Depending what kind of data you have, this might cause the TempDb size to blow out of the proportion.

In our case I had a 169GB AX database, and the TempDb size blew to over 500GB because of this query. The database does have a lot of customers and vendors using the same account numbers across 130+ companies.

I was told to use Management reporter RU5 instead, where it doesn't have any customer and vendor attributes, and that worked fine.

AX2012 Help server error - Handler "svc-Integrated" has a bad module "ManagedPipelineHandler" in its module list

When you open the Help menu, it displays an error dialog saying "Unable to contact the server".

When opening the help server URL using an internet browser, it displays an error saying "Handler "svc-Integrated" has a bad module "ManagedPipelineHandler" in its module list".

Solution:

Open a command prompt and run this:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -i

Intermittent SSRS report error: System.Security.Permissions.EnvironmentPermission when running reports for the first time

Just today I had an issue with Dynamics AX 2012 SSRS report when running reports for the first time. It sometimes came up with this error:

"The DefaultValue expression for the report parameter ‘AX_CompanyName’ contains an error: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. (rsRuntimeErrorInExpression)"

Then I've found a helpful blog that seems to explain the same issue in http://blogs.msdn.com/b/axsupport/archive/2012/02/02/microsoft-dynamics-ax-2012-reporting-extensions-error-system-security-permissions-environmentpermission-while-running-report.aspx

So basically I need to make some changes in the rssrvpolicy.config to change the permission set name from Execution to FullTrust and then restart the SSRS service.

You can find the rssrvpolicy.config file in:

• If you are using SQL Server 2008, the default location of this file is: \Program Files\Microsoft SQL Server\MSRS10.[SSRSInstanceName]\Reporting Services\ReportServer
• If you are using SQL Server 2008 R2, the default location of this file is: \Program Files\Microsoft SQL Server\MSRS10_50.[SSRSInstanceName]\Reporting Services\ReportServer
• If you are using SQL Server 2012, the default location of this file is: \Program Files\Microsoft SQL Server\MSRS11.[SSRSInstanceName]\Reporting Services\ReportServer

<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="Report_Expressions_Default_Permissions"
Description="This code group grants default permissions for code in report expressions and Code element. ">

AX 2012 - Changing ledger/default dimensions lookup

If you ever want to change the lookup on AX 2012 ledger/default dimensions, these are 2 places that you will need to change:

• Form DimensionDefaultingLookup for default dimension lookup
• Class LedgerDimensionController.restrictQueryDimensionAttributeValues for ledger dimension lookup

Management Reporter 2012 RU3 with Dynamics AX 2012 - failed integration due to dimension totalling function

I've just recently done an installation of recently released Management Reporter 2012 RU3 with Dynamics AX 2012 CU3 using DataMart and has bumped into a known Management Reporter issue.

The issue will prevent Management Reporter to actually complete the initial integration with Dynamics AX 2012. If I take a look at the DataMart integration log, there is an error entry that says:
[AX 2012 Dimension Values to Dimension Value] has encountered an error. Processing will be aborted. Error text: Exception of type 'Microsoft.Dynamics.Integration.Service.Tasks.IncompleteResultException' was thrown.

Basically this version of Management Reporter cannot work together with a feature in Dynamics AX 2012 to "calculate total from multiple dimension values". You can check this from GL > Setup > Financial Dimensions  > Financial dimension values [button]. Please note that all the dimension values must not have the "calculate total from multiple dimension values" checkbox ticked and also there should not be any totals set up for any of the dimension values.

You can run this SQL script to check if you might have the issue:
select DIMENSIONATTRIBUTEVALUE, COUNT(DIMENSIONATTRIBUTEVALUE) as DimCount 
from DIMENSIONATTRIBUTEVALUETOTALLINGCRITERIA 
group by DIMENSIONATTRIBUTEVALUE 
order by DimCount desc 

If you have any entry with DimCount greater than 1, then you'll have the integration issue with this Management Reporter.

To solve the issue, you need to make sure that all of the dimension values have the "calculate total from multiple dimension values" checkbox un-ticked and you have no entry in DimensionAttributeValueTotallingCriteria table.
I have been told that the issue only affects the initial integration between Management Reporter and Dynamics AX 2012, meaning you should be able to use the dimension value totalling function from AX once you've done the initial integration.