Using Totaling Accounts with SQL in Dynamics NAV

If you’re like many of my clients, you didn’t clean up your chart of accounts when you installed your new ERP. Or maybe you did but then your business changed. So, instead of having a nice set of account ranges, like those in the sample Cronus companies, you need to create odd groupings of accounts.

Totaling Accounts are your first option in NAV. And they work well. However, if you want to use them to write reports using SSRS or another SQL tool, they aren’t that easy to work with. But I’ve built some code to make it a bit easier.

Let’s start by looking at the chart of accounts in CRONUS USA, Inc. (I’ve applied a filter to show the income statement.)

Like most sample companies (and unlike most of my customers), the data is beautifully organized with total accounts having nice ranges.

Scrolling down a little, we find the total range for the revenue accounts:

To use this range in SQL, we use the command “BETWEEN,” as in the following query:

And we get the following results using SSMS:

That was pretty easy.

Another easy conversion of totaling accounts to SQL is with the “|” or “OR” command.

Let’s say we wanted to create nonconsecutive totaling accounts of 41100 and 43100. We can easily do that in NAV. (To keep the chart clean, I placed the totaling accounts at the bottom, starting with an alpha character):

(If you end up using a lot of totaling accounts, you should nail down some naming conventions.)

Converting the “|” sign to SQL requires just an “OR” statement

And the results look like this:

The problem with these examples is that they only work on a “one off” basis. And usually, you need more than a few totaling accounts when you get started.

Here’s an example of one totaling account for customer who has almost 70:

750532..750544|760450..760950|900040|750547

This requires a combination of BETWEENs and ORs. Which isn’t something you want to do every time you write a query. Further, with this kind of mess, you often want to see what you’ve included in your ranges—so you can compare and see if you’ve duplicated accounts in the account schedule. (Something I’m planning to do in the future.)

Fortunately, there is a way to list all accounts in totaling accounts. Here’s a sample report that I built:

The SQL here is pretty complicated. It was an interesting challenge as T_SQL isn’t the best string manipulation language (which generally isn’t a problem).

I created the stored procedure using my “multi company view model.”

I’m planning to include this procedure in my forthcoming set of free SSRS reports for Dynamics NAV.

Here’s the code:

While it’s nice to have the listing, I also like having all my totaling accounts ready to use for any other SQL reporting.

We can adjust the above SQL to update a permanent table that we can use again and again. The table is called TotalingPosting. I use it with my other queries as part of my overall reporting solution, and it allows me to create reports over multiple companies.

And once you have the report, you can create SQL based on totaling accounts in NAV.

For example, I populated a TotalingPosting table for Cronus US. Now, I want to run a report to show all GL Entries for total account 49950.

(Again, the Cronus setup is remarkably clean so it’s not all that realistic. But it will serve our purposes for this example.)

A query of TotalingPosting looks like this:

And returns:

PostingNo_

41100

41200

41450

42100

42200

42300

42400

43100

43200

43300

43400

44100

44200

44300

44399

44400

45000

45100

45200

Now I can build a simple query against my GL Entry table by joining to the TotalingPosting table. (Note: This kind of simple join will only produce correct results for one total account at a time.)

If you have any questions, please let me know.

If you’d like the complete code set when it’s ready or the SSRS report, let me know that as well.