Accessing your non english data from the MS SQL server

If you are like me, located in a non-English speaking country and would like users to be able to use other tools then the Dynamics NAV clients to access the company data you will find that all the meta data in the database is in English.  This means that you will have to translate the fields and sometimes the data to your language.

The problems are:

• Field captions are unavailable in the MS SQL database
• Option values are shown as number
• Boolean is shown as number
• Global dimension do not have the correct caption
• Time is shown as DateTime
• Not easy to see the difference between normal date and closing date

The solution that I am using is to create a separate database on the same database server and create localized views in that database.  What you get with the solution is:

• A date table that can be used to show all properties of a given date
• A import of the option value captions
• A selection of tables to make accessible
• A batch job to create the localized SQL view for each table

Here is a list of the fields in the date table

Lets take the G/L Entry table as an example. In the MS SQL the field names are in English and the data not readable for the normal user.

SELECT [Entry No_],[G_L Account No_],[Posting Date],[Document Type]
      ,[Document No_],[Description],[Bal_ Account No_],[Amount]
      ,[Global Dimension 1 Code],[Global Dimension 2 Code],[User ID] 
... 
      ,[FA Entry Type],[FA Entry No_] 
FROM [Dynamics NAV Demo Database (IS 2009 R2)].[dbo].[CRONUS Ísland hf_$G_L Entry]

and for example the [Document Type] will show as numbers.  Here is an example of the output of my tool to create a localized view for this table.

IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'dbo.ISL$CRONUS Ísland hf_$Fjárhagsfærsla'))
DROP VIEW [dbo].[ISL$CRONUS Ísland hf_$Fjárhagsfærsla]
GO
CREATE VIEW [dbo].[ISL$CRONUS Ísland hf_$Fjárhagsfærsla]
AS
SELECT
[Entry No_] As [Færslunr_]
,[G_L Account No_] As [Fjárhagsreikn_nr_]
,[Posting Date] As [Bókunardags_]
,CASE [Document Type]
WHEN 0 THEN ' '
WHEN 1 THEN 'Greiðsla'
WHEN 2 THEN 'Reikningur'
WHEN 3 THEN 'Kreditreikningur'
WHEN 4 THEN 'Vaxtareikningur'
WHEN 5 THEN 'Innheimtubréf'
WHEN 6 THEN 'Endurgreiðsla'
END As [Tegund fylgiskjals]
,[Document No_] As [Númer fylgiskjals]
,[Description] As [Lýsing]
,[Bal_ Account No_] As [Mótreikningur nr_]
,[Amount] As [Upphæð]
,[Global Dimension 1 Code] As [Deild Kóti]
,[Global Dimension 2 Code] As [Verkefni Kóti]
,[User ID] As [Kenni notanda]
,CASE [System-Created Entry]
WHEN 1 THEN 'Já'
WHEN 0 THEN 'Nei'
END As [Kerfisfærsla]
...
,CASE [FA Entry Type]
WHEN 0 THEN ' '
WHEN 1 THEN 'Eignir'
WHEN 2 THEN 'Viðhald'
END As [Eignafærslutegund]
,[FA Entry No_] As [Eignafærslunr_]
FROM [CRONUS Ísland hf_$G_L Entry]
GO

Executing this will give me a view in my database that I can use to fetch localized data from the G/L Entry table with all the above problems solved.

By using the additional date table as dimension in OLAP or as join in a SQL query I can easily find all aspects of the “Posting Date” in the G/L Entry table and group entries accordingly.