The "Period Name" field is not present in the "Date" table when using a Dynamics NAV 2013-2018 SQL Server data source
Jet Reports which have been designed with the Dynamics NAV 2013-2018 Web Services data source and which use the Period Name field in the Date table will need to be modified when switching to the Dynamics NAV 2013-2018 SQL Server data source.
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 Dynamics NAV 2013-2018 SQL data source for Jet Reports provides substantial performance increases compared to a Dynamics NAV 2013-2018 Web Services data source.
Connections that directly use the Dynamics NAV system (such as the Web Services data source for Dynamics NAV 2013-2018 and the Dynamics NAV C/Front data source for Dynamics NAV 2009 R2 and earlier) have access to Dynamics NAV's virtual "Date" and "Integer" tables. Virtual tables are created by Dynamics NAV in memory, rather than being stored in Dynamics 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 Dynamics NAV.
The Jet Reports Dynamics NAV 2013-2018 SQL Server data source does not directly utilize Dynamics NAV but, rather, retrieves data directly from the SQL database. For convenience and backward compatibility, insightsoftware has also created a separate database with an Integer table as well as a Date table that includes all fields found in Dynamics NAV's virtual date table (with the exception of the "Period Name" field). Instead of querying the Dynamics 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.