Jet Reports – What NAV Server Am I Using?

A client recently asked me: “When I run a report, how can I know which server/company I’m using?”

It’s a good question — and one that others may have.

Below, I’ll walk you through the answer.

And because pretty colors impress most folks, I’ve even made it match the colors that show up in NAV.

While you can specify the server you’re using in any given report, you generally don’t want or need to do this.  

However, even though the server isn’t in the formula itself, Excel still sees it.

As in this example:

Compare the formula I’ve entered above to what actually shows up in the cell.

Since the server is actually there, you can put it into your report. 

Here’s a little formula that will return the server name itself:

=MID(B$3,2,FIND(“,”,B$3)-3)

A quick explanation: the FIND function looks for the first comma in the string. It separates the server name from the next field, which indicates the company (in this case, SPECIAL). We then subtract 3 to get the string length we need to use.

Of course, you may want the name of the company as well. 

To make this formula more comprehensible, I’m going to break it into pieces:

=FIND(“,”,B$3)

=MID(B$3,2,B2-3)

So far, we’re doing the same thing we did above, just in two steps.

I search again, but I start after the first comma:

=FIND(“,”,B$3,B2+1)

=MID(B$3,B2+2,D2-B2-3)

Now, knowing the positions of both commas, I can pull out my company name.

What if I want the actual company name? And what if I like the color that NAV uses?

I can look that information up from the company information table:

=NL(“First”,”Company Information”,{“Name”,”System Indicator Style”})

And pull the field data out:

=NF(B$2,”Name”)

=NF(B$2,”System Indicator Style”)

With that system indicator style, I can create custom formatting.

To make my life easier, I’ve created a little table that maps NAV indicators to the colors they represent:

And I added a vlookupto my spreadsheet to find the color based upon this company’s system indicator, in this case Accent7:

And now, I can create formatting rules based upon the value in field E2.  

Note that I’ll need one formatting rule for every color I want to use.

Here’s the one for yellow:

And here’s the result:

You can do a lot with conditional formatting. This is just one simple example. 

If you’d like the spreadsheet with these examples, please let me know.