Multi-language support for Crystal reports in SB1

[fulvia_monti]

Hi experts,

how can I develop a multilanguage report for Sap Business One in crystal reports that really uses the multilanguage support?

Example: an invoice CR report prints the payment terms description and in database is inserted the description in many languages, how can I print the translation in the BP language?

The translation is in OMLT, MLT1 tables, but I can not link it to the report Field.

Any suggestions?

Thank you,

regards

Fulvia

[KennedyT21 · Accepted solution]

Hi Fulvia Monti ...

Using the SP you can check the below sample

USE [SBO_Databasename]

GO

/****** Object:  StoredProcedure [dbo].[xxx_translation]    Script Date: 01/28/2013 10:15:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ===================================================================

-- Author      : Willy Nachtigall

-- Create date : 23.07.2012

-- Description : Translate Article Description

-- ===================================================================

ALTER PROCEDURE [dbo].[xxx_translation]

    @DocKey@ INT

AS

select ItemCode, Dscription, ISNULL(

    (

    select MLT1.Trans

    from OMLT INNER JOIN MLT1 ON OMLT.TranEntry = MLT1.TranEntry INNER JOIN OPOR ON MLT1.LangCode = OPOR.LangCode

    where OMLT.TableName = 'OITM' AND OMLT.FieldAlias = 'ItemName' AND OPOR.DocEntry = T0.DocEntry AND OMLT.PK = T0.ItemCode

    ), T0.Dscription) AS 'Translated'

from POR1 T0

where T0.DocEntry = <123....>

------------------------------

This SP is then added as a db field and used in the layout.

1. Crystal Report multi-language limitation As Crystal Reports

integaration with SAP Business One does not support multi-language function now,

the Crystal Reports contents developed with pure query support only one

lanuguage for each country localization. SAP Business One delivers Crystal

Reports contents developed with the pure query with only one major lanugage for

each country localization until the multi-language function is available

2.

Text language in Crystal Reports parts is bound to language of
localization

Description: Text language in Crystal Report parts (reports,

layouts & electronic EU sales report for UK/DE)are bound to language of

localization(not translatable and does not support multi-language). Business

Impact: CR content layouts is only available in some languages and cannot be

widely used across all localizations. Electronic EU sales report for UK/DE which

leverage CR integration platform also does not change language when UI language

is changed in B1. Workaround: Generic CR content layouts are provided in English

or specific local language and customer/partner need to translate to local

language.

Regards

Kennedy

[fulvia_monti · Accepted solution]

Tank you for your reply, it is very helpful.

I developed a workaround that is a subreport that uses a query similar to your stored procedure, but this solution  is difficult in report design for alignment and a sub report is needed for each translated field, but it also works.

Regards

Fulvia,

[Former Member · Accepted solution]

Hi @all,

I am facing the same problem and did not get it fixed so far. I tried to implement the sample code via sql studio in a new procedure but it does not really work. I am not able to save the ne procedure. When I parese the code, theres no error, but when i execute it, in order to save it, i get several errors.

Can anybody help me? I will do exactly the same as Fulvia Monti, tranlated article descriptions.

@Fluvia Monti: What did you use in your Subreport?

Thx in advance.

Greets
Stephan

[fulvia_monti · Accepted solution]

Dear Stephan,

the solution I finally applied is the following:

1.create a store procedure like the one at the end of this reply, that is fine fore every document type

2.add the store procedure as a table in the report, and join it with the document rows on DocEntry and line num

3.Add field ItemDescrT in report layout, overlapped at itemdescription and invisible in case it is empty.

I hope it is clear, I found it better to mantain than the subreport.

CREATE

PROCEDURE [dbo].[TmSp_DocDETTtrans]

@DocKey@ INT,

@ObjectId@ INT

AS

-----------------------------------------

DECLARE

@object NVARCHAR(4000)

IF

(@ObjectId@=13) SET @object = 'INV'

ELSE

IF (@ObjectId@=14) SET @object = 'RIN'

ELSE

IF (@ObjectId@=15) SET @object = 'DLN'

ELSE

IF (@ObjectId@=16) SET @object = 'RDN'

ELSE

IF (@ObjectId@=17) SET @object = 'RDR'

ELSE

IF (@ObjectId@=18) SET @object = 'PCH'

ELSE

IF (@ObjectId@=19) SET @object = 'RPC'

ELSE

IF (@ObjectId@=20) SET @object = 'PDN'

ELSE

IF (@ObjectId@=21) SET @object = 'RPD'

ELSE

IF (@ObjectId@=22) SET @object = 'POR'

ELSE

IF (@ObjectId@=23) SET @object = 'QUT'

ELSE

IF (@ObjectId@=24) SET @object = 'RCT'

ELSE

IF (@ObjectId@=25) SET @object = 'DPS'

ELSE

IF (@ObjectId@=46) SET @object = 'VPM'

ELSE

IF (@ObjectId@=58) SET @object = 'INM'

ELSE

IF (@ObjectId@=59) SET @object = 'IGN'

ELSE

IF (@ObjectId@=60) SET @object = 'IGE'

ELSE

IF (@ObjectId@=67) SET @object = 'WTR'

ELSE

IF (@ObjectId@=68) SET @object = 'WKO'

ELSE

IF (@ObjectId@=69) SET @object = 'IPF'

ELSE

IF (@ObjectId@=112) SET @object = 'DRF'

ELSE

IF (@ObjectId@=132) SET @object = 'CIN'

ELSE

IF (@ObjectId@=140) SET @object = 'PDF'

ELSE

IF (@ObjectId@=162) SET @object = 'MRV'

ELSE

IF (@ObjectId@=163) SET @object = 'CPI'

ELSE

IF (@ObjectId@=164) SET @object = 'CPV'

ELSE

IF (@ObjectId@=165) SET @object = 'CSI'

ELSE

IF (@ObjectId@=166) SET @object = 'CSV'

ELSE

IF (@ObjectId@=191) SET @object = 'SCL'

ELSE

IF (@ObjectId@=198) SET @object = 'FCT'

ELSE

IF (@ObjectId@=199) SET @object = 'MSN'

ELSE

IF (@ObjectId@=202) SET @object = 'WOR'

ELSE

IF (@ObjectId@=203) SET @object = 'DPI'

ELSE

IF (@ObjectId@=204) SET @object = 'DPO'

ELSE

IF (@ObjectId@=321) SET @object = 'ITR'

ELSE

IF (@ObjectId@=140000009) SET @object = 'OEI'

ELSE

IF (@ObjectId@=140000010) SET @object = 'IEI'

ELSE

IF (@ObjectId@=540000006) SET @object = 'PQT'

SELECT

* INTO #ODOC FROM OINV WHERE 1=2

INSERT

INTO #ODOC EXEC ('SELECT * FROM O'+@object+' WHERE DocEntry = '+@DocKey@)

SELECT

* INTO #DOC1 FROM INV1 WHERE 1=2

INSERT

INTO #DOC1 EXEC ('SELECT * FROM '+@object+'1 WHERE DocEntry = '+@DocKey@)

CREATE

TABLE #TransDETTTab

(

DocEntry INT,

LineNum INT,

ItemDescrT NVARCHAR(4000)

)

INSERT

INTO #TransDETTTab(DocEntry, LineNum, ItemDescrT)

--, TextCodeT)

select

A.DocEntry, A.LineNum, A.trans as ItemDescrT

--, C.Trans as TextCode

from

(select #DOC1.DocEntry, #DOC1.LineNum, MLT1.Trans, mlt1.LangCode from OMLT, MLT1 , #ODOC, #DOC1  where

OMLT.TranEntry = MLT1.TranEntry and

#ODOC.LangCode = MLT1.LangCode and

#DOC1.ItemCode = omlt.pk and

TableName = 'OITM' and FieldAlias = 'ItemName') A

---- Output Result

SELECT

* FROM #TransDETTTab ORDER BY DocEntry

DROP

TABLE #TransDETTTab

DROP

TABLE #ODOC

DROP

TABLE #DOC1

[Former Member]

Hi Fulvia,

A multilingual report is developed in CR. There is no link between a multi-language report to B1 table. You need to add translation to all fields that in need.

Thanks,

Gordon

[kothandaraman_nagarajan]

Hi,

Please check with below link and page 67 onwards:

http://www.michellgroup.com/images/pdf/bwork_with_crint882.pdf

Hope helpful.

Thanks & Regards,

Nagarajan