Best Practices - G/L Reports

Overview

Using Excel's formatting capability, you can have almost unlimited potential for determining how your reports will look.

You also have a great deal of flexibility on how you use the Jet Excel add-in's capabilities to find and display the data for your report.

There are certain techniques, however, that work better (and faster) than others. As an example, let's look at a simple G/L-Balance report.

Examples

Static Report - slower

One common type of report is what is call a "Static Report". In a static report, much of the information is already entered in the report by hand, and then other information is drawn from the database.

In this example:

All of the account numbers and names we are wanting to see have been entered by hand. Then, the Jet GL() function has been used to just compute the current balance of each account from our NAV 2013 data source. Running this report would result in one query being sent to that data source for each account displayed in our report.

Before we run this report, let's look at another way we could design it so that we can compare the two methods.

Replicating Report - faster

In this example, a Jet NL("Rows") function has been used to gather all the account numbers we want to see:

In the next column, we get the name of each of those accounts:

And finally, just as in the Static example, the GL() function is used to compute the current balance of the account.

Each line of this example involves three separate Jet functions [one each of: NL(Rows), NL(First), and a GL() function].

Given that this example uses 3 Jet functions for each account (as compared to 1 Jet function for the Static example), you would probably expect this method to take 3 times as long to run as the Static example.

Optimization

Excel and most databases - including Dynamics NAV - are able to perform calculations very quickly. Much of the time involved in reporting is taken up by the communications between the database and the reporting tool that is requesting the information.

Jet Reports (version 2013 and higher) includes a "Function Optimization" feature that is able to optimize various aspects of Replicating-type reports in order to minimize the amount of data that has to flow back and forth between Jet Reports and the NAV data source.

Comparison

Using the replicating method with our sample NAV 2013 database required 97% less time than was needed by the static example to get the same results.

Conclusion

The combination of Jet Reports and Excel provides you with great flexibility for how to design your reports.

Keep in mind, however, that some design strategies work better than others.

Resources

More information about report design can be found in this Help Center.