Dynamics Business Central Nav Blog

I get calls from clients to answer auditor questions on a somewhat regular basis. These questions usually start with the accounting software and then drill down to the database level. Then, I often get this entirely reasonable question: Who can change data on SQL Server?

This is, as I said, an entirely reasonable question. Unfortunately, sometimes the person asking the question knows next to nothing about SQL Server. Which makes it difficult to answer this question without launching into a lengthy explanation of SQL Server Security 101.

(It’s disheartening when this happens. You would think that a person working for a large national firm sent to audit a system would have some knowledge of the database. It’s not like we’re using dbase or internally described files on the AS400. Yet, in talking to other folks, this is far from an uncommon problem.)

So, to save you the trouble, I’m going to walk through SQL Server Security 101 (or, more accurately, 000, or maybe -101). You can use it to explain to non-IT people how SQL Server security works.

To capture all this in a short series of posts is going to be a challenge—and I’m sure I’ll have to oversimplify.

The situation reminds me of one of my college professors. When I argued that a question on a western civilization test was way too big for a test, she stated that every question has a five-minute answer, an hour answer, a term paper answer and a thesis answer. And you have to learn to give the appropriate answer for the time given.

Here, I’ll be giving you the five-minute answer.

You’ll note that I’ve divided this series into three posts:

1. Pieces—The pieces that make up SQL Server
2. People—The people or groups that get access to SQL Server
3. Permissions—What the people can do to the pieces.

I’m purposely going to start by using language that’s not special to SQL Server but can be understood by anybody.

Again, the goal here isn’t to describe how to do each thing. It’s to describe how to explain each thing to an auditor who will probably never use SQL Server.

Part I: The Pieces

SQL Server is complex software. Sadly, it doesn’t have a simple switch you can flick on and off to denote “keep end users from doing anything that’s going to cause a problem.” Instead, it’s made of up a variety of pieces that combine to make more precise distinctions.

So, to understand SQL Server security, you need to understand the pieces.

To start, you need to know four things:

• Instance
• Database
• Schema

Below is a visual representation of SQL Server. (In this case, I’m using Dynamics NAV, but it could be any software.) In it, I’ve circled examples of these four things:

Let’s start at the top.

The Instance

In the example, the first line is the name of the instance. (I’ve greyed out the actual company name for privacy.)

Essentially, an instance is one installation of SQL Server, meaning that someone took the software and ran the install program. Mostly (but not always), you’ll find one instance of SQL Server running on one physical server or virtual machine.

The instance is important because every user who has access to the system will need to be set up at the instance level. (We’ll get there later in this series.)

The Database

The database in this example is Cronus.

A database is really just a collection of data about a given topic. In the world of accounting software, you may have separate databases for production and testing of a given software. You may also need, as in Dynamics GP, a separate database for each company that is setup.

From an audit perspective, you want to focus initially on the databases which hold production data.

Databases are important because you can grant users permission to do things at the database level and these permissions will give them rights to the next lower levels.

The Schema

In this example, the schema is dbo.

A schema is a way to organize the various tables and other objects that make up the database that store the data.

In most accounting systems I’ve worked with, only one schema exists per database, and it’s called dbo. However, you can have multiple schemas. Again, this is important because you can grant access to a schema and that access will apply to lower levels.

The Tables

Tables are where the actual data is stored. For example, in Dynamics NAV General Ledger, transactions for a given company are stored in a table called Company$G_L Entries. In Dynamics GP, General Ledger Transactions are stored in GL20000 and GL30000.

(Of course, databases contain other objects besides tables (e.g. views and stored procedures) but today, I’m going to focus on tables.)

Tables are the lowest piece to which you can assign security in SQL Server. (At least, in all accounting software I’ve seen.)*

So, to review, we have four pieces in SQL server:

Instance->Database->Schema->Table

And security settings at higher levels affect security settings at lower levels.

In the next post in this series, I’ll discuss the second part of the security puzzle: People.

*Theoretically, there’s a feature in SQL Server called row-based security that allows you to assign security to parts of a given table. But I know of no accounting software that uses this feature.

 

 

As I’ve said before, the cardinal rule of well-performing reports is to use SQL Server for your processing, and not SSRS. Instead, save SSRS for presenting and distributing data. (And here’s why.)

In addition, you should create and store these procedures as views first, and then as a stored procedure, if necessary.

Continuing our discussion, let’s talk about datasets.

First, what is a dataset?

You can think of a dataset in two pieces:

• Some kind of SQL code (a SELECT statement or a stored procedure)
• Additional logic performed to the results of that SQL Code once the code is returned to SSRS.

Now let’s look at specific types of datasets and some scenarios.

Multiple SSRS Datasets for Parameters

Many reports require more than one query. To illustrate, let’s take a simple example I’ve built over AdventureWorks. This simple report gives us sales and allows us to select those sales based on customer and/or item.

Now, it would be theoretically possible to just have the user key in the appropriate item number. But that’s not going to make anyone happy (especially if they’ll eventually to want to select multiple items or customers). So, this report has two additional data sets: one to provide values for the customer and one to provide values for the item.

Let’s take a look at the report:

We have five datasets, but they’re not all the same.

The first dataset (called “DataSet1”), is used in the table and provides the bulk of the report output.

The other four datasets—“Territories,” “Categories,” “FiscalYears,” “FiscalPeriods,” and “Quarters”—are only used for parameter selection.

Shared Datasets vs. Embedded Datasets

Continuing with this example, only DataSet1 is unique to this report.

You can see this by right clicking on the dataset and choosing properties:

Now, we can see that the DataSet1 is “embedded in the report.” This means that this particular set of “query plus extras” can only be used by this report.

The other four datasets use the shared dataset option. That means that the query was defined BEFORE we created the report.

If we look on the right side of Visual Studio, we can see all the shared datasets that I’ve created for this particular project. I’m using most, but not all of them in this report.

When to Share Datasets

When I work in development, I almost only use shared datasets for parameters. If I have complicated logic that might power multiple reports, I’ll create a view, stored procedure or even simple datamart rather than create a complex dataset (as discussed in my post “SSRS Consultant Tip: Stored Procedures vs. Views for SSRS Reports“).

To keep things clear, I generally name datasets as I have here: “DataSet1” for the dataset unique the report and then real names (e.g. “Products,” “Territories,” “FiscalYears”) for shared datasets.

Multiple Datasets in One Report Beyond Parameters

In the example above, I only have one main dataset. But sometimes I’ll use other datasets in one report.

For example, for a NAV customer, I created a report to show all invoices that have been selected for payment. The user then asked to see all vendors with a credit balance who were not receiving checks at that time. In other words, every time the user asks for step one they also want to see step two.

Therefore, I created another dataset and another table in the report. But essentially, they’re two reports in one because they’re always requested together.

A No-No: Combining Data from Multiple Datasets With the Look Up Function

SSRS has the ability to combine data from multiple data sets. Going back to my cardinal rule about using SSRS only for presenting data and reporting, I don’t think this is a good idea.

Yes, it’s possible to link each dataset in the report to a different data source and therefore a different database (which I once did, combining Unix data and AS400 data in one report. Oh, the horror!). But the performance suffers.

I intend to write more posts on this topic as this is a fairly common scenario for users using multiple software packages.

 

I hope I’ve convinced you in my previous post to develop and store your SQL on the database and my rationale for that argument.

The next question is how to create and keep this code on the server. You have lots of options, such as views, stored procedures, functions, simple data marts and even SSAS.

In this analysis, I’m going to focus on views and stored procedures. That’s where I spend most of my time and is most relevant for “next level” super users.

Generally, I like to start with views before going to stored procedures. I do this for two reasons:

1. Views get along with most report tools

Almost every reporting tool I’ve ever used makes it easy to use views. After all, views look just like tables to a reporting tool. The same can’t be said for stored procedures.

For example, if you connect to SQL Server from Excel, you’ll see views and tables, but no stored procedures:

So save yourself some trouble and go with views wherever you can.

2. You can combine a view with other views

At Red Three, we often create a basic view that we can then combine with many other views.

For example, we often work with Dynamics GP and NAV. Both systems have situations where the data is stored in an entity-attribute-value format, which is less then ideal for reporting. In Dynamics GP, for example, additional customer data may be stored in Extender while in GP additional customer data may be stored in Dimensions.

To deal with this problem, we’ll create a master customer view of this data and then “normalize” it so we can then combine that view with many others.

(I acknowledge that this can become too much of a good thing. Combining too many views can produce a mess of unsupportable code.)

All that said, I do still use stored procedures regularly. Sometimes, a report will require multiple steps or a calculation that can’t be handled in a view.

Processing Speed

You may have heard that stored procedures are faster than views, and therefore preferable. (A consultant once told me that stored procedures are ALWAYS faster than views.) It’s true that a stored procedures doesn’t need to be compiled (as views do). And I suppose that this extra time might be meaningful if a report is going to run thousands of times.

However, I live and breath mid-market companies, and this difference in processing speed is NOT A FACTOR in performance in my universe. (At some point in the future, I am going to spend some time talking about important performance differences. But this isn’t one of them. For an example of something that does matter, see “SSRS Tip: Put Parameters in Your Query, Not Your Filter.”)

 

As I started this series, I emphasized the importance of using SSRS for what it does best, not for everything it can do. I ended the post by noting the importance of having someone on your team with knowledge of T-SQL and access to the database.

Whenever I make this point, whether in presentations or with colleagues, they ask me to back up my argument. So let me lay it out for you here:

1. You can’t assume that SSRS will be your only reporting tool

Over the years, many IT shops have tried to control the number of ways folks see data. And this control makes sense, in theory. There’s value in having at least some of your staff up to speed on your preferred tool. But in reality, end users like the tools they like, regardless of anything you have to say about it.

Because of this human proclivity to use whatever damn tools we want, a more realistic approach is to say, “If you’ll let me (or someone on your staff who knows what they’re doing) develop your queries, you can then use whatever tool you want to make your presentations pretty.”

Those tools could be Excel, or Excel with an add-in like Jet Reports, Power BI or Tableau. Which tools they choose doesn’t really matter. If you build the logic once—and know it’s good—you’ll have a greater sense of comfort (if not complete ease) when that logic gets used with other tools.

At the least, you’ll be reasonably confident that the data is accurate.

2. Debugging in SSMS is much, much easier than debugging in SSRS

Microsoft makes some great development tools. I work with both SSMS and Visual Studio on a regular basis. However, even though SSDT (SQL Server Data Tools) is an add on to Visual Studio, it isn’t that nice to work with if you’re developing queries while building reports.

Indeed, just this week I found that the query designer in SSDT isn’t just hard, it’s sometimes wrong. I had copied code from SSMS into the Query Designer. I wanted to quickly see some results. But when I did, I realized that SSDT had rewritten my query and actually changed my logic. But I digress.

Further, I find it’s crucial to fully verify data before formatting. Therefore, I’ll often write my queries, dump output to Excel and verify—all before I get around to creating reports (or passing the data to someone else to create reports). This process is much, much easier in SSMS, especially if you, like me, buy inexpensive tools like SQL Prompt to make development easier.

3. Six months, one year or two years later—you can still understand what happened

My point isn’t to just put some code in SQL server. The idea is to do most of your work in SQL server. I’ve seen situations where the report developer started with a view (or stored procedure) on the database, and then created all kinds of additional calculations in the dataset.

Then, six months or two years later, something needs to be changed. At which point, the developer (or even worse, a new developer), will spend days, weeks or months clicking on formulas, trying to figure out where the calculation came from.

In this respect, SSRS isn’t any worse than many other tools. (I’m thinking of the many years I spent with Crystal Reports). But still, if you don’t want to create “write only code,” it’s better to get your stuff in T-SQL and store it as a view, stored procedure or even datamart in your database. This way, it’s much easier to figure out what happened when you open up your report years later.

In my next post, I’ll delve into further into this topic of how to store code on the server.

 

I recently presented on SSRS at the NAV Focus Conference in St. Louis. As often happens at these events, I wasn’t exactly sure who was going to show up and what they would already know. (Especially at an event like this, which had a development track but with no specified levels.)

In any case, it became clear to me that many folks in the audience could benefit from a series of posts aimed at a “SSRS 102” level. They’ve mastered “SSRS 101” material—they can create a basic report and know what data source, datasets and reports are—but now, they’re ready to move up.

The Cardinal Rule of SSRS Performance Happiness

To move from a SSRS 101 level of understanding to a SSRS 102 level, you have to understand what SSRS does well—and what it doesn’t.

Like most software tools, SSRS can do many things. Some of those things it does really well. Other things, not so much. When you get your knowledge from books (and I read and scan through lots of books), you tend to learn about what you CAN do with a program, rather than what you SHOULD do. And when it comes to SSRS, this is an important distinction.

In my next few blog posts, I’m going to try and take you to the next level of understanding. Everything I describe will stem from my cardinal rule of SSRS performance happiness:

SSRS is an excellent tool for presenting and distributing data. It’s NOT a good tool for querying and processing data. That’s what SQL Server is for.

Let’s dig a little further into the implications of this rule:

• You want queries to return ONLY the data you need for SSRS. (For more on this, see my post “SSRS Tip: Put Parameters in Your Query, Not Your Filter.”)
• You should mostly perform aggregation and sorting in SQL server and not in SSRS. (Which we’ll cover when we discuss detail reports.)
• If you need to combine data from multiple sources, it’s better to do that in SQL Server rather than have multiple data sources in one SSRS report.

In other words, use SQL Server for data crunching. Save SSRS for reporting.

Someone on Your Team Needs T-SQL

Following from the above, there’s one more key point I want to make: you cannot build efficient, easy to maintain SSRS reports without someone on your team knowing T-SQL and having access to the database.

Yes, there’s certainly a role for folks with less raw database skills on reporting projects.

Certainly, at Red Three we’ve successfully trained many super users on SSRS. But even skilled SSRS super users aren’t going to write the SQL themselves. Instead there going to take those queries and create many different kinds of pretty presentations.

In my next post, I’m going to elaborate on my reasons for using SSRS only for what it does best.

 

In May, I’m presenting at the NAVUG Focus conference on the topic of “BFFs with SSRS.” (Not a title I chose. Please.) In preparation, I’ve been doing more work on the database, and I thought it worth writing up some of my findings.

In this post, I’m going to review three fields that are in the NAV table but aren’t (or aren’t obviously) in SQL Server:

• Option fields
• Flow filter fields (FlowFilter)
• Sum index fields (SumIndexFields).

If you’re new to NAV, you should start with my Understanding the NAV Database post from last summer.

Option Fields

In my previous post, I wrote about how to find the table name from a NAV page. Now, let’s look at another example, using the Vendors page and underlying table.

Here, I’ve filtered to show three vendors and the value for “Blocked” (which I’ve set):

If we look at the table information, we can see that the underlying table is indeed Table 23 Vendor:

And there is a field called Blocked:

We can now query the database to look at the value for Blocked:

We see that the field Blocked is there. But rather than alpha values, we see only numeric values. We can’t see blanks, “All,” or “Payment” as we saw on the screen.

So how can we figure out those values?

There are three different methods we can use:

1. The NAV Development Environment
2. RapidStart
3. The data stored in the database.

1. Using the NAV Development Environment to Find Blocked Values

You may or may not have access to the NAV Development Environment. (Often, folks writing reports over SQL databases aren’t the same folks who have access to NAV Development.)

But if you do have access to NAV Development, you can figure out the mapping between name and number fairly easily.

From Object Designer, we find the table:

We click on “Design” at the bottom.

Then, we scroll through Field No. until we find the Blocked field:

We then choose “Properties” or click shift-F4:

Then, we find “OptionString.”

In this case, we see: ,Payment,All

This expression works as an array, where each value in our array is separated by a comma. We start with zero (which occurs before the first comma) and then count forward.

So, here the blocked values map to the string as follows:

0 = No value (or blanks)

1 = Payment

2 = All

For another example, let’s look at Table 36 Sales Header:

In this case, “Document Type” has the following options:

Quote,Order,Invoice,Credit Memo,Blanket Order,Return Order

Which we can map to numbers as follows:

0 = Quote

1 = Order

2 = Invoice

3 = Credit memo

4 = Blanket order

5 = Return order

2. Using RapidStart to Find Blocked Values

If you don’t have access to the NAV Development Environment, but do have access to RapidStart, you can find blocked values by exporting a sample set of data.

Here, I’ve created a RapidStart package to export the Vendor table:

I choose “Export to Excel” and get the following spreadsheet:

We see that the Blocked column has the string and not the table.

However, if you hover over the column name, the mapping appears:

3. Using Data Stored in the Database to Find Blocked Values

Field level information is actually stored in the Objects table in the database. However, it’s not in a format that’s easy to use with just T-SQL. Therefore, I’m working on a separate post that will show how to pull that data.

Flow Fields and Flow Filters

Flow Fields aren’t in the SQL table at all. Rather, they’re essentially queries of other tables based on:

• Fields that are actually in the table.
• Flow filters (i.e. fields described in the NAV table but are only entered by users when performing filter operations).

To illustrate, let’s take the field “Balance”:

If we look at the field properties, we see FieldClass, called FlowField, and CalcFormula, which kind of looks like SQL:

Let’s look at CalcFormula in detail by clicking on the box to the right of the line:

Now we can see what the system is doing.

Method: The method is Sum. Other options are Average, Exist, Count, Min, Max and Lookup. (Lookup returns one value. All the rest should be obvious to the SQL folks reading this post).

Reverse Sign: Reverse sign simply does what it says. The number appears as positive even though (because of certain NAV conventions) it’s stored as negative in the related table.

Field: The field we’re going to return.

Table Filter: Again, let’s take a closer look:

Table Filter shows how NAV selects records from the table. These fields exist as fields in the database. In this case, as Vendor No. (which is fairly obvious).

As noted above, Flow Filters are defined in the table, so users may use them for filtering. They are not stored in the SQL Database.

A side note: As with the options strings, flow field definitions are stored in the Objects table as metadata. In a future post, I’ll explain how to read these if you want to keep everything as SQL.

Sum Index Fields

SumIndexFields are not separate fields in either the NAV Table or SQLServer Table. Instead, they’re fields that are aggregated based on a particular key. You don’t have to use SumIndexFields, but they may speed up your query.

Many ERP systems keep both transaction tables and balance tables. So, for example, in the general ledger, you could have a GL transaction table as well as a table that maintains balances per period for given accounts.

In NAV, we only have the transaction table—in this case, G/L Entry. So, if you’re writing in SQL, you might think you have to add up all the records in that table to get a balance, which is inefficient. And that’s where SumIndexFields becomes useful.

SumIndexFields relies on indexed views in SQL server. Every time a G/L Entry record is created, matching aggregates are also updated.

Here’s an example. We open the G/L Entry Table:

From the menu, we select View –>Keys:

Then we come to the list of keys. (These are just ways the table is sorted. They aren’t really keys in the SQL sense.)

Next to each key, you have SumIndexFields. Let’s take the example of the G/L Account No. and Posting Date keys. If we click in SumIndexFields, we see the fields that are summarized for each combination of the key:

We can actually see the view in the SQL database. The view number equals the row number on the key screen – 1.

In our case, we want: dbo.CRONUS USA, Inc_$G_L Entry$VSIFT$1

If we look at the code, we can see how the aggregate is created:

It’s a simple grouping on the key, plus all the fields we asked for are summarized.

One thing to note: As you can see, the indexed view only has the key fields we care about. To get other information, you’ll have to join back to G/L Accounts—and this may affect the overall efficiency of your query.

 

I’m working on my first paid project using Azure. Previously, I’d set up test databases in Azure but nothing focuses the mind like having to get something working for a real client. I am hardly an Azure expert, but most of my clients are interested in or using some sort of cloud application, of which Azure SQL database is one. So, part of my motivation for writing this post is to get my thoughts clear.

Why Azure SQL Database Should Work for Me and My Clients

In the SQL server universe, I spend most of my time writing queries and doing integration development on SQL Server. I’m not a production DBA. I only learned about the back end to help various clients survive SOX and other financially related orders. So things like backups and redundancy are areas where neither my team nor I have in depth experience. Most of our clients have someone else (whether an ERP VAR or an in house IT group) who’s responsible for keeping the infrastructure up and running.

For me, this is the big selling point of Azure. If I don’t have to worry about the back end stuff, it makes it easier to create our solutions.

Also in Azure’s favor: developing on Azure SQL Database is much the same experience as any other database—whether on-premise or in a hosted environment like Rackspace. Indeed, we do the vast majority of our work remotely. So, while there are some small annoyances (as long as you stay within one database), it feels no different.

Why Azure SQL Database Doesn’t Work for Me or My Clients

There are two big reasons why Azure SQL database doesn’t work for me:

• Lack of SSRS (SQL Server Reporting Services)
• Cross database functionality.

Lack of SSRS

Azure SQL Database is well named. It is SQL Database, not SQL Server. SSRS is not part of the package. (Other parts of SQL Server aren’t included either, but we don’t use them as frequently).

Therefore, if you want to use SSRS (which we do. Always.), you’ll be spinning up another server. And this means you’ll end up responsible for much of the maintenance you’d hoped to avoid with Azure SQL Database. Granted, the scope of maintenance can be much less. But you still have to deal with it.

Cross Database Functionality

In standard SQL Server, accessing another database is as simple as changing your table reference from dbo.mytable to mydatabase.dbo.mytable.

In Azure SQL Database, it’s not nearly as simple. You can’t even switch databases in SSMS without opening a new query.

There are ways to access other databases, but they’re much more cumbersome. (I won’t describe the procedure here. But if you’re interested, I recommend these posts by Paris Polyzos and Torsten Grabs.)

In my business, I work across multiple databases all the time. It’s part and parcel of our commitment to provide “the reports you need from the software you already have.”

And I’ve written posts about how to manage issues of cross database code. (For example, this one on a Dynamics GP integration.)

Given the multi-database nature of our work, Azure SQL Database’s restrictions on accessing other databases would require a major rethink. This may be worth it. But not right now.

Conclusion

Azure SQL Database can make a strong case for some uses. But considering how my team and I spend most of our time, for now we’ll be sticking with SQL Server in a virtual machine.

 

Recently, I read a blog post by Andy Leonard on his SQLblog.com titled Long Poles and Critics. In this post, Andy discusses the importance of not being too quick to criticize other’s work when he’s called in to complete or extend software reporting projects.

Almost by definition, consulting is an arrogant profession. SSRS consultants are paid considerable sums to solve other people’s problems. So being quick to criticize is endemic.

Still, I agree with Andy. I’m careful to give those who’ve come before me the benefit of the doubt. But it’s not because I don’t know the full story, as Andy writes. It’s because I know that, while I’m proud of most of the work I’ve done, anyone looking at some of my projects would scratch their heads.

If you were to ask me, “What were you thinking?” I could tell you exactly. Here are some examples:

1. The Simple, Quick Solution That Took Over the Universe

This scenario has come up more than once, most recently a little over two years ago. We had done a bunch of data integration and reporting work for a client. Then they asked us to build a simple tablet app that could replace a spreadsheet they were printing and filling out by hand.

Our software development services were focused on data work. So, building tablet apps for the client’s warehouse was a little out of our ballpark. But the spec was simple. So we said okay.

Eight months and around 1500 billable hours later, the “simple spreadsheet” app had become what we (only half jokingly) referred to as their new warehouse management system. Oy!

2. The “We Have to Go Live” Work Around

Another fun situation. I had a role in a Lawson implementation project, focused mostly on converting data and some customization. Other consultants were in charge of writing a major interface between the company’s AS400 system and Lawson on Unix.

Two weeks before going live, the client asked me to test the interface. It took me five minutes to break it in two. It wasn’t going to work.

So, yes, I did criticize the other guys. But then my own solution, which I had to put together with whatever was available, was not my prettiest work. That’s what happens when Perl and COBOL are the only languages you have, the setup is beyond complicated, you have to work with a second rate integration tool, and time is up before you start. But we went live on schedule. And my workaround worked.

A few months later, I was able to clean it up. But anyone who’d seen the code in the meantime would think I was on drugs.

3. The Only Guy to Get Anything Done

This story isn’t about a program we wrote, but a program we had to upgrade. Essentially, we upgraded the existing “poor man’s workflow.” The workflow started as a RPG program. Which called a Java program. Which sent emails to users in Lotus. Who clicked on the email to approve changes. Which sent data back to Java. And then back to RPG. Which called an API in the ERP system.

It wasn’t pretty.

But the final result was highly useful to the business, and the program ran pretty well for many years. And the client’s IT department had the tendency to design beautiful solutions that worked in PowerPoint and nowhere else—so the client had no real alternative.

With these experiences, I find it pretty easy to limit my criticism of other’s work.

If people claim things that are absolutely false, then I’ll call them out on it and won’t be shy. But I won’t critique things because they aren’t elegant or don’t show best practices. Because I remember much of what I’ve written. And it isn’t always pretty.

 

Tracking changes is a good idea in any ERP application, including Dynamics NAV. And Dynamics NAV’s “Change Audit” feature is a good place to start because it can comprehensively track changes to your tables.

That said, it has some limitations in reporting that are important to know.

In this post, I’m going to review some of the limits and also point out a few other key things to remember when tracking changes.

Setting Up Change Log

Setting up the change log is easy.

First, we navigate to Change Log Setup. Click the check box “Change Log Activated”:

(This doesn’t actually do anything. It just enables the next step.)

From this page, we choose the Actions tab and click on Tables:

We come to a list of our tables. I’ve filtered the list so we can work with the vendor table.

(Vendor table is the table that probably everyone should track changes to:

Each table has three options:

1. Log Insertion—when we add a record (see caveat below)
2. Log Modification—when we change a record
3. Log Deletion—when we delete a record.

On each of the three fields, we have three options:

1. Leave blank—to do nothing
2. Some Fields—to track some fields
3. All Fields—to track all fields.

If we select “Some Fields,” we can then specify the fields we want to track:

Remember: We must close the client for the change log to take effect!

Tips on Tracking Fields

1. Tracking “All Fields” for insertion doesn’t do what you think it does.

If you’ve used Dynamics NAV for any amount of time, you know that NAV adds a record into the database as soon as you’ve keyed the absolute minimum amount of information. And what’s the result? Not much shows up in the change log as an insertion insertions. For example, with vendors I find the only field I care about that comes through on an “insertion” is vendor number.

Even the name, when entered for the first time, comes through as a modification. Both the insertion and the modification will have similar datetime stamps, so you can put the pieces together.

2. Limit the number of fields you track.

As you’ll see in the next step, having too many fields creates a lot of data. In addition, the out-of-the-box report and screen isn’t fantastic. Indeed, the screen can become painfully slow.

Looking Up Changes

Once we’ve started tracking changes, we want to see the information, naturally.

To do so, we go to Change Log Entries:

Here, I’ve filtered the screen on Vendor and added one new vendor.

As you can see, adding just one vendor created 10 insertion records and another five modification records (which is the data I actually care about).

It’s hard to show all the data in one screen. If we scroll right, we’ll see the old and new values for the fields. In this case only new values:

Another option is to print the data. But, please, make sure you’ve filtered to see only the records you want. Otherwise, you’ll kill a ton of trees.

To print, merely choose print from the Actions tab:

Unfortunately, it’s not the most usable report ever created:

However, if you have some in house technical help, I can show you how to create a better report in SSRS. Stay tuned for details my next post.

Archiving Purchase and Sales Quotes/Orders in Dynamics NAV

One more thing: The change log isn’t the only way to save old versions of things. You can save archived versions of Purchase Quotes, Purchase Orders, Sales Quotes and Sales Orders.

To set up archiving, go Purchases & Payables Setup. Simply click Archive Quotes and Orders:

The same option is available in Sales & Receivables Setup:

Viewing Archived Purchase and Sales Quotes/Orders

To view archived purchase or sales quotes and orders, go to the relevant archives. They are:

1. Purchase Quote Archive
2. Purchase Order Archive
3. Sales Quote Archive
4. Sales Order Archive.

Have questions about the Dynamics NAV SSRS change log or purchase/sales archives? Feel free to post below.

 

Here’s another installment in my series of posts on SSRS consultant tips for super users and advanced beginners. (See my post Put Parameters in Your Query, Not Your Filter for the first installment.)

Part of what I enjoy most about working as a SSRS consultant, and performing SQL Server consulting, is the variety. Some months, I spend a lot of time writing T-SQL. Others, I’m deep into Excel pivot tables. And then others I’m writing SSRS reports.

Truth be told, if I could spend all my time doing solution architecture and T-SQL coding, I’d be happy. But that’s not my current life.

Every once in a while, when I go back to SSRS, I find I’ve forgotten a few helpful but not crucial things. So, I empathize with folks who don’t use SSRS full time and need a moment to get their heads back into it.

In that spirit, I’m going to use this post to point out one of those “helpful but not crucial” things: the importance of rounding your data instead of just changing the format.

Rounding Numbers Before Exporting to Excel

To illustrate, let’s use a simply example from the AdventureWorks Database.

Writing a report, we see that the total line amount is set to a numeric field with a total length of 38 with 6 decimal digits.

If we create a simple report (as below), we see all six decimal digits show up:

Let’s start by changing the formatting.

We change the properties for my Total Sales column to show only two decimals:

And when we run the report again, we think we’ve fixed the problem:

But have we?

Exporting a Formatted Number From SSRS to Excel

Let’s see what happens when we export from SSRS to Excel. (I work mostly with back office types, so I fully expect almost all of my reports to wind up in Excel at some point.)

In this case, the numbers look fine—but there’s a lot more detail behind the scenes?

And any finance person who looks at that number is going to ask questions.

(In this case, everything is formatted correctly in Excel. But once I did this in Dynamics NAV (which defaults to 18 decimal precision), and the field showed up with all 18 digits in the cell itself. Not a good thing.)

So what should you do instead?

Make sure that what’s in the cell matches the formatting.

You can do this in many ways, but the easiest is to create an expression that rounds the number.

In this case, let’s start by going to the reports dataset. On the fields page of the dataset properties, let’s create a new calculated field called TotalSalesRounded. The expression is simple:

=Round(Fields!TotalSales.Value,2)

Where “2” is the accuracy I want:

Then we replace TotalSales with TotalSalesRounded in Tablix:

The report looks the same in SSRS. But when we export it to Excel, the extra decimals are gone:

Rounding Your Numbers to Make Sure Everything Adds Up

I want to make one more point about how important rounding in the formula can be.
Let’s modify the report to show both the “rounded” and “just formatted” columns.

When we run the report, the columns look correct all the way down:

But if we go to the grand total row, we see something strange:

As you can see, the grand totals are different.

Why? Because the “just formatted” column adds the actual values and then formats the result.

In contrast, the rounded column works with the rounded value from the start.

As you can imagine, this “small” difference can become a big one, especially when rounding to whole dollars, thousands or millions.