Creating a Summary/Detail Report for Jet Reports and Dynamics NAV

One weakness of NAV Account Schedules is its inability to specify a range of accounts (or a totaling account) and then give details on the fly.

Unfortunately, when my client went to their first Jet consultant with this issue (thinking that Jet Reports could do better), they got the same answer. If you want to see detail, the consultant said, you have to enter EVERY account you want to see. (Which is something of a maintenance nightmare.) Further, it just isn’t true. In today’s post, I’ll show you how to do it.

I’m going to divide the process into three parts:

1. How to enter a range of accounts and see the detail
2. How to add a total to the range
3. How to create one report that can show either detail or summary, depending on your parameters.

These posts assume you have some basic familiarity with Jet Reports and the special functions it provides for Excel. You might also want to have some familiarity with GL, NL, NF and hide functions in Jet.

For my examples, I’ll use the Cronus, USA company.

Let’s start with a simple example in Jet, using the GL function to return the amounts we’d like.

As explained in an earlier post, I’m using accounting periods for my report and not dates.

In this example, I’ve entered the same accounts in three ways:

1. As individual posting accounts
2. As a range of accounts
3. As a totaling account.

I want to point out two key things:

First, the amount columns (G,H,L and M) all use the GL function to return the information we want. We can see the formula, and here’s an example of it:

=GL(“Balance”,$B7,CYPSD,CYED)*$E7

Second, we can’t just rely on the GL formula. We need to remember that, unlike many financial report writers, Jet also creates regular database reports.

Entering a Range of Accounts

So, let’s check out the new report design. In it, we use two basic functions to retrieve non-financial data: NL and NF.

We start by listing all our revenue accounts 41000..41999. Then we place that range in column B.

Note that column B is set to hide when the report runs:

In column C, we use our NL function. As you know, NL function can do many things. In this case, we ask it to create a row in our Excel worksheet for every record returned from the specified table.

Note that the formula appears somewhat cryptic in the spreadsheet. But if we look at the formula itself, we can understand what’s going on:

=NL(“Rows”,”G/L Account”,{“No.”,”Name”},”Account Type”,”Posting”,”No.”,$B$8)

But the easiest way to see the formula is by clicking NL in the ribbon:

Here, we see that we’re asking for rows to be returned from the table “G/L Account” where the chart of accounts is stored. We ask for Fields “No.” and “Name” to be brought in. And we’re filtering by “Posting” accounts. Most importantly, we reference the range of accounts that we setup in our spreadsheet, which Jet helpfully show us to be 41000..49999.

So, the NL function looks good. But we need to do one more thing. We need to put numbers and names into our report. And we’ve done that with the NF function, which we see in columns D and E.

In column D, the formula refers back to the cell with the NF function and asks for “No.,” (i.e. the account number):

=NF($C8,”No.”)

Similarly, column E asks for the name:

=NF($C8,”Name”)

We can click on NF to see the formula structure clearly:

“Key” is where we’re getting the data and “Field” tells us what we want to see in this cell.

We then update our GL formulas to reference this cell:

=GL(“Balance”,$D8,CYPSD,CYED)*$F8

Now, let’s run the report and see what happens:

And we have a list of all the accounts we want with the appropriate numbers.

Yay!

When Jet ran, it inserted one row per account.

Before we started we had totaling formulas under our data:

For example, take a look at the current MTD column. Ideally, we’d like to see this automatically add up all our inserted rows. (In practice, I use SUBTOTAL instead of SUM in most of my reports, but I don’t want to confuse things here.)

Let’s see what happens:

Unfortunately, the formula didn’t change. It’s still only includes row 8.. How can we work around this?

Let’s try inserting a row to “anchor” our formula:

The formula now spans H8:H9.

Will this work? Let’s try it:

And it does!

We can, if we want, place a hide command in that row so we won’t see it when the report runs:

And when we rerun it, it still works:

The hidden row doesn’t appear, but the formula still works.

You can repeat the above process for as many ranges as you’d like.

So, we’ve seen how we can get financial detail for a range of accounts. Now, we want to go a step further and create a summary or detail version of our report.

We start by having a cell where we can enter “S” (for summary) or “D” (for detail).

So, we create a named cell on our parameter page where the user can select “Summary” or “Detail.”

Now, when we return to our report page, we need to make two changes:

First, we add a formula for each row so that if we want summary, the row will be hidden. We do this by displaying the word “Hide.” (This works because at the top of the column we’ve entered “Hide.”)

=IF(SummaryDetail=”Summary”,”Hide”,””)

Second, on the actual summary row, we note that we only want to see a name if we’re summarizing:

=IF(SummaryDetail =”Summary”,”Revenue”,””)

So, if we run this as detail, we see what we received before:

And if we choose summary:

Perfect!

Feel free to comment below if you have any questions.