SSRS

SSRS Consultant Tip: Defaulting Your Parameters

I’ve been doing various forms of systems consulting since the mid-90s. From my very first job, it was clear that the amount of credit you receive is rarely related to the amount of work involved. So, in that spirit, I’m going to help you become a reporting hero by showing you how to set default values in your SSRS reports.

As an SSRS consultant, I do the majority of my work with finance and accounting data. That means my reports generally run by fiscal year and period. Therefore, in this example, I’ll show you how to have fiscal year and period default on your report parameters. So, even if you have, say, 15 possible fiscal years, the current year comes up.

If you want to follow along at home, I’m using the AdventureWorks2012 database with the addition of three custom tables: FiscalYears, FiscalPeriods and DateToFiscalYearPeriod. (When conducting actual client work, we have more complex date tables—but that’s a discussion for another time.) I’ll include all the code at the end of this post for your use.

Step 1: Create Datasets for the Parameters

In order to select a parameter, we need to have a set of available values to work with.

So, I’ve created two simple datasets, one for FiscalYears:

And one for FiscalPeriods:

Now that I have my datasets ready to go, I can use them with my parameters.

I’ve create a very simple report that allows users to show order for a given year and period:

If we look at parameter properties, we can see how we’ve set the available values to use our FiscalYear and FiscalPeriod datasets.

For example, here’s my FiscalYear parameter:

And here are the available values which are set to come from the FiscalYears dataset:

(The same setup also applies to FiscalPeriod.)

When run, the report looks like this:

Now, if we use the dropdown box for either Fiscal Year or Fiscal Period, we see all possible values for our dataset:

Now, it’s NOT a big deal for the user to select from a list. But again, the idea is to make things look good. And if you have lots of fiscal years, it’s a little annoying to have to scroll down to choose the current year 99 percent of the time.

Now that we have our report, let’s see how we can set defaults.

Let’s start by setting the default value in the report itself.

Step 2, Option A: Set the Default with a Hardcoded Value

In general, I don’t like to set hardcoded values in the report itself. I don’t want to have to go to the report to change the value every time I run it. Still, if users generally run a report one way rather than another, it can be useful.

To set the default value, we go back to our parameter, in this case FiscalYear. We choose properties, and then choose default values.

In this case I’ve chosen to specify a value:

You’ll see that the default value is set to “Expr.” If we look at the expression, we see it’s set to 2007:

And, as soon as I open the report, I see 2007:

Step 2, Option B: Set the Default With an Expression

Of course, you can use a fancier formula. For example, it might be sufficient to default to years based on the current date of the system. The formula would look like this:

And when you opened the report, you’d see the 2017 default (which, I acknowledge, won’t return any data from AdventureWorks).

But with many reports deriving the default from current data, this might not be the best thing. We generally don’t issue financial or other statements until the time period has passed. So we need more control.

Step 2, Option C: Set the Default From Another Dataset

We can’t use generic logic to know that folks have moved from period 1 to period 2. (Indeed, sometimes I even create applications that set defaults by user name to get even more specific, but that’s beyond the scope of this post.) To do this, our basic FiscalYears Table not only has a column for FiscalYear, but also has a column for CurrentYear.

So, we can build another dataset called “FiscalYearDefault.” Through other means, we make sure we have only one year marked as current. Then we build a simple query to bring back that one value:

If we go Query Designer, we see that this is indeed the case. The one value of 2008 is returned:

Now, we go back to Parameter Properties and use this dataset as our source for the default value:

When we run the report, we see that 2008 defaults into the report:

Step 2, Option D: Set the Default Through Report Manager

There is one more way to set a default: in Report Manager. Generally, I only use this method when I’m deploying the same report to different report folders, and I want to specify certain parameters based on the users who work with the folder.

We find the report in Report Manager:

We right click to the right of the report and select Manage.

For my sample report, I created a default by FiscalYear, which SSRS shows me:

We haven’t set a default for fiscal period. But if we wanted to, we would check the default box and enter a value:

Now when we run the report, we see that Fiscal Period defaults to “1”:

One thing you should also note: If you provide defaults for all your parameters, the report will run as soon as it opens—which may or may not be what you want.

(Usually, I have at least one other parameter beyond my defaults that users must choose, but it’s something to consider.)

The code to set up the tables can be found below. If you’d like the RDL and RDS files, please let me know.

–INSERT CODE HERE–

 

SSRS

SSRS Tip: Put Parameters in Your Query, Not Your Filter

As a SSRS consultant, we’re often asked how to speed up reports. While there are many ways to do it, I would make this the top one: Put parameters in the query, not in the dataset filter, if at all possible.

Filtering is not your friend. Repeat after me: Put your parameters in your query, not in your filter.

To show you why, let’s look at two reports: one called Parameter in Filter, the other Parameter in Query. Both reports are identical except for one thing—in one report I use a filter on the dataset and in the other I use a filter in the query itself.

Note: I’m using a dataset here based on Adam Machanic’s bigproduct and bigTransactionHistory tables. The view I’m querying has about 700,000 rows—so it’s not a huge set.

When we run either report, we get the same output:

However, there is a one striking difference in the two reports: performance. Parameter in Query results come back instantaneously. Parameter in Filter results take about 10 seconds.

Why is that?

To find out, let’s take a look in Report Builder.

Parameter in Filter

In the first report (Parameter in Filter), I have a very simple layout and one parameter:

And if we look at the query in DataSet1, we can see that the parameter is not in the query:

Figure 1 – No Parameter in Query

It is, instead, referenced in the filters:

Figure 2 – Parameter Used in Filters

Parameter in Query

In contrast, in my other report (Parameter in Query), you can see that the parameter is in the query:

Figure 3- Parameter Used in Query

SSRS Execution Log

SSRS has a view in the database called ExecutionLog3. It’s useful for seeing the differences between the two reports. (You may have seen it in the initial report manager screen shot.)

Let’s take a look:

You can see the path name of the reports on the left. The columns we care about are Time Processing and Row Count.

When the parameter was placed in the filter, SQL Server returned 756,000 rows to SSRS. SSRS then had to plow through them all, which took almost seven seconds. (The processing times given are in milliseconds.) This is even though the report only printed 25 rows.

In contrast, when the parameter is placed in the query, only 25 rows were returned. Which were the rows we wanted.

So, here’s the first rule for fast SSRS reports. Let SQL Server narrow your data as much as possible to avoid bogging down your processing times. If you’re not sure whether you have a problem, compare the row count from the execution log to the row count on the actual report output. If the numbers are way out of balance, you probably do.

 

Dynamics NAV, Jet Reports

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

Part 1—Showing Detail for a Range of Accounts

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!

Part 2—Adding Totals

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.

Part 3—Creating a Summary Detail Report

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.

 

Dynamics NAV

Converting 1099 Balances in Dynamics NAV

I’m in the process of finishing up a data conversion from Lawson to Dynamics NAV 2016. The last piece to figure out are 1099 balances from checks issued before we converted. (We only converted open AP Items, so we don’t have the history necessary to calculate 1099s at year end.)

I’ve looked around the web and found a variety of incorrect and partial answers. Many solutions have a lot of discussion about invoices and credits. But I think I’ve found a much simpler way of doing this.


While converting 1099 balances is a fairly rare occurrence, you may find it useful for other scenarios as well. (For example, in many systems, I use “bank accounts” for clearing things that aren’t real payments.)

But before we start, there are three things you need to remember about NAV and 1099s:

1. If you have paid invoices in the system for the year, it’s easy to update 1099 amounts.

Just go to the vendor ledger, find the appropriate invoice, and adjust the IRS-1099 amount and/or the IRSS 1099 Code. Note that this is done at the invoice or credit memo level, not at the payment level. The fields may need to be added through choose columns:

2. 1099 amounts are specified on the invoice/credit memo and/or purchase journal line level. 

You cannot issue a payment to a vendor without a prior document and have the balance affect 1099 output.

3. You must pay a document for it to affect the 1099 balance.

If you just convert the invoice and don’t pay it, you won’t have any 1099 effect.

The Basic Point

To understand this process, you need to remember your T-Accounts. Just like when we convert open items, we want the invoice to represent our credit/debit balance for the same account, thereby having no general ledger effect. We’re going to create an invoice and matching payment that will have no G/L effect, yet still update 1099 balances.

With that out of the way, let’s get started.

Step 1 – Create a One-Time Setup

First, we need to choose an account that will be our “clearing account.” If we only have one payables account, we can use that. Or we can create a special one-time clearing account for this purpose.

To make this easy, let’s create a one-time clearing account:

Now, let’s create a dummy bank account to “create our checks.” (We don’t want to create checks in our regular bank accounts because that will mess up our reconciliation.)

To do this, we go to bank account posting groups:

We set up a new one that points to the new account 22300-1 previously set up.

By the way, I like using accounts as part of my codes on many posting groups. It makes it easier to keep things straight if I only use the groups to specify accounts.

Now that we have our posting group, we can set up the bank account. Let’s name it with a “Z” so it will always fall to the bottom of our list (never to be used again).

We only care about No., Name, Last Check No., and Bank Acc. Posting Group. We’re using the Bank Acc. Posting Group Set Up in our previous setup.

One last point: I’m assuming that all your vendors are setup with proper 1099 information. If you’re not sure, confirm and complete if necessary before continuing.

With that, setup is done.

Step 2 – Create the Purchase Journal/Purchase Invoice

Next, we create the invoice that represents the amount we want to have on our 1099s. Here, I used RapidStart, which allows me to enter 1099 codes and amounts for purchase journals. For the purpose of this example, I’m going to enter the 1099 . (Note we can’t do this from the Purchase Journal page.)

We can enter a 1099 code on Purchase Invoice, so let’s do that:

Now, we only care about a few fields beyond the normal invoice stuff:

  1. Invoice Number. We put 1099 in front of the invoice number. This makes it easier to find in our check run.
  2. Due Date. We want to choose a due date that’s before our conversion date. This way we can pay only the dummy 1099 invoices.
  3. G/L account for the line. This should be the clearing account we set up in the above step.
  4. IRS 1099 Code. The point of this exercise.

Once we’re done, we can post our records.

If we look at the register for this posting, we’ll see that the system debited our “clearing” account and credited accounts payable:

Step 3 – Cut the Checks

It’s important we set the due date correctly so when we select payments in our payment journal, we find only the records we care about.

When we reach “suggest vendor payments,” we use our dummy bank account for the check run:

Now here’s the payment we want to make:

And we can see it’s going against our dummy bank account.

Once we print the checks and post them, we can look at the entries for our check:

We see that our accounts net to zero.

Now, we can print our 1099s and close our invoice with no effect on our ledger.

Any questions? If so, feel free to comment below.

 

Dynamics NAV

See All Dimensions for NAV General Ledger Entries

Dimensions are a wonderful feature of NAV. However, when doing General Ledger analysis, sometimes clients just want to dump everything into Excel and “play” with it—especially if they use Analysis Views.

And to do this, clients want to see all dimensions, at the detail level, for each transaction.

But here’s the challenge: You can only view or filter two dimensions directly on the General Ledger entries page.

Fortunately, there’s a way around this restriction.

How to See All Dimensions for NAV G/L Entries

Let’s start by reviewing the problem: We want to see all transactions for a specified dimension, but when we look at General Ledger Entries, we can only see and filter by the two shortcut dimensions.

We start with the basic General Ledger Entries page in Cronus, USA.:

Out of the box, no dimensions are shown at all.

If we choose Columns, we see the two global dimensions, in this case Department Code and Project Code:

If we add Department Code to the columns and then filter to show only records with department codes, we see that information:

And that may be all we need. But Cronus uses several more dimensions with the General Ledger, as we see in General Ledger Setup:

On a transaction by transaction basis, we can see the additional information by clicking dimensions in the ribbon:

But what if we want to see all G/L transactions for Sales Campaign Winter?

To do this, we need to build an analysis view.

How to Create a View for Your Company

You can build a view for your company to get all transactions with all dimensions. This is especially useful for dumping data with a direct query into Excel or through an SSRS report.

Below is an example SQL for Cronus, USA.

To try this out yourself, change the names to reflect your company and dimensions.

And here’s what the data would look like (without all the columns, obviously):

A few key notes:

1. I’ve joined to the dimension set ID for each dimension I’ve defined.

Theoretically, I could have taken the values for department and project directly from the G/L Entry Table. But I chose to do it this way so I can join to the dimension value table and get the name, not just the code.

This is particularly useful when working with a dimension (such as area, for example) where codes aren’t obvious.

2. I’ve used “coalesce” around all the dimension values and names.

If I don’t do this, SQL server will return “NULL” into the query, which then shows up in Excel or SSRS. End users don’t understand null. It’s better to clean it up at the query/view level.

3. To make this work, you need to replace the dimension names in the appropriate join.

Or, as part of our free Dynamics NAV reports pack, you can used our stored procedure—which will automatically generate the information.

4. I’ve included EVERY column from G/L Entry.

You probably won’t want to give your users every column. Too many choices creates confusion.

A Generic Dimensions View for All Companies

As I’ve written before, I don’t like developing queries or views that work for just one company. So, below is some generic code you can use.

(For more on this, check out my post Using SSRS with Dynamics NAV—Creating Views to Write SSRS Queries.)

As I don’t know what the dimensions will be called (and indeed, they can be called different things for different companies), I’ve included the dimension name as a value in a column rather than the name of a column:

And here’s what it will look like:

I’ve created a sample SSRS report that includes basic transaction data with all the dimensions. I will include it in my set of Free SSRS reports for Dynamics NAV.

Let me know if you’d like a copy or if you have questions about the code.

 

Dynamics NAV

Free SSRS Reports for Dynamics NAV

Since most of my clients have switched to Dynamics NAV (many of them from Lawson), I’ve been busy writing a bunch of SSRS reports that facilitate specific finance and accounting functions my clients require.

Here are the reports I’ve completed thus far:

I have more reports in the works. If you’d like my complete package of free SSRS reports for Dynamics NAV when it’s ready, let me know.

 

 

Dynamics NAV

How to Deal With NSF Checks in NAV (Without Using Reverse Transaction)

I was at the NAVUG user group in NYC last week. There, I learned that NAV2017 now allows users to cancel posted sales invoices and sales credit memos. While I’m sure some users will welcome these changes (and I’ve yet to see exactly how they’ll work), my first impression is that this isn’t such a great idea.

Part of my reasoning is that, honestly, I believe that people who “pay for their mistakes” are more likely to stop making them.

But I also hesitate because I don’t like reversing transactions in NAV more generally.

Using Reverse Transaction in Dynamics NAV

NAV has at least three “reverse transaction” options: General Ledger Entries, Customer Ledger Entries, and Vendor Ledger Entries.

When a customer bounces a check, you could usually reverse that in the customer ledger.

To better understand my hesitation about reversing transactions, let’s look at a payment reversal as an example.

Let’s start with payment 2596 (below) in the Dynamics NAV Cronus USA database.

Click Applied Entries to see where this payment was applied:

And here’s the invoice:

So, we see this payment was applied against one invoice.

Let’s see what happens when we choose Reverse Transaction:

This is what shows up:

As you can see, there are three customer payments on the same date with the same information, which is confusing.

But the key issue is that your only option to continue is by clicking “reverse.” You can’t control the posting date—it automatically goes back to the original date of the payment. This creates several issues:

  • If you have proper date controls in place, you can’t post the entry at all. This means reverse transaction only works when the period is open. (See my post Month End Closing in Dynamics NAV for more on this.)
  • Even if you’re in the same period, report numbers will change in unexpected ways.
  • At best, you’ll need to have two different solutions.

My Alternate Solution for Dealing With a Bounced Check

Here’s my suggested method:

  • Choose the appropriate payment and click “Unapply Entries”:

When you do, this page appears:

Strangely, on this page, you can control the posting date. (Even though from an accounting standpoint, Unapply Entries doesn’t effect any ledger balance, while Reverse Transaction does.)

Click “Unapply” at the top of the page.

Now, the payments (and matching invoices) show as unapplied:

After we’ve unapplied the entries (so the next good check from the customer can be applied correctly), we can “refund” through a cash receipt journal and “apply” this refund to the now-open payment.

We create the refund here:

Then, we click Apply Entries and get to this screen:

We can use this process at any time—regardless of when the customer check bounced.

A side note: I somewhat accept that reversing general journals isn’t the worst thing if you’re doing it to correct an error on your part. However, I’ve had many occasions where people ask, “Why did this report change?” when someone reverses an entry just a little too fast.

What’s your process for dealing with NSF checks?

 

Dynamics NAV

Lawson and Dynamics NAV

Since 1995, I’ve worked extensively with Lawson software. Until this month, that is, as my last Lawson client is moving into Dynamics NAV.

I’ve learned a lot about NAV over the last few years. And while I do miss Lawson at times, Lawson’s time has passed for my core clients (who aren’t in health care or government).

Why My Clients Have Moved From Lawson

Before I explain, note that my observations about Lawson are largely based on my for-profit clients in the greater NY area. Generally, these clients fall into two large categories:

  1. Companies that grew into the multi-billion dollar category

As these clients grew, they saw no real added functionality in Lawson and have therefore moved to SAP and Oracle mostly. This move was often driven by the desire to consolidate on a single ERP—and they didn’t feel Lawson had the functionality they wanted or a strong interest in meeting their needs.

  1. Mid-market companies for whom Lawson got too expensive

Many of these companies bought Lawson in the mid-90s/early 2000s to run on Unix and/or AS400 systems. At the time, Windows/SQL server solutions didn’t have the power to run $100 million dollar companies (or if they did, they needed an awful lot of support).

But over the years, Lawson’s maintenance grew and grew—to the point where I’ve seen companies cut maintenance costs by over 80-percent by moving to different packages. The crazy thing is that Lawson will cut deals with new customers or re-licenses, but they won’t do the same for clients on maintenance. Then again, even if existing clients could get a deal, the costs in the Lawson ecosystem would probably still be higher than in the Dynamics world.

As a Dynamics NAV Consultant, I can say that Dynamics NAV is not a good fit for companies in the first category. (I do know some former Lawson consultants have moved clients to Dynamics AX, but I don’t know enough about AX to comment.)

I will say that, in recent years, Lawson made some changes to make it a little more user friendly. (My experience with Lawson runs from version 6.0 to version 9.0.1—or thereabouts.)

Where Dynamics NAV Beats Lawson

  1. Easier data entry

I base this statement on the experience of a client who recently moved from Lawson to NAV. Fundamentally, Lawson is based on a batch process developed over 30 years ago. And consequently, many processes require multiple screens and steps. If you’ve been using Lawson for a long time, you may not even notice some of these steps (e.g. Bl121, Bl122 and Bl123 to post an invoice).

But when these steps go away—and all you have to do is push “post”—life is good and the time savings are real.

  1. More flexible account string and better online analysis

Lawson has been promising a more flexible account string since 1998. (I recall hearing it for the first time at a sales conference presentation.) Other than Lawson’s stillborn strategic ledger, nothing much happened. Of course, you can get creative with attributes, activities and account categories in Lawson, but it’s challenging. And you have to build lots of custom reports to get the data out.

NAV Dimensions, in contrast, is much more flexible. And NAV has many more built-in tools for analysis that really work.

  1. Jet Reports for financial report writing

Lawson’s RW100 Report Writer seemed like a great tool once. But that was over 20 years ago. While there are many levels to Jet Reports, even with the basics you can build your financial report in Excel. Yay!

(For my thoughts on the best uses of Jet Reports vs. SSRS, see this post.)

  1. Manufacturing and warehouse functionality

Most of my Lawson clients only needed financials and procurement. And Lawson was fine for that. But even though these clients weren’t full manufacturers, they could have sometimes used more advanced warehouse or manufacturing pieces. These pieces are included with NAV but were never part of the Lawson package.

  1. Simpler administration and installation

Over the years, folks in the Lawson world came to expect ever more complex installs. Between WebSphere and Tivoli, a basic install took days (and sometimes even a week) per server. And a lot of this was for stuff that really only benefitted big users.

A Dynamics NAV install is basically a day’s work. Not coincidentally, the skills you need to keep the back end going are also easier to train or outsource.

  1. Built in workflow

Standard Lawson had some basic ways to control workflow. With security, for example, you could allow some folks to enter data and others to post batches. But that’s not real workflow.

NAV comes with workflow. And as more companies try to get away from paper, NAV provides a solid starting point out of the box.

  1. A larger third-party ecosystem, at less cost

No ERP software gives you everything you want. But because NAV is so widely installed, it has a large variety of third-party ISVs to fill the gap. In addition, where $25K-$50K was the typical starting point in the Lawson world, many NAV solutions are a fraction of that.

Where Lawson Beats Dynamics NAV

But all is not sweetness and light. There will be days when you miss Lawson. This isn’t a matter of having to “just get used to something new.” Rather, some things in NAV don’t work as well:

  1. Lawson Add Ins for MS Office

Lawson Add Ins, and its backing technologies, are the best Excel integration tools I’ve seen. And I say that based on my experience with Lawson, Oracle EBS, Dynamics GP and Dynamics NAV. Yes, third-party options are available. But Add-Ins is just a wonderful tool.

  1. The ability to maintain multiple balance sheets in one company

NAV is based on a separate data sets for each company—which is fine if you’re a mid-sized company that doesn’t have many entities. But if you’re used to paying bills for multiple entities out of one company, you’ll need to seriously look at your process or consider third-party products that build on NAV capabilities.

Are you on Lawson and considering your next steps? Or have you made a decision to move and are in the process?

 

Dynamics NAV

AR Revaluation Report – Dynamics NAV

In my last series of posts, I wrote about NAV currency setup, entering foreign currency sales invoices and how adjust currency amounts can be run at period end to create gain loss entries.

In that last post, I promised to show you a sample report that would allow you to see the gain loss before you actually post it.

And here it is.

Aged Accounts Receivable in NAV

The Aged Accounts Receivable report in NAV has the option to print results in the customer’s currency:

If we run this report for Beef House (49525252), and select Print Detail, we see the following:

We can see the transaction in euros as well as a report total in USD. Which is all good.

However, it’s important to note: The USD total reflects the current rates as of the cut off date for the report whether or not we’ve actually posted gains and losses to the ledger.

How do I know this? Let’s start by looking at the rates. (I had to add a rate to the system to create the example.) Checking the exchange rates, we can see that the system should choose the 1.4 rate because that’s the latest rate before the cut off date.

(If you do the math, you’ll see that indeed 30315.02 /21,653.58 = 1.4.)

However, when we look at the customer ledger entries, we something else.

For example, let’s take the first invoice (103005) and look it up in the Customer Ledger. If we multiply 1,245.92 x 1.4 we’d expect to see 1744.33.

However, if we look at the local currency amount (marked misleadingly with a $ sign) we see that it’s 1541.20.

Which, if you do the math, is a rate of 1.237, which is the rate as of 1/1/2016.

If we choose Detail Customer Ledger Entries, we would find there’s no revaluation for the transaction. (For more on this, see our post Understanding NAV Currency: Part 3 Month End Revaluation.)

Red Three Open AR Unrealized Gain/Loss Report

As part of my forthcoming set of free SSRS reports for NAV, I’ve created a report that shows both the current value (which should tie to the ledger), the prospective revaluation, and the prospective gain and loss.

For example, here’s a small screenshot for Beef House:

You can see the current calculation (1.237) and the new at 1.4 (which ties to the NAV report).

I’ve built this report using my shared database view.

I’m in the process of putting all my code together, but I’ve also included it below.

Let me know if you’d like to have the stored procedures and/or the SSRS report when they’re ready. I’m hoping to have them put together by mid-December.

 

Dynamics NAV

Using Totaling Accounts with SQL in Dynamics NAV

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.

 

Get tips and insights delivered to your inbox

Start a conversation with us

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

Request a Consult