Understanding the NAV Database

I recently posted a seven-part series on SQL for super users. (You’ll find the introduction here.)

In this series, I chose to use generic Northwind database (with some enhancements) for my examples. (The posts came out of presentation I made to a mixed group of Dynamics users.)

But, as you know, each kind of Dynamics software (i.e. GP, NAV, AX, CRM) has its idiosyncrasies. Therefore, I’m in the process of adapting the generic Northwind examples to specific Dynamics types. And I’m starting with NAV, as I happen to spend quite a lot of time on it. (If you’re interested in getting a copy of the PDF when it’s ready, let me know.)

As I prep the NAV series, I realize a basic intro to NAV would be useful before launching into it.

Therefore, in this post I’m going to discuss:

  1. Finding the right table—the “About This Page”
  2. The suboptimal use of special characters
  3. Foreign keys and the NAV036 data dictionary report
  4. How many tables you have to join in NAV reporting?

1. Finding the Right Table—The “About This Page”

For every page you visit in NAV, you can use the “About This Page” feature to find the major table the page updates.

For example, say we’re in Vendors:

To see what table Vendors updates, we click the top right arrow, select help and then “About This Page”:

And this window appears:

What we care about is Source Table, which in this case is Vendor.

Let’s see what we get when we follow the same steps with the Vendor Ledgers Entries:

We get the source table of Vendor Ledger Entry.

Of course, we now have the NAV table name. But that’s not quite the same thing as the name in SQL Server. Why? Because NAV creates a different table for every company you create.

So, for example, if we had two companies:

  • Cronus USA, Inc.
  • Cronus Mexico, Inc.

Then we would find two database tables representing vendors:

  • Cronus USA, Inc_$Vendor
  • Cronus Mexico Inc_$Vendor

As you can see below:

About This Page—Finding Column Data

About This Page is also useful when you want to know not just the table name but the columns themselves.

Let’s use Customer Ledger Entries as an example.

If we open the Table Fields tab, we find a list of columns. It starts with the primary key (PK) for the table and continues with all remaining columns in alphabetical order:

What’s great is that you can see what data is populating each column – so you can have some confidence when you go to build your reports that you’re choosing the correct column.

Limitations of About This Page

About This Page works fine when you have only one table per page. But it doesn’t work so well when you have multiple tables per page, as is the case with Sales Orders.

Sales Orders has both header and detail tables. But About This Page only displays the header:

Most header detail combinations are fairly easy to figure out. Here, for example, Sales Header matches to Sales Line and Purchase Header matches to Purchase Line. But it’s not always so obvious. (I’m planning to write a series on basic queries to get you started.)

2. The Suboptimal Use of Special Characters

Unfortunately, NAV allows the use of spaces and other special characters in its table and column names. From my SQL perspective, this is annoying because it forces users to place square brackets around all names.

For example, let’s start with a table name that works:

Here, SQL interprets the space between CRONUS and USA as the end of the table name. Therefore, it looks for a table called CRONUS and comes back with “invalid object name.”

By the way, do yourself a favor and keep your company names simple. In this case, “CRUS” would be much nicer than “Cronus USA, INC.” (On a side, note using special characters can also mess up Web Services. But that’s another topic).

Another catch: In this example the name of the table is the same in Help and in the database. But that’s not always the case because you can use periods in NAV’s internal names, but you can’t use periods in table names in SQL server. So SQL replaces the period with an underscore.

Let’s look at the Customer Ledger Entries to illustrate:

In SQL Server, the name is:

[dbo].[CRONUS USA, Inc_$Cust_ Ledger Entry]

Here, “Cust.” has become “Cust_”.

The same is true for column names. So it’s “[Entry No_]” not “Entry No.”

And remember: You need to put brackets around your column names as well. They almost always include spaces or other special characters.

3. Foreign Keys and the Jet Sample Report NAV036 Data Dictionary

Unlike in the sample Northwind Database, NAV doesn’t maintain foreign key integrity at the database level. So, you don’t have any of those nice silver key emojis to help you figure out relations.

The good news is that, in general, foreign keys in NAV are fairly straightforward.

For example, if you were reporting on Sales Order and wanted to know where the customer data was found, you could simply go to the Customer page using “Advanced”:

And once you’re on the Customer page, you can use About This Page again.

About This Page tells you that the sell-to customer on the sales order came from the Customer table:

Jet Sample Report NAV036 – Data Dictionary

But there’s another option. You can use Jet Sample Report NAV036 – Data Dictionary to figure out how to join tables.

Let’s go back to our Cust. Ledger Entry table and look at NAV036:

First, we see that TableNo and TableName are the same as in the About This Page option.

Second, every column is assigned a Field No.

Mostly, what we care about is the RelationTableNo and RelationFieldNo (i.e. columns seven and eight).

In this case, we can see that Customer No. is related to Table 18 on FieldNo. But the related field number is actually 1 (i.e. When a RelationTableNo exists and the RelationFieldNo is zero, then the related field number is actually 1).

If we now look up Table 18, we see that the related Table is Customer, as we expected.

We can draw two key points about this information:

  1. Beware the No. (or No_) column

Based upon the data dictionary, you’ll be relating the “Customer No.” column in “Cust. Ledger Entry” table to the “Customer No.” column in the “Customer” table. But there is no column called Customer No. in the Customer table!

This is different from almost every other ERP database I’ve used. In Dynamics NAV, the “No.” (or “No_”) column name is used for different purposes in different tables.

Yes, this gets tricky. Which is why I’ll cover it in more detail in the NAV PDF.

  1. Not all relations are shown

Let’s take a look at Sales Line (used for sales order and unposted sales invoices):

We can see that Sell-to Customer No. is validated against table 18 – Customer.

But “Document No.” isn’t shown as related to anything—even though we know it must be in table 36 – Sales Header. (Shown as “No.” Not “Document No.” Of course.)

Further, the “No.” column doesn’t have any Relation table. This makes some sense as No. can be validated against multiple tables—including Item (table 27) and G/L Account (table 15) depending on Type.

Again, I’ll cover this wrinkle in more detail in my upcoming PDF on SQL for NAV super users. I’ll also include some sample queries (and hopefully diagrams) to show you the practice, and not just the theory, of NAV data.

4. How Many Tables Do You Have to Join in NAV Reporting?

Before we start, let me say that my goal here isn’t to discuss relational database theory. My goal is to make sure you get what you want (and expect) when writing reports.

The challenge when writing reports in NAV is that it copies a lot of data between tables. While this can make reporting easier, it can also make it much harder.

Let’s look at an example.

If we look at Sales Order, we see not only the sell-to customer, but also the city and state:

If we take a look at About This Page, we see the data stored in the Sales Order table:

So the Sales Order table includes data for Sell-to City and Sell-to State. Which means that if we’re building a query, we don’t need the Customer table. Isn’t that great?

Well, yes and no. Watch what happens when I update the customer address because the company has moved from Atlanta, GA to New York, NY:

The address change isn’t reflected in the Sales Order table:

This could be good or bad. In this case, maybe you want to keep the old address—so then your reports on sales by territory or state don’t change.

But what if it was the company name that had changed, instead of the company address? Then you’d have two names in your sales report, when you only want one.

 

Building an SSRS Trial Balance for Dynamics GP

In this post, I’ll show you how to create a view in SSRS that allows Dynamics GP users to create a trial balance for any time period (or multiple periods) and get the ending balance for that period(s).

I was motivated to write this post after seeing a question on the GPUG forum about it. The question was referred to Victoria Yudin, a GP SQL guru, and she has a nice stored procedure to get a 12-month trial balance for the same year.

Also, I’ve been developing some generic design patterns for financial reporting in SSRS and as part of this project we’ve been asked to create trial balances for the last 12-month period—so the topic has been on my mind.

Why Views Instead of Stored Procedures?

While Victoria’s solution is good, I want to add to it by allowing users to return trial balances for any period, not just the last 12 months. Also, I wanted to use a view instead of a stored procedure for a couple reasons:

  1. From a users perspective, views work just like tables. So, for example, a user can use views in the SSRS Report Builder Report Wizard where they can’t use a stored procedure.
  2. Stored procedures tend to multiply and/or require lots of maintenance. If you add output fields to a stored procedure, you have to either create a new procedure or fix all your existing reports. But, if you add a few new fields to a view, your existing reports don’t change. (But this is really a topic for another blog post.)

So let’s start where Victoria started—with the balance table. I’m going to use the Account Summary view as it contains both open and historical data. This query takes the period balances that are stored in GP and totals them for each period. So, we get the ending balance for each period:

And here’s the output:

It’s not quite what we’re looking for. It’s great that each period “rolls forward” to include previous balances. But GP only stores data in the table when there was some activity in the period.

List all Account/Fiscal Period Combinations

A Dynamics GP Consultant can fix this by starting with a list of all account/fiscal period combinations that exist, instead of starting with the amount data. Here’s the query:

And here’s the sample output:

This is much better.

By the way, in financial reporting we often want to see all accounts, whether or not they had activity. Or if they had budgeted activity but not actual activity. Or actual activity but not budget. The solution is to start with a list of what we want to see, rather than start with the amount information.

Now that I have a list of possible accounts and periods, I use a left outer join to check for actual activity:

When I run the above query, I get the output I want—with a value for every period:

Next, I’m going to run the above query into a view. Note that when doing this, I remove the “order by” clause as well as the check for fiscal year, which I used for testing.

With the above view, I can create an SSRS report which groups fiscal year and periods as columns and account descriptions as rows:

Filtering Time Periods

Now, if I want to choose only a few periods, I can do so by changing the parameters. But remember, I must filter the time periods in SSRS, not in the SQL query. Why? Because to calculate balances correctly, I need to select periods 1-6 in order to get period 7.

For example, let’s say I want to select the last 12 months. I tell the query to select two year’s worth of data:

I’ve purposely broken the filter process into multiple steps. I like to segment the process because multi-part formulas are harder to debug when you return to your reports months (or years) later.

In each case, I’m combining fiscal year and period into a six-digit number. For example:

  • 2015 period 10 becomes 201510
  • 2015 period 6 becomes 201506.

I do this three times: (1) for the fiscal year and period from the data returned from the query, (2) for the fiscal year and period from the query result and, (3) for the starting fiscal year and period, which requires a little twist in the logic.

All these fields can be created from Dataset->Properties->Fields:

Which I define as:

Now, I’m going to establish the ending period of the report, which I get directly from my parameters:

The logic for the starting period is a little more complex. Basically, I have two situations: period 12 and all other periods. For anything but 12, the logic is simple:

  • Subtract one from the year
  • Add one to the period.

So, 2015 period 10 becomes 2014 11.

For period 12, I want to start at period 1 for the same year.

So, the year remains the same. The period becomes 1.

Here’s how the logic looks in SSRS:

Now that I have the three date fields, I can easily create the filter.

Choose Dataset->Properties->Filters:

I state that “fiscal year and period” must be between “start fiscal year and period” and “end fiscal year and period.” I also exclude period 0, which represents the balance brought forward.

Here’s the output:

A Word of Warning

I acknowledge that using filters in SSRS degrades performance. I’m only using filters here because I’m crossing years. If I simply wanted all months for the current year, I wouldn’t need a filter because I could incorporate the record selection into the query.

If you’re looking for a Dynamics GP Consultant or would like a copy of the sample report, let us know.

 

Using Dynamic SQL to Solve the Problem of Too Few SQL Server Instances

As consultants, we pride ourselves on both knowing the best way to set up systems AND finding workarounds when client infrastructure is “suboptimal” and unlikely to change. We recently performed such a workaround for a client that had too few SQL server instances—and I’m going to share the experience with you here. (Hopefully, your system is set up more optimally. But even if it is, you can still learn from our example.)

A Workaround for Dynamic SQL and BatchMaster

Our client was running both Dynamics GP and BatchMaster. Both systems share the same database instance, with different database names. So, we started with six different databases. (This wasn’t the exact structure, but it’s close.)

BMPROD

BMTEST

BMDEV

GPPROD

GPTEST

GPDEV

(Dynamics GP folks know that Dynamics Shared database makes things even complex. But I don’t want to get into that here or we’ll lose sight of our main topic.)

To make things more interesting, we set up our own databases for development. In general, we’re strong believers in having software packages control their own databases. It makes for easier upgrades and eliminates “Your peanut butter is in my chocolate!” kind of arguments.

So, we have three more databases:

RED3PROD

RED3TEST

RED3DEV.

Now, we have a problem because all nine databases are on a single instance. And much of our Red Three (RED3) code needs to reference both GP and BatchMaster.

So how do we do it? Well, say we start with a stored procedure in Dev. We write an interface between BM and GP, and we want to reference the item master in both systems for our stored procedure. So, we have to fully qualify the object names. A simple select statement would look like this:

SELECT bmi.ITEM, bmi.DESCRIPTION, gpi.ITEMNMBR, gpi.ITEMDESC FROM

BMDEV.dbo.ITEM bmi

LEFT OUTER JOIN GPDEV.dbo.IV00101

ON bmi.ITEM = gpi.ITEM

And that’s fine. But if we want to move this code to production, we have to search and replace as we move, so it looks like this:

SELECT bmi.ITEM, bmi.DESCRIPTION, gpi.ITEMNMBR, gpi.ITEMDESC FROM

BMPROD.dbo.ITEM bmi

LEFT OUTER JOIN GPPROD.dbo.IV00101

ON bmi.ITEM = gpi.ITEM

This is bad for several reasons:

  • It leaves room for human error. Every code promotion requires making a change.
  • If you want to compare your code in production and development, whatever tool you use (we use Red Gate SQL Compare) is going to find differences that aren’t really differences.
  • If database names change, you have to go back and change your code.

A More Ideal Solution: Separate Database Instances

A more ideal solution (and not just for coding reasons) is to have separate database instances for Dev, Test, and Prod. Then our database names could stay consistent:

Production Server

GP
BM
RED3

Test Server

GP
GM
RED3

Development Server
GP
GM
RED3

But unfortunately, that wasn’t an option.

An Alternate Solution: Build Simple Views in Our Red3 Database

We decided we would never, ever reference the tables outside of GP and BM directly with our custom code. So instead, we decided to build simple views in our Red3 database for each table we need to access.

To demonstrate, let’s use the above example where I’m accessing two different item masters. I would create two simple views:

First, a simple view like this:

USE RED3TEST,

CREATE VIEW dbo.vGP_IV00101 AS SELECT * FROM GPTEST.dbo.IV00101

CREATE VIEW dbo.vBM_ITEMMASTER as select * from BMTEST.dbo.Item

Second, a simple view so my red3 code doesn’t have to access any databases:

SELECT bmi.ITEM, bmi.DESCRIPTION, gpi.ITEMNMBR, gpi.ITEMDESC FROM

dbo.vBM_Item

LEFT OUTER JOIN dbo.vGP_IV00101

ON bmi.ITEM = gpi.ITEM

Now that’s not bad. I do have to setup underlying simple views, but I can move the code between environments with no changes.

Automating the Procedure to Reduce Odds of User Error

But I want to go one better. As you can predict, when your database grows, you’ll need more and more of these views. And keeping database names straight is prone to user error. So, I automated a procedure to automatically create a view for a list of tables. (Theoretically, I could create a view for EVERY table in the packaged systems. But that creates thousands of objects I’d never use and make SSMS hard to navigate.)

As a first step, I create a simple table that contains the list of tables I want to turn into views:

And then I populate it. (I will also save all these inserts as a stored procedure so I can easily update the procedure and the table when I need to create more views.)

(I repeat this for about 50 tables.)

Now, comes Dynamic SQL. Basically, I read through the table, create a view name using a prefix, and then create the tables:

Once this runs, I have the views I need without a lot of cutting and pasting. (Note: This procedure only updates views that don’t exist already. I have other versions that drop and create the view. And I also use synonyms when stored procedures are involved.)

Hopefully, most of you have multiple environments and don’t need to resort to this kind of workaround. However, the idea of using Dynamic SQL to build other objects can be useful in other environments as well, for example:

  1. Dynamics GP: Even with multiple environments, you’ll often have multiple databases. We use this technique to build code to “union” all databases in a single view and update that view each time we add a new one.
  2. Infor SunSystems: While GP creates a database for each company, SunSystems creates a different set of tables within a database each time a company is added. We use this technique to join all tables together in one view (and then one data mart).

If you’d like to play around with these solutions, you can download the sample code files.

Do you have these kinds of issues? How do you deal with them?

* * *

For more of these tips, sign up for our newsletter on the top right of this page.

 

Finding the Relevant View in Dynamics GP

During my recent presentation on SSRS for Dynamics GP at the recent GPUG Summit conference, an attendee asked how to begin. Could he just start with the Report Wizard in Report Builder, he asked?

I replied that would work fine if he had a test database (and preferably a test system) so he wouldn’t blow up the production work. Also, rather than try to join tables directly himself, he should work with one of the provided views—this would give him and his team a major head start with names that make sense and joins that are already in place.

The challenge: While there are many places to find out about tables, including the application itself and the many blogs on the topic (such as those by Victoria Yudin and Mark Polino), I haven’t been able to find information about the views.

But as I was writing an earlier blog post about database queries, I realized that these same queries might help folks get a head start on the correct views.

To illustrate the point, I walk through the process below. Because I don’t want to scare end users (who’re the primary audience for this post), I demonstrate using sample data and leave the actual queries for the end of this post. (You’ll also find a link to a spreadsheet with both the queries and complete sets of data at the end of the post.)

Start with a Role and Find the Views that Role can Access

Dynamics GP sets up many database roles to control data access for reporting. The role names are fairly obvious. Here’s a list of “rpt” roles delivered standard with the TWO database:

(See query 1, below.)

Looking through the list, you can probably guess which role has access to the data you’d like to see.

Once you have the role, you can find what views that role has access to. For example, I checked which views “accounting manager” has access to and received this list:

(See query 2, below.)

Again, the names are pretty straightforward.

One more query. After working with the system, you’ve started to learn a few table names. Or perhaps you found a name through an online tool or a website that covers GP. But still, you don’t want to create all your own table joins. So, I created another extract where you can start with a table name and see which views display its data.

Here’s an example:

(See query 3, below.)

One point on this last query: I didn’t look at every single view in the database, just the views report writers have access to. Also, views are built from other views, not just directly from tables—but I haven’t looped recursively to find every table that supplies information to any given view.

Queries Used in this Post

Download our Excel spreadsheet with the queries and complete sets of data from TWO.

Sign up for our newsletter by entering your email in the box on the top right of the page.

 

Data Access for Reporting in Dynamics GP

During a session at the recent GPUG Summit, an attendee asked how to find out which users had access to which database objects. (This was in the context of a conversation about setting up SSRS and using the various database roles that GP provides for reporting.)

I responded that you can find this out using the system views which are part of SQL Server. To demonstrate the point, I’ve created two queries below. You can combine them, but for illustrative purposes I’ve divided the process into two steps.

Step 1: Find out which roles have access to which objects

The purpose of the first query is to find all objects that the given role has access to. In this example, I’m selecting by role using the “rpt_accounting manager” role. You might also select by permission type (e.g. UPDATE, INSERT OR DELETE permissions)—and thereby tell auditors that only one role has rights to perform those actions.

Here’s a sample of the output:

Step 2: Show all users assigned to a role

The second query takes a role and finds all the users assigned to it. Please note: Theoretically, roles can be assigned to other roles and those roles assigned to yet other roles. So, this one-level query won’t work in all circumstances. But for most folks, this will work just fine.

The Microsoft website has a lot of documentation on system views. Search for any of the views used in my queries above with T-SQL, and you’ll find what you need. (Note: For a “sys.” search, Google “sys objects,” otherwise you end up on the wrong page).

If you’d like to play around with this sample data and queries, you can download our speadsheet.

Sign up for our newsletter by entering your email in the box on the top right of the page.

 

Get tips and insights delivered to your inbox

Start a conversation with us

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

Request a Consult