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.

A Simple Example—Using “Or” and “BETWEEN” in SQL

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 Full Solution

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.)

List all Accounts in Totaling Accounts

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:

Save Totaling Accounts for Future Reporting

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.

 

Adam Jacobson

Adam is founder and president of Red Three Consulting. He has over 20 years of experience in ERP consulting and BI consulting. Adam has particular expertise in complex accounting and other multi-company and international reporting challenges. Prior to founding Red Three, Adam was a partner in United Systems Consultants where he ran its 30-person Lawson software practice. Outside of work, he serves as board member and treasurer of the Riverdale Y. When not working, he spends his time answering his son’s political questions and cycling, swimming and reading.

Share

Leave a comment

2 comments

    • Filip A

    I think this is a simpler solution:

    declare @input_kst varchar(1000) = ’10|92..95|97′
    — kst means cost center… but could of course be account or something else.
    declare @code nvarchar(max)

    select @code = coalesce(@code + ‘ OR ‘, ”) + t2.t_sql
    from (
    select
    t_sql =
    case
    when t.KST not like ‘%..%’ THEN ‘kst = ‘ + t.KST
    else ‘kst between ‘ + replace(t.KST, ‘..’, ‘ and ‘)
    end
    from (
    select KST = value from string_split(@input_kst, ‘|’)
    ) t
    ) t2

    –select @code

    declare @table varchar(1000)
    set @table = ‘select kst
    from (
    select kst = ”10” union select kst = ”11” union select kst = ”94” union select kst = ”97” union select kst = ”100”
    ) t2 where ‘
    –select @table

    set @table = @table + @code

    –print @table

    exec(@table)

      • Adam Jacobson

      I developed this some time ago – always more than one way to solve a problem.
      I will note that string_split is only available starting with SQL Server 2016 and my solution was developed on earlier versions.

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult