Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Feedback

The "Period Name" field is not present in the "Date" table when using a NAV 2013 SQL Server connector


Jet Essentials reports which rely on the “Period Name” field in the “Date” table will need to be modified when upgrading to the NAV 2013 SQL Server connector.  It will be necessary to modify these reports to use the "Period No." field instead of the “Period Name” field. The "Period No." field contains the same information as "Period Name", just in a numeric format.

This is only an issue when the period type is Date or Month.  The following Excel formulas can be used to convert these values into period names:

Where cell B2 = 1

Period Type Formula Output
Date =TEXT(B2+1,"dddd") Monday
Month =TEXT(DATE(2014,B2,1),"mmmm") January

Background

The NAV 2013 SQL connector for Jet Essentials provides substantial performance increases compared to a NAV Web Services data source connector.

Connections that directly use the NAV system (such as the Web Services connector for NAV 2013 and the NAV C/Front connector for NAV 2009 R2 and earlier) have access to NAV's virtual "Date" and "Integer" tables.  Virtual tables are created by NAV in memory, rather than being stored in NAV's database.  Included in the "Date" table is the "Period Name" field that is populated with language-specific values based on the language setting within NAV.

Jet Essentials' NAV 2013 SQL Server connector does not directly utilize NAV but, rather, retrieves data directly from the SQL database.  For convenience and backward compatibility, Jet Reports has also created a separate database with an "Integer" table as well as a "Date" table that includes all fields found in NAV's virtual date table - with the exception of the "Period Name" field. Instead of querying the NAV SQL database for these 2 tables, Jet queries its own database.  Since this database is the same for all users, it it not possible to include all, possible, language-specific "Period Name" field values.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments