Multilanguage in SSRS

Is there a way to display/export eng开发者_如何转开发lish SSRS report in some other languages?

No, unfortunately, there's no easy way to do this :-( I've been trying to get this up and running myself, but in the end what I did was basically pass all the labels I want to have displayed on the report from the calling app (an ASP.NET app, in my case).

Another approach might be to store the text fragments in a SQL Server table, and add a datasource to your report which retrieves those text labels, and then bind them to the appropriate controls. I tried something like that but haven't been able to make it work for myself.

It's a pain that ASP.NET is so nicely internationalized with resources, but SSRS is still quite a messy affair when trying to make it multi-language aware :-(

I managed to get multilanguage support, via .NET Resource files, by applying an interesting hack. There is an unused Property for every single Report Control, called ValueLocId. Using this property, you can specify the resource name to use for each control. The idea here is that you will be looping through your report definition, looking for controls that have the ValueLocID property set. If the property is set, replace the Text of that control with the Resource Text specified in the ValueLocID. So basically, the idea is this:

1. Load the RDLC file in memory, as an XML file
2. Traverse the XML file using XPath, looking for ValueLocID properties
3. Replace the innerText of that XML node with the Resource specified in ValueLocID
4. Load the ReportViewer control using the memory copy of the RDLC file.

See the function below, which will do exactly what I mentioned above.

Private Sub LocalizeReport()

    Dim xmlDoc As XmlDocument = New XmlDocument
    Dim asm As Reflection.Assembly = Reflection.Assembly.GetExecutingAssembly()
    'create in memory, a XML file from a embedded resource
    Dim xmlStream As Stream = asm.GetManifestResourceStream(ReportViewer1.LocalReport.ReportEmbeddedResource)

    Try
        'Load the RDLC file into a XML doc
        xmlDoc.Load(xmlStream)
    Catch e As Exception
        'HANDLE YOUR ERROR HERE
    End Try

    'Create an XmlNamespaceManager to resolve the default namespace
    Dim nsmgr As XmlNamespaceManager = New XmlNamespaceManager(xmlDoc.NameTable)
    nsmgr.AddNamespace("nm", "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition")
    nsmgr.AddNamespace("rd", "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner")

    'IMPORTANT LINE BELOW
    'YOU WILL NEED TO SET THIS TO YOUR RESOURCE MANAGER, OTHERWISE NOTHING WILL WORK
    Dim rm As ResourceManager = New ResourceManager("Insurance.Subs.WinUI.Controls.Resources", asm)

    'Loop through each node in the XML file, that has the ValueLOCId property set.
    'Using this property as a workaround for localization support.  The value specified in this
    'property will determine what resource to use for translation.
    Dim node As XmlNode
    For Each node In xmlDoc.DocumentElement.SelectNodes(String.Format("//nm:{0}[@rd:LocID]", "Value"), nsmgr)  'XPath to LocID
        Dim nodeValue As String = node.InnerText
        If (String.IsNullOrEmpty(nodeValue) Or Not nodeValue.StartsWith("=")) Then
            Try
                Dim localizedValue As String = node.Attributes("rd:LocID").Value

                'Get the resource via string
                localizedValue = rm.GetString(localizedValue)
                If Not String.IsNullOrEmpty(localizedValue) Then
                    'Set the text value - via the retrieved information from resource file
                    node.InnerText = localizedValue
                End If

            Catch ex As Exception
                'handle error
            End Try
        End If
    Next

    ReportViewer1.LocalReport.ReportPath = String.Empty
    ReportViewer1.LocalReport.ReportEmbeddedResource = Nothing
    'Load the updated RDLC document into LocalReport object.
    Dim rdlcOutputStream As StringReader = New StringReader(xmlDoc.DocumentElement.OuterXml)
    Using rdlcOutputStream
        ReportViewer1.LocalReport.LoadReportDefinition(rdlcOutputStream)
    End Using

End Sub

You can expose a global parameter (User!Language) which reflects the user's lang.

Then you can use google translateapi to convert words in english to your language.

Here is an excellent article: http://mscrm4u.blogspot.com/2008/06/multi-lingual-ssrs-reports.html

You should try the following link, this seems the best way to do it.

http://support.microsoft.com/kb/920769

You will need to create an assembly with your resources and methods to get strings based on the culture. You can find the full tutorial here :

http://www.codeproject.com/Articles/294636/Localizing-SQL-Server-Reporting-Services-Reports

You can add a custom assembly which contains the resources for your strings you want to translate, and access them in your report.

Going the SQL dsTranslations dataset way ( i dont like messing about in the XML )

This allows you to make a simple interface to have your customers fill in the translations, for example, a quick Lightswitch GUI

Using this dataset i do a simple lookup to translate

=Lookup("WORDTOBETRANSLATED", Fields!Key.Value, Fields!Value.Value, "dsTranslation")

This second method is the one i personaly use, in case the key is not in the dbTranslations, it shows wat key they have to add to the db. I could probably do this second part more elegantly, feedback is verry welcome.

=Microsoft.VisualBasic.Interaction.iif(Lookup("WORDTOBETRANSLATED", Fields!Key.Value, Fields!Value.Value, "dsTranslation") = "", "WORDTOBETRANSLATED", Lookup("WORDTOBETRANSLATED", Fields!Key.Value, Fields!Value.Value, "dsTranslation"))

I agree with Igoy on this after having recently run through the steps listed in the codeproject, but would like to add that the steps to follow when adding the new CodeGroup are lacking a bit in that if you place the new CodeGroup anywhere but after the unnamed UnioncodeGroup (it's the one with the Url="$CodeGen$/*") your attempts to access your custom assembly will fail.

After a lot of digging I was able to find confirmation of this on one of the msdn pages (see the "Placement of CodeGroup Elements for Extensions" section). Their wording was that "it is recommended", but from my testing I'd say it's required, at least when testing directly on the report server: http://msdn.microsoft.com/en-us/library/ms152828.aspx

The xpath for this location in the .config files is as such (useful in wix): //PolicyLevel/CodeGroup/CodeGroup[[]@class='FirstMatchCodeGroup'[]]/CodeGroup[[]@PermissionSetName='ReportLocalization'[]]

The simple way to make a multilingual ssrs report is hard-code or using sharedataset. however, we will face rendering performance issue if we use share dataset.

Here are a efficient approach to translate labels in report with better performance (especially on Rendering performance)

Step 1: Implement a library to support getting dictionary for reports, the below example is for reference, this should be modified to return correct dictionary

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Security.Permissions;
using System.Text;
using System.Data.SqlClient;

namespace CmsReportLibrary
{
    public class DictionaryLabel
    {
        DictionaryLabel()
        {
            
        }
        
        public static string[] GetDictionary(int languageid)
        {
            System.Data.SqlClient.SqlClientPermission oPerm = new System.Data.SqlClient.SqlClientPermission(PermissionState.Unrestricted);
            oPerm.Assert();
            SqlConnection oConn = new SqlConnection();
            oConn.ConnectionString = ConfigurationManager.ConnectionStrings["appconnectionstring"].ConnectionString;

            //oConn.ConnectionString = "Data Source=(local);Initial Catalog=Northwind;User Id=<>;Password=<>";

            //oConn.Open();
            //SqlCommand oCmd = new SqlCommand();
            //oCmd.Connection = oConn;
            //oCmd.CommandText = "..................";
            // ....................

            //oConn.Close();	
            return new string[] {...............}; 
			//ex return new string[] { "Client||Klient", "Week||Woche", "Year||Jahr"}; 			

        }
    }
}

Step 2: Compile the library and copy it to Bin folder in ReportServer of Reporting Service

For example: copy the library to C:\Program Files\Microsoft SQL Server\MSRS10_50.R2\Reporting Services\ReportServer\bin

Step 3: Modify rssrvpolicy.config file in ReportServer folder (ex: C:\Program Files\Microsoft SQL Server\MSRS10_50.R2\Reporting Services\rssrvpolicy.config), find the "$CodeGen$" and add the following code to let the SSRS know the location of new library

<CodeGroup
        class="UnionCodeGroup"
        version="1"
        PermissionSetName="FullTrust"
        Name="CoDeMagSample"
        Description="CoDe Magazine Sample. ">
       <IMembershipCondition
            class="UrlMembershipCondition"
            version="1"
            Url="C:\Program Files\Microsoft SQL Server\MSRS10_50.R2\Reporting Services\ReportServer\bin\CmsReportLibrary.dll"
    />
</CodeGroup>

Step 4: Stop and Start Reporting Service In Reporting Service Configuration Manager

Step 5: Apply library into SSRS Report Create new report or modify new report, this report should have a languageid parameter Set references to the library for this report by Right click on report, select Report Properties Click on References tab Paste the references of library into "Add or remove assemblies" area:

CmsReportLibrary, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null

Step 6: Add custom code to support label translation

public Shared Dim ListLabel as String()

function GetListLabel(languageid as Integer) ListLabel= CmsReportLibrary.DictionaryLabel.GetDictionary(Parameters!LanguageId.Value) end function function Translate(input as String) as String

dim i as Integer


For i=0 to UBound(ListLabel,1)
    if Instr(ListLabel(i), input) > 0 then
        Translate = Replace(ListLabel(i), input + "||","")      
        exit function
    end if

Next

'Not found, return any string you want
Translate = "not found"
end function

Step 7: Add new report variable

Expression for the ListLabel variable

=Code.GetListLabel(Parameters!LanguageId.Value)

Step 8: Translate labels in report

Modify the label expression, use the Translate method in Custom Code to translate

For example:

=Code.Translate("Client")

=Code.Translate("Week")