Calculate Budget Balance by code

Question

I need to calculate the budget balance for any particular dimension (AX 2012 R2).

I perform the following code works correctly but it takes a long time to finish.

It is because this calculated all budget balances that has AX and not just the one I want.

In the end, the variable BudgetTmpBalance It has the records of all budget entries.

There is something I'm not considering. Anyone know what is?

Thanks in advance.

Code:

BudgetTransactionLine              recBudgetTransactionLine;
DimensionAttributeValueCombination recDimensionAttributeValueCombination;        
date                               FiscalYearStart, FiscalYearEnd;
AccountingDistribution             recAccountingDistribution;    
Query                              query = new Query();
QueryBuildDataSource               qbds;
QueryBuildDataSource               qbdsJoin;
QueryBuildRange                    qbr;
BudgetCalculateBalance             budgetCalculateBalance = new BudgetCalculateBalance();
BudgetTmpBalance                   budgetTmpBalance;
DimensionDisplayValue              pDimensionDisplayValue;

;

pDimensionDisplayValue = "Correct Value"; 

select * from recDimensionAttributeValueCombination where recDimensionAttributeValueCombination.LedgerDimensionType == LedgerDimensionType::Budget &&
                                                          recDimensionAttributeValueCombination.DisplayValue        == pDimensionDisplayValue;

FiscalYearStart = str2Date("01/04/" + int2str(year(today())), 123);
FiscalYearEnd   = str2Date("31/03/" + int2str(year(today()) + 1), 123);

//Find recId of header that I Want (Works fine)
select BudgetTransactionHeader from recBudgetTransactionLine where recBudgetTransactionLine.LedgerDimension == recDimensionAttributeValueCombination.RecId &&
                                                                      (recBudgetTransactionLine.Date >= FiscalYearStart &&  recBudgetTransactionLine.Date <= FiscalYearEnd);

qbds = query.addDataSource(tableNum(BudgetTransactionHeader));
qbr  = qbds.addRange(FieldNum(BudgetTransactionHeader, TransactionStatus));
qbr.value(queryValue(1));
qbr  = qbds.addRange(FieldNum(BudgetTransactionHeader, RecId));
qbr.value(queryValue(recBudgetTransactionLine.BudgetTransactionHeader));//Header RecId (correct value)
qbdsJoin= qbds.addDataSource(tableNum(BudgetTransactionLine));
qbdsJoin.relations(false);
qbdsJoin.fields().dynamic(NoYes::Yes);
qbdsJoin.addLink(fieldNum(BudgetTransactionHeader, RecId), fieldNum(BudgetTransactionLine, BudgetTransactionHeader));
qbdsJoin.joinMode(JoinMode::InnerJoin);

budgetCalculateBalance.parmDimensionFocus("CTA+UN+CC");    
budgetCalculateBalance.parmCalculateLedgerAmounts(NoYes::Yes);
budgetCalculateBalance.parmFiscalCalendarRecId(Ledger::fiscalCalendar());
budgetCalculateBalance.parmStartDate(FiscalYearStart);
budgetCalculateBalance.parmEndDate(FiscalYearEnd);
budgetCalculateBalance.parmIncludeSubModels(NoYes::No);
budgetCalculateBalance.parmIncludeAllBudgetModelsIfNotSpecified(NoYes::No);
budgetCalculateBalance.parmBudgetModelId("TOTAL");
budgetCalculateBalance.parmBudgetType();
budgetCalculateBalance.parmAccumulateAmounts(NoYes::No);
budgetCalculateBalance.parmLedgerCategory(CurrentOperationsTax::Current);
budgetCalculateBalance.parmQuery(query);
budgetTmpBalance = budgetCalculateBalance.calculatePeriodBalances();

Answer 1

Solved Now the process is complete in 5 seconds!

here the correcto code:

BudgetTransactionLine              recBudgetTransactionLine;
DimensionAttributeValueCombination recDimensionAttributeValueCombination;        
date                               FiscalYearStart, FiscalYearEnd;
AccountingDistribution             recAccountingDistribution;    
Query                              query;
QueryBuildDataSource               qbds;
QueryBuildDataSource               qbdsJoin;
QueryBuildRange                    qbr;
BudgetCalculateBalance             budgetCalculateBalance = new BudgetCalculateBalance();
BudgetTmpBalance                   budgetTmpBalance;
DimensionDisplayValue              pDimensionDisplayValue;
#define.LedgerDimensionDataSourceName('BudgetTransactionLine_1')

;

pDimensionDisplayValue = "Correct Value"; 

select * from recDimensionAttributeValueCombination where recDimensionAttributeValueCombination.LedgerDimensionType == LedgerDimensionType::Budget &&
                                                      recDimensionAttributeValueCombination.DisplayValue        == pDimensionDisplayValue;

FiscalYearStart = str2Date("01/04/" + int2str(year(today())), 123);
FiscalYearEnd   = str2Date("31/03/" + int2str(year(today()) + 1), 123);

//Find recId of header that I Want (Works fine)
select BudgetTransactionHeader from recBudgetTransactionLine where recBudgetTransactionLine.LedgerDimension == recDimensionAttributeValueCombination.RecId &&
                                                                  (recBudgetTransactionLine.Date >= FiscalYearStart &&  recBudgetTransactionLine.Date <= FiscalYearEnd);

budgetCalculateBalance.parmDimensionFocus("CTA+UN+CC");    
budgetCalculateBalance.parmFiscalCalendarRecId(Ledger::fiscalCalendar());
budgetCalculateBalance.parmCalculateLedgerAmounts(NoYes::Yes);
budgetCalculateBalance.parmFilterByBudgetType(NoYes::Yes);
budgetCalculateBalance.parmIncludeSubModels(NoYes::Yes);
budgetCalculateBalance.parmStartDate(FiscalYearStart);
budgetCalculateBalance.parmEndDate(FiscalYearEnd);
if(TieneCC)
    budgetCalculateBalance.parmDimensionFocus("CTA+UN+CC");
else
    budgetCalculateBalance.parmDimensionFocus("CTA+UN");

query = new Query(querystr(BudgetCalculateBalance));
BudgetTransactionManager::addDimensionCriteriaForLedgerDimension(query, recDimensionAttributeValueCombination.RecId, #LedgerDimensionDataSourceName);
budgetCalculateBalance.parmQuery(query);        
budgetTmpBalance = budgetCalculateBalance.calculatePeriodBalances();