Localizing Reports

Article
05/30/2011

For people who speak other languages than English, you can localize the report. There are no localization tools in Microsoft SQL Server Reporting Services, and therefore Business Intelligence Development Studio. However, Operations Manager 2007 R2 supports multiple languages by using language packs. Supporting another language requires defining report strings for text that you want to localize, and then providing the appropriate display strings in the language pack section of the management pack. By using the Authoring console, you can create report strings, but providing the content itself requires that you edit the .xml file. In addition, you must also use some code and references from the generic report library.

Activating Language Support in a Management Pack

To prepare the report for localization, you first must create report strings in the management pack. The Authoring console has a tool to assist you with that.

To add report strings to a management pack

In the Authoring console, open the management pack that contains the report. Click the Reporting tab. Right-click the report that you want to modify, and select Properties.
Click the Report Strings tab. On the Report Strings page, click Create. The Choose a unique identifier dialog box opens. Enter an identifier for the report string. This identifier must be unique in the report and cannot be changed after it is created. You must preface the identifier with R., for example, R.ReportTitle, so that the code that you will add later functions correctly. Click OK when you are finished.
The string you just created appears in the Report Strings list. Select the string, and then click Edit. The Properties page for that string appears. In the Name box, enter the value for that string in the default language; that is, the text you want to appear when the string is used. In the Description box, you can optionally enter a description of what this string is used for. The report users do not see this text, but it can be helpful to add a description if you have many strings in your report. Click OK to close this Properties page.
Add any additional report strings that you need for this report, and set their Name properties.
Click OK to close the report properties. Save the management pack.

You now have to add some language packs to your management pack. The Authoring console has a feature that lets you create new language packs and view what display strings and knowledge articles are used in existing language packs. However, you cannot create new display strings or edit existing ones with the Authoring console. For creating or editing display strings, you must edit the .xml file.

To add language packs to the management pack

In the Authoring console, click the Language Packs tab. The left pane shows a language pack for United States English (ENU) and for your default language, if it is not ENU. A check mark indicates the default language pack.
In the left pane, right-click and select New Language Pack. The New Language Pack wizard opens, containing just one control, the Language box. Select the language for which you want to add a language pack. Click Finish to close the wizard.
The new language pack appears in the list in the left pane. However, it does not contain any display strings or knowledge articles. Save the management pack, and then close the Authoring console.

The report string has been created and has a value, and the empty language packs have been created. To provide values for those new language packs, you must edit the XML code for the management pack. The last section of the management pack .xml file is the LanguagePacks section, which is where the display strings for different languages are located. There is a LanguagePack element for each language pack shown in the Authoring console. By default, there is just one LanguagePack section, for your default language. Within the LanguagePack element is a DisplayStrings element, which contains several DisplayString elements. Each DisplayString element represents one item of displayed text in the management pack. The ElementID attribute of the DisplayString element uniquely identifies the element and is used to identify that display string elsewhere in the management pack. Each DisplayString element also has two subelements: Name and Description, just as the ReportStrings elements you just created do. You must add DisplayString elements for each ReportString element you created, add the code from the generic report library to process the display strings, and some additional code to display the strings in the appropriate locations in your report.

To edit the XML code to include display strings

Locate the .xml file for the management pack, and open it in the XML Editor of your choice.
Search for the term “ReportStrings” in this file. You will find a section of the file where the ReportStrings element is declared, which looks like the following code.
```
<ReportStrings>
   <ReportString ID="R.ReportTitle" />
</ReportStrings>
```
The ReportString elements that you created in the previous procedure are found here. Notice the ReportString ID element and attribute with the value of R.ReportTitle in this example.
In the LanguagePacks section of the file, locate the corresponding DisplayString element, where the SubElementID attribute of the DisplayString element matches the ReportString ID attribute, as in the following code.
```
<DisplayString ElementID="AvailabilityReportingMP.AvailabilityReport" SubElementID="R.ReportTitle">
   <Name>Availability Report</Name>
   <Description>Indicates the title of the report.</Description>
</DisplayString>
```
You can see the text that you entered previously between the Name and Description elements.
You also find a LanguagePack element for each additional language pack that you created in the previous procedure with its associated 3-letter value, for example:
```
<LanguagePack ID="DEU" IsDefault="false" />
```

Delete the slash mark / of the closed element. Then add the following code to provide the necessary display strings for this LanguagePack element and a new closing tag.

<LanguagePack ID="DEU" IsDefault="false">
   <DisplayStrings>
      <DisplayString ElementID="" SubElementID="">
         <Name></Name>
         <Description></Description>
      </DisplayString>
   </DisplayStrings>
</LanguagePack>

If this language is the default language for this management pack, set the IsDefault attribute value to true. Only one language pack can be the default language, so ensure that the IsDefault attribute value on any other language packs is set to false.
In the DisplayString element, set the ElementID attribute to the ID of the report, which should be the same as in the DisplayString element for the default language. Set the SubElementID attribute to be the same as the ID attribute for the report string you created.
Between the Name tags, insert the text that should be displayed for this element when the selected language pack is used. Optionally, between the Description tags, enter a description of this element.
If your report already contains a ReportParameters section, locate it in the file. If you have not defined any ReportParameters elements, this section might not yet be present in your report; therefore you must add it. Following the closing /DataSources tag, add the following code.
```
<ReportParameters>
</ReportParameters>
```

Between the opening and closing ReportParameters tags, add the following code.

<ReportParameter Name="LT_Report">
   <DataType>String</DataType>
   <DefaultValue>
      <DataSetReference>
         <DataSetName>LT_Report_Query</DataSetName>
         <ValueField>ValueXml</ValueField>
      </DataSetReference>
   </DefaultValue>
   <ValidValues>
      <DataSetReference>
         <DataSetName>LT_Report_Query</DataSetName>
         <ValueField>ValueXml</ValueField>
         <LabelField>ValueName</LabelField>
      </DataSetReference>
   </ValidValues>
   <Hidden>true</Hidden>
</ReportParameter>
<ReportParameter Name="LT_Table">
   <DataType>String</DataType>
   <DefaultValue>
      <DataSetReference>
         <DataSetName>LT_Table_Query</DataSetName>
         <ValueField>ValueXml</ValueField>
      </DataSetReference>
   </DefaultValue>
   <ValidValues>
      <DataSetReference>
         <DataSetName>LT_Table_Query</DataSetName>
         <ValueField>ValueXml</ValueField>
         <LabelField>ValueName</LabelField>
      </DataSetReference>
   </ValidValues>
   <Hidden>true</Hidden>
</ReportParameter>

These ReportParameter elements are necessary for the correct functioning of the code that you will add in the next step.

The two ReportParameter elements you just added require their own datasets. Locate the DataSet element in the file. This element is already present, and includes the dataset for your existing report query. Following the last /DataSet end tag that currently exists, add the following code.

<DataSet Name="LT_Report_Query">
   <Fields>
      <Field Name="ValueXml">
         <DataField>XML_F52E2B61-18A1-11d1-B105-00805F49916B</DataField>
      </Field>
      <Field Name="ValueName">
         <Value>="LT"</Value>
      </Field>
   </Fields>
   <Query>
      <DataSourceName>DataWarehouseMain</DataSourceName>
      <CommandType>StoredProcedure</CommandType>
      <CommandText>ReportDisplayStringGet</CommandText>
      <QueryParameters>
         <QueryParameter Name="@ManagementGroupGuid">
            <Value>=Code.GetCallingManagementGroupId()</Value>
         </QueryParameter>
         <QueryParameter Name="@ManagementPackSystemName">
            <Value>[Management Pack ID]</Value>
         </QueryParameter>
         <QueryParameter Name="@ReportSystemName">
            <Value>[Report ID]</Value>
         </QueryParameter>
         <QueryParameter Name="@LanguageCode">
            <Value>=Code.GetReportLocLanguageCode()</Value>
         </QueryParameter>
         <QueryParameter Name="@Scope">
            <Value>R.%</Value>
         </QueryParameter>
      </QueryParameters>
   </Query>
</DataSet>
<DataSet Name="LT_Table_Query">
   <Fields>
      <Field Name="ValueXml">
         <DataField>XML_F52E2B61-18A1-11d1-B105-00805F49916B</DataField>
      </Field>
      <Field Name="ValueName">
         <Value>="LT"</Value>
      </Field>
   </Fields>
   <Query>
      <DataSourceName>DataWarehouseMain</DataSourceName>
      <CommandType>StoredProcedure</CommandType>
      <CommandText>ReportDisplayStringGet</CommandText>
      <QueryParameters>
         <QueryParameter Name="@ManagementGroupGuid">
            <Value>=Code.GetCallingManagementGroupId()</Value>
         </QueryParameter>
         <QueryParameter Name="@ManagementPackSystemName">
            <Value>[Management Pack ID]</Value>
         </QueryParameter>
         <QueryParameter Name="@ReportSystemName">
            <Value>[Report ID]</Value>
         </QueryParameter>
         <QueryParameter Name="@LanguageCode">
            <Value>=Code.GetReportLocLanguageCode()</Value>
         </QueryParameter>
         <QueryParameter Name="@Scope">
            <Value>RT.%</Value>
         </QueryParameter>
      </QueryParameters>
   </Query>
</DataSet>

Without this code, the localization process does not work. Both DataSet elements contain the element QueryParameter Name="@ManagementPackSystemName". Replace the content in the Value element in this QueryParameter element with the ID for the management pack. Also, both DataSet elements contain an element QueryParameter Name="@ReportSystemName". Replace the content in the Value element in this QueryParameter element with the ID for the report.

After the /Datasets end tag, add the following code.

<Code>
Const LocTableStringQuery As String = "R/S[@N='{0}']/text()"

Const TimeZoneParameterName As String = "TimeZone"
Const SD_BaseTypeParameterName As String = "StartDate_BaseType"
Const SD_BaseValueParameterName As String = "StartDate_BaseValue"
Const SD_OffsetTypeParameterName As String = "StartDate_OffsetType"
Const SD_OffsetValueParameterName As String = "StartDate_OffsetValue"
Const ED_BaseTypeParameterName As String = "EndDate_BaseType"
Const ED_BaseValueParameterName As String = "EndDate_BaseValue"
Const ED_OffsetTypeParameterName As String = "EndDate_OffsetType"
Const ED_OffsetValueParameterName As String = "EndDate_OffsetValue"
Const IsRelativeTimeSupported As Boolean = False
Const TimeTypeParameterName As String = "TimeType"
Const TimeWeekMapParameterName As String = "TimeWeekMap"

Dim LocTables As System.Collections.Generic.Dictionary(Of String, Microsoft.EnterpriseManagement.Reporting.XmlStringTable)
Dim ReportTimeZone As Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation
Dim ReportStartDate As DateTime
Dim ReportEndDate As DateTime
Dim ReportTime As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime
Dim ReportCulture As System.Globalization.CultureInfo
Dim ParameterProcessor As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor

Protected Overrides Sub OnInit()
LocTables = new System.Collections.Generic.Dictionary(Of String, Microsoft.EnterpriseManagement.Reporting.XmlStringTable)()
ReportTimeZone = Nothing
ReportStartDate = DateTime.MinValue
ReportEndDate = DateTime.MinValue
ReportTime = Nothing
ReportCulture = System.Globalization.CultureInfo.GetCultureInfo(Report.User("Language"))
ParameterProcessor = New Microsoft.EnterpriseManagement.Reporting.ParameterProcessor(ReportCulture)
End Sub

Public Function GetCallingManagementGroupId() As String
Return Microsoft.EnterpriseManagement.Reporting.ReportingConfiguration.ManagementGroupId
End Function

Public Function GetReportLocLanguageCode() As String
Return ReportCulture.ThreeLetterWindowsLanguageName
End Function

Public Function GetLocTable(Name As String) As Microsoft.EnterpriseManagement.Reporting.XmlStringTable
Dim LocTable As Microsoft.EnterpriseManagement.Reporting.XmlStringTable

If Not LocTables.TryGetValue(Name, LocTable) Then
LocTable = New Microsoft.EnterpriseManagement.Reporting.XmlStringTable(LocTableStringQuery, Report.Parameters(Name).Value)
LocTables.Add(Name, LocTable)
End If

Return LocTable
End Function
</Code>

After the /Code end tag, add the following code.

<CodeModules>
   <CodeModule>Microsoft.EnterpriseManagement.Reporting.Code, Version=6.0.0.0, Culture=neutral</CodeModule>
</CodeModules>

This CodeModules element is required to access the code in the Code.

Next, you must indicate where you want your report string to be displayed. Locate the point inside the body tag where you want the localized text to appear. If you generated the report by using SQL Server 2008 Business Intelligence Development Studio (BIDS), this will most likely be in a TextRun element. Within that TextRun element, replace the text inside the Value element with code similar to the following code.
```
<Value>=Code.GetLocTable("LT_Report").GetString("R.ReportTitle", "[Report Title]")</Value>
```
In place of R.ReportTitle, insert the ID of the ReportString element that you defined earlier. In place of [Report Title], insert the default text; that is, the text that will appear if no report string with that ID is found.
Repeat the previous step for all the report strings that you defined.
Save the file and redeploy the management pack. For more detailed information, see Deploying Reports.

After deploying the report, if the user opens the report by using an operating system set to another language, the report opens with the display strings configured for that language.

Warning

Management packs that include many display strings can become very large. A recommended practice to reduce the file size of the management packs is to move the language pack sections into separate management packs. This requires sealing the parent management pack.

Localizing Reports

Activating Language Support in a Management Pack

To add report strings to a management pack

To add language packs to the management pack

To edit the XML code to include display strings

Additional resources