Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Creating Monthly $0 Bu...
Microsoft Dynamics AX (Archived)

Creating Monthly $0 Budget Entries in BUDGETTRANSACTIONLINE

(0) ShareShare
ReportReport
Posted on by Konrad U 800

This should be simple but as always there are catches. We import our budgets and it works fine but it appears that only entries greater than 0 are allowed. In the inquiry display the missing line is shown fine but in the BUDGETTRANSACTIONLINE table dates/months without a budget do not have a zero entry. When I try to create a 0 entry for the missing month it throws the must be greater than zero error. 

Any easy suggestions? We would like to have the 0 month entries in the above table to make reporting joins work out.

Thanks for any help!

Konrad

*This post is locked for comments

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,269 Super User 2024 Season 2 on at
    RE: Creating Monthly $0 Budget Entries in BUDGETTRANSACTIONLINE

    Hi Konrad,

    Thanks for your feedback. I have verified your answer.

    Indeed when you use a certain new combination on budgets or journals, it will create or reuse DimensionAttributeCombination records.

  • Verified answer
    Konrad U Profile Picture
    Konrad U 800 on at
    RE: Creating Monthly $0 Budget Entries in BUDGETTRANSACTIONLINE

    Andre,

    That did work but after further analysis and attempting to fix the SQL and make it perform better I found that there were many missing accounts. So I went back to reworking the stored procedure. It actually ended up being informative because of the nature of the AX fragmented database.

    The base SQL Query now includes the MainAccount table over a range of expense accounts tied to the DIMENSIONATTRIBUTEVALUECOMBINATION. I joined the DIMENSIONATTRIBUTEVALUECOMBINATIONSTATUS table to get rid of non postable combinations.

    That created a range of valid postable (isValid) department account combinations.

    I joined the LEDGERPERIOD table for the appropriate fiscal year and created a record for each combination for the fiscal year.

    Then it was simple to add in the BUDGETTRANSACTIONCUBE period budget amounts for periods that had a budget. Records not updated would remain at 0.

    Finally I created a query against the GL header and detail tables to add in the actual amounts and export the payment lines for detail analysis.

    The most interesting thing I found from the other solution is that it created a new DIMENSIONATTRIBUTECOMBINATION record for the unapproved budget combo.

    We are good! It runs nice and fast for a side benefit.

    Thanks for the assistance, Hope this helps others with the same dilema.

    Konrad

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,269 Super User 2024 Season 2 on at
    RE: Creating Monthly $0 Budget Entries in BUDGETTRANSACTIONLINE

    Hi Konrad,

    It sounds like a solution, but not a neat one. Those records could be easily deleted. However, you mentioned you will work on a future improvement... So, has your issue been solved for now?

  • Konrad U Profile Picture
    Konrad U 800 on at
    RE: Creating Monthly $0 Budget Entries in BUDGETTRANSACTIONLINE

    Andre,

    Found somewhat of a solution to the budget strangeness. Create a draft register and put all the 0 month entries in it. Does not need to be posted or anything. It completes the join because the draft status is contained in the account dimension in the BudgetTransactionLine table. Department and Main Account will resolve fine SQL.

    Long term we may look at adding a nice FY/Account/Department table with buckets for budget and actual.

  • Konrad U Profile Picture
    Konrad U 800 on at
    RE: Creating Monthly $0 Budget Entries in BUDGETTRANSACTIONLINE

    Andre,

    It is a stored procedure with about 300 lines. It does multiple selects to gather the information on the department owner, expense accounts, budgets month and yearly and actuals month and yearly. Outer joins are used and have been tried but there are several multiple joins due to the transactional nature of the budget and expenses. Unfortunately the budget cube views leave holes where there is no budget for a month.

    Thanks

    Konrad

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,269 Super User 2024 Season 2 on at
    RE: Creating Monthly $0 Budget Entries in BUDGETTRANSACTIONLINE

    Hi Konrad,

    I don't know how this SQL statement is defined. Usually, it would be possible to use outer joins instead of inner joins.

  • Konrad U Profile Picture
    Konrad U 800 on at
    RE: Creating Monthly $0 Budget Entries in BUDGETTRANSACTIONLINE

    Hi Ludwig!

    I have been around forever and am very used to both Dynamics and older table/record based ERPs.

    As Andre pointed out we are using SQL to generate monthly expense reports automatically for our department heads. We use the employee id and the department dimension in ledger to extract and report on expense accounts. Some employees have more than one department so they get multiple reports. The budget function in AX appears to be largely transactional (entries are only made on defined dates and they have to be non zero).

    That creates issues since we start off at the top with the periods and then join and populate down to the department account level. We want to see all the periods even if there is no budget or expenditure.

    We would like to find something to emulate the old bucket days since we evaluate monthly and like to show  YTD Budget and YTD Actuals. Just because there is no budget for the period does not mean there are no expenditures.

    The online inquiry does a good bit of it but it is done through programming and is missing some our calculations.

    Any ideas? There are probably many ways to do this I am trying to figure out the most efficient given AX highly normalized table structure.

    Thanks

    Konrad

  • Suggested answer
    Satish Panwar Profile Picture
    Satish Panwar 14,645 on at
    RE: Creating Monthly $0 Budget Entries in BUDGETTRANSACTIONLINE

    Hi Konrad,

    Instead of creating dummy data to satisfy the need for reporting, you should review reporting strategy and use different kind of join instead. This will make sure in future you don't have to keep creating these records with $0. At some point, user will forget to create record, you will report will have an issue and it may go unnoticed. This will also make sure you don't go outside what product support (not allowing negative amount Or $0 as amount).

    Thanks,

    Satish Panwar

    Please take time to click 'Yes' against the answers that help you guide in right direction to help other community members.

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Creating Monthly $0 Budget Entries in BUDGETTRANSACTIONLINE

    Hello Konrad,

    Why would you upload a $0 budget amount?

    If you don't upload anything wouldn't that represent a $0 budget?

    Can you elaborate?

    Many thanks and best regards,

    Ludwig

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,269 Super User 2024 Season 2 on at
    RE: Creating Monthly $0 Budget Entries in BUDGETTRANSACTIONLINE

    Hi Konrad,

    What reporting are you using? It sounds like you have custom SQL coding or scripts with inner joins. The standard application is not allowing the zero entries. The default reporting options and business logic is aware of a zero budget in case there is no record found.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans