Selecting Data Source and Company as menu options

Saturday, September 1, 2018 - By Jon Gardner

There are a couple of scenarios where a NAV implementation might have multiple data sources and multiple companies –

• A multinational  or conglomerate with different databases for different countries or businesses
• A major NAV implementation which may have a number of UAT databases and companies each looking at different aspects of the pre-live testing

Whilst it is possible to change the default data source and company on the Jet Ribbon Bar, the issue with this is that there is no record on the report itself of which data source and company the data comes from. To say the least this can be confusing when looking at the reports later, especially when there are multiple versions of the same report, each run against a different data source and/or company.

It may mean a little extra work “up front” but adding the two options – data source and company – to the report menu, and then carrying that information onto the report itself can make the report so much easier to use and so much clearer to read.

At Measure Metrics, unless it is the most simple of data source/company configurations we will always put the data source and company lookups at the top of our Options page in the workbook.  The data source should be selected first – and then we link the company lookup to the companies available in that data source selection. (Subsequently other option lookups too can be linked to the choice of company).

The lookups for data source and company are not straight forward however, since these are system tables rather than NAV tables – so Jet requires some special syntax for each.

The NL(Lookup) wizard for the data source looks as follows –

The syntax reads as follows –

          =NL(“Lookup”,NP(“DataSources”),”Data Source”)

Rather than a table name, the table element of the wizard is populated with a nested Jet function =NP(“DataSources”)

Once the data source is selected the NL(Lookup) for the company looks as follows –

The syntax reads as follows –

          =NL(“Lookup”,NP(“Companies”,,$C$3),”Company”)

Once again rather than using a NAV table name, the table element of the wizard is a system file and so requires the lookup table field to be populated with the nested Jet function =NP(“Companies”), but this time referring to the previously selected data source in Cell C3.

As the report is run/refreshed the first lookup is for the data source and the second lookup is for the company. Other lookups may follow.  It is important that the values returned are then carried across to the main report where they should be visible with all the other filters – using the =NP(“Eval”) function.

If you haven’t done this already, it is strongly suggested that you should make a Snippet of these two lookups. Highlight the area A1:E4 shown in the top picture, click on Snippets on the Jet Ribbon Bar and then click on “New”, giving the snippet an appropriate name.  We are happy to send you a copy of the snippet file if you prefer; please just contact us by clicking here and populating the Contact Form.