917.848.7284Data for Finance and Accounting

Top

Jet Reports – What NAV Server Am I Using?

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.

Finding the Server

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:

Jet Reports – What NAV Server Am I Using - Image 1

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.

Jet Reports – What NAV Server Am I Using - Image 2

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:

Step 1: Find the first comma

=FIND(“,”,B$3)

Jet Reports – What NAV Server Am I Using - Image 3

Step 2: Use that value to get the server name

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

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

Jet Reports – What NAV Server Am I Using - Image 4

Step 3: Find the next comma

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

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

Jet Reports – What NAV Server Am I Using - Image 5

Step 4: Use both comma values to get the company string

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

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

Jet Reports – What NAV Server Am I Using - Image 6

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”})

Jet Reports – What NAV Server Am I Using - Image 7

And pull the field data out:

=NF(B$2,”Name”)

Jet Reports – What NAV Server Am I Using - Image 8

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

Jet Reports – What NAV Server Am I Using - Image 9

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:

Jet Reports – What NAV Server Am I Using - Image 10

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

Jet Reports – What NAV Server Am I Using - Image 11

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:

Jet Reports – What NAV Server Am I Using - Image 12

And here’s the result:

Jet Reports – What NAV Server Am I Using - Image 13

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.

No Comments

Post a Comment