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

Field No. Field Name Data Type Length
1 Date Date
2 Date Name Text 30
3 Year Integer
4 Week Integer
5 Month Integer
6 Month Name Text 20
7 Day of Week Integer
8 Day Name Text 20
9 Day of Month Integer
10 Closing Date Boolean
11 SQL Month Name Text 20
12 SQL Day Name Text 20
13 Quarter Integer
14 Year Month Integer
15 Year Month Name Text 20
16 Month Year  Name Text 20
17 Quarter Name Text 20
18 VAT Period Integer
19 VAT Period Name Text 20
20 Sorting Date Integer
21 HRMS Integer Start Integer
22 HRMS Integer End Integer
23 Day of Year Integer
24 Day of Half Year Integer
25 Day of Quarter Integer
26 Day of Accounting Integer
27 Half Years Integer
28 Half Year of Year Integer
29 Is Holiday Boolean
30 Is Working Day Boolean
31 Month of Half Year Integer
32 Month of Quarter Integer
33 Month of Year Integer
34 Quarters of Half Year Integer
35 Quarters of Year Integer
36 Week of Year Integer
37 Is Week Day Boolean
41 Half Year Name Text 20
42 Week Name Text 20
102 Fiscal Day Date
103 Fiscal Year Integer
104 Fiscal Week Integer
105 Fiscal Month Integer
107 Fiscal Day of Week Integer
109 Fiscal Day of Month Integer
113 Fiscal Quarter Integer
123 Fiscal Day of Year Integer
124 Fiscal Day of Half Year Integer
125 Fiscal Day of Quarter Integer
127 Fiscal Half Years Integer
128 Fiscal Half Year of Year Integer
131 Fiscal Month of Half Year Integer
132 Fiscal Month of Quarter Integer
133 Fiscal Month of Year Integer
134 Fiscal Quarters of Half Year Integer
135 Fiscal Quarters of Year Integer
136 Fiscal Week of Half Year Integer
137 Fiscal Week of Month Integer
138 Fiscal Week of Quarter Integer
139 Fiscal Week of Year Integer
140 Fiscal Quarter Name Text 20
141 Fiscal Half Year Name Text 20
142 Fiscal Week Name Text 20
143 Fiscal Month Name Text 20

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.