Dimensions are a wonderful feature of NAV. However, when doing General Ledger analysis, sometimes clients just want to dump everything into Excel and “play” with it—especially if they use Analysis Views.
And to do this, clients want to see all dimensions, at the detail level, for each transaction.
But here’s the challenge: You can only view or filter two dimensions directly on the General Ledger entries page.
Fortunately, there’s a way around this restriction.
How to See All Dimensions for NAV G/L Entries
Let’s start by reviewing the problem: We want to see all transactions for a specified dimension, but when we look at General Ledger Entries, we can only see and filter by the two shortcut dimensions.
We start with the basic General Ledger Entries page in Cronus, USA.:
Out of the box, no dimensions are shown at all.
If we choose Columns, we see the two global dimensions, in this case Department Code and Project Code:
If we add Department Code to the columns and then filter to show only records with department codes, we see that information:
And that may be all we need. But Cronus uses several more dimensions with the General Ledger, as we see in General Ledger Setup:
On a transaction by transaction basis, we can see the additional information by clicking dimensions in the ribbon:
But what if we want to see all G/L transactions for Sales Campaign Winter?
To do this, we need to build an analysis view.
How to Create a View for Your Company
You can build a view for your company to get all transactions with all dimensions. This is especially useful for dumping data with a direct query into Excel or through an SSRS report.
Below is an example SQL for Cronus, USA.
To try this out yourself, change the names to reflect your company and dimensions.
And here’s what the data would look like (without all the columns, obviously):
A few key notes:
1. I’ve joined to the dimension set ID for each dimension I’ve defined.
Theoretically, I could have taken the values for department and project directly from the G/L Entry Table. But I chose to do it this way so I can join to the dimension value table and get the name, not just the code.
This is particularly useful when working with a dimension (such as area, for example) where codes aren’t obvious.
2. I’ve used “coalesce” around all the dimension values and names.
If I don’t do this, SQL server will return “NULL” into the query, which then shows up in Excel or SSRS. End users don’t understand null. It’s better to clean it up at the query/view level.
3. To make this work, you need to replace the dimension names in the appropriate join.
Or, as part of our free Dynamics NAV reports pack, you can used our stored procedure—which will automatically generate the information.
4. I’ve included EVERY column from G/L Entry.
You probably won’t want to give your users every column. Too many choices creates confusion.
A Generic Dimensions View for All Companies
As I’ve written before, I don’t like developing queries or views that work for just one company. So, below is some generic code you can use.
(For more on this, check out my post Using SSRS with Dynamics NAV—Creating Views to Write SSRS Queries.)
As I don’t know what the dimensions will be called (and indeed, they can be called different things for different companies), I’ve included the dimension name as a value in a column rather than the name of a column:
And here’s what it will look like:
I’ve created a sample SSRS report that includes basic transaction data with all the dimensions. I will include it in my set of Free SSRS reports for Dynamics NAV.
Let me know if you’d like a copy or if you have questions about the code.