Trial Balance by all dimensions

(0) Share

Report

Posted on by Community Member

How do you generate a trail balance report that lists all associated dimensions tagged to the GL account and their balances? Is this not an out of the box trial balance report? All I see is a trail balance report that will generate by 1 global dimension but not all.

Categories:

Reports and Business Intelligence

I have the same question (0)

All responses (5)

Answers (1)

Community Member on at

Like (0)

Report

This is for dynamics 365, business edition

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Can anyone help on this? This is for Dynamics 365 for f&o, Business edition.

Thank you,

Tim Tishman

Was this reply helpful? Yes No
Suggested answer

AJAnsari 5,754 on at

Like (0)

Report

Hi ttishman,

There are various versions of the Trial Balance report out of the box in Dynamics 365 for Financials. You are probably using the "Trial Balance, per Global Dim." which shows one Dimension Value at a time.

There is another one called "Trial Balance, Spread G. Dimension", in which you define which Dimension you want to see as a column spread. In my screenshot, I selected by CUSTOMERGROUP dimension, which has multiple Dimension Values. And it presented an output with all the Dimension Values side by side as columns.

If you wish to create report that shows multiple Dimensions with their Dimensions Values spread as rows or columns side by side, then you will need to go one of the following routes:

1. Use a tool like Power BI

2. Create a new Report Layout for this Trial Balance report (ideally, in the RDLC format, because the SQL Report Builder / Visual Studio toolsets are more powerful than Microsoft Word for this type of work).

I hope this helps. If my response has answered your question, please verify by clicking Yes next to "Did this answer your question?"

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi AJ,

Thank you for the response. I appreciate it. The way I look at this tool/NAV is that I compare mandatory dimensions (mandatory to be tagged to a GL account) to account segments like that in GP or QB or other accounting tools. You could call it a fully qualified account string. In GP when your run a trial balance you can see the listing of ALL account strings down the rows. In this tool I would like the see the GL account with ALL mandatory dimension values listed down the rows, like viewing an account string. What I am taking from your answer is that this is not possible without customization in RDLC format or the like (I have worked with this and it would take some time to do in SQL Report Builder). To clarify, you agree that this function indeed is not out of the box? Do you know if it is out of the box in Dynamics NAV? I am surprised that this is not an option.

Thank you,

Tim Tishman

Was this reply helpful? Yes No
Verified answer

AJAnsari 5,754 on at

Like (0)

Report

Hi Tim,

That is correct - I am not aware of any Trial Balance reports in NAV or D365FIN that provide what you're looking for in a prepackaged report.

Given what you're explaining, I would use Power BI if I were doing it myself. I would set up two Web Services in D365FIN:

1. General Ledger Entries page, which contains a field called the Dimensions Set ID, and

2. Page 479 (Dimension Set Entries)

Once you extract these as queries in Power BI Desktop, model the two together (the common point in the two queries is the Dimension Set ID). Then you can make your report to show all the detail per entry including the Dimension Values used in the entry, and you will need to set it up like you would a Trial Balance.

You could also do the same in Excel (ideally using PowerPivot), but Power BI is my tool of choice because of the ability to access the Trial Balance wherever I go, and to share with others in the organization.

I hope this helps. If my response has answered your question, please verify by clicking Yes next to "Did this answer your question?"

Was this reply helpful? Yes No