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