multilingual | Dutch Data Dude

Multilingual SSRS reports – Scenario 3: Report Definition Customization Extension

on August 27, 2013

This is the third post in my series about multilingual SSRS reports. If you missed the introduction, you can find it here.

I am looking for feedback! Please let me know what you think!

The full code of the solution I describe here is available on Github.

What we will be doing in this post is developing something which called a Report Definition Customization Extension (RDCE), which is available since SQL Server 2008. The MSDN page is here: http://msdn.microsoft.com/en-us/library/dn296363.aspx

Let’s first start with the basics: what is a RDCE and what can it be used for?
Simply put a RDCE transforms an existing report definition (RDL) and modifies it just before the SSRS rendering engine renders the report. In other words building an RDCE allows you to interfere with the report rendering process and make some last minute changes just before rendering starts. In a schema it looks like this:

As this post is about making reports multilingual you might already have guessed that one of the uses of an RDCE is doing translations. However, you can also change the report’s look by hiding elements and even change the data set returned. This is useful if you not only need to translate labels in your report but also want to actually return text strings from your dataset in a different language. Another option would be for example to deal with right-to-left languages by changing your report’s layout.

Developing a RDCE
An RDCE is a .NET class library, where you will have to select .NET framework 3.5 as the target framework. This class library will need a reference to Microsoft.ReportingServices.Interfaces.dll. This contains the interface you will need to implement (see below). So let’s get started.

First off, start Visual Studio and create a new class library project (targeting .NET framework 3.5).

Include a reference to the aforementioned Microsoft.ReportingServices.Interfaces.dll file by right clicking ‘References’ in your Solution Explorer and choosing ‘Add Reference…’

The file is located under your SSRS installation directory. In my case the file was in C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin.

Now open your class file (I renamed it to MyRDCE.cs) and add the following line:

using Microsoft.ReportingServices.Interfaces;

1	using Microsoft.ReportingServices.Interfaces;

Then, implement IReportDefinitionCustomizationExtension by changing your class declaration to:

public class MyRDCE: IReportDefinitionCustomizationExtension

1	public class MyRDCE: IReportDefinitionCustomizationExtension

You can explicitly implement this interface to get an idea of the methods you will need to implement by right-clicking the interface name and choosing ‘Implement Interface Explicitly’.

Your code should now look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.ReportingServices.Interfaces;

namespace SSRSMultilingualRDCE
{
    public class MyRDCE: IReportDefinitionCustomizationExtension
    {
        bool IReportDefinitionCustomizationExtension.ProcessReportDefinition(byte[] reportDefinition, IReportContext reportContext, IUserContext userContext, out byte[] reportDefinitionProcessed, out IEnumerable<RdceCustomizableElementId> customizedElementIds)
        {
            throw new NotImplementedException();
        }

        string IExtension.LocalizedName
        {
            get { throw new NotImplementedException(); }
        }

        void IExtension.SetConfiguration(string configuration)
        {
            throw new NotImplementedException();
        }
    }
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.ReportingServices.Interfaces;

namespace SSRSMultilingualRDCE

{

public class MyRDCE: IReportDefinitionCustomizationExtension

{

bool IReportDefinitionCustomizationExtension.ProcessReportDefinition(byte[] reportDefinition, IReportContext reportContext, IUserContext userContext, out byte[] reportDefinitionProcessed, out IEnumerable<RdceCustomizableElementId> customizedElementIds)

{

throw new NotImplementedException();

}

string IExtension.LocalizedName

{

get { throw new NotImplementedException(); }

}

void IExtension.SetConfiguration(string configuration)

{

throw new NotImplementedException();

}

Let’s start with the easiest method: IExtension.LocalizedName, which just returns the name for your RDCE. I replaced the line in this method with:

get { return "SSRSMultilingualRDCE"; }

1	get { return "SSRSMultilingualRDCE"; }

Yes, I know I should not have hardcoded the name here, but for demonstration purposes this will do.

As developing an RDCE is as hard as it is (never mind the debugging, testing, logging and deployment challenges you will have to deal with) I suggest adopting the framework Holger Schmeling introduced in his blog on RDCE’s. This framework allows you to componentize your transformations into smaller transformations instead of one lengthy bit of code. By doing so the RDCE can be more easily managed and understood. Notice that in this blog I will not deal with logging and error handling; however, in production that should be included. To use this framework we add an interface called ITransformation with one method named Transform. Just add a new Interface file to your solution, name it ITransform and copy paste the following code in (adapted from Holger’s blog):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Microsoft.ReportingServices.Interfaces;

namespace SSRSMultilingualRDCE
{

///<summary>
/// Interface to implement for one RDCE transformation step.
///</summary>

public interface ITransformation
{
///<summary>
/// Receives an XML element and returns transformed element to the sender
///</summary>
///<param name="report">Report to convert.</param>
///<param name="reportContext">Report context as conveyed to the RDCE</param>
///<param name="userContext">User context as conveyed to the RDCE</param>
///<returns>Transformed report</returns>

 XElement Transform(XElement report, IReportContext reportContext, IUserContext userContext);
 }
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Xml.Linq;

using Microsoft.ReportingServices.Interfaces;

namespace SSRSMultilingualRDCE

{

///<summary>

/// Interface to implement for one RDCE transformation step.

///</summary>

public interface ITransformation

{

///<summary>

/// Receives an XML element and returns transformed element to the sender

///</summary>

///<param name="report">Report to convert.</param>

///<param name="reportContext">Report context as conveyed to the RDCE</param>

///<param name="userContext">User context as conveyed to the RDCE</param>

///<returns>Transformed report</returns>

XElement Transform(XElement report, IReportContext reportContext, IUserContext userContext);

}

The Transform() method takes a report definition in XML format and returns the modified version. The other two parameters provide the report and user context as returned from SSRS. This is handy to dynamically apply transforms based on these contexts.

Now it is time to add a list to hold all the transformations (Again thanks to Holger’s blog). Add the following code directly under the class statement in your RDCE class:

//List holding all transformations
private IList<ITransformation> _transformations;

1 2	//List holding all transformations private IList<ITransformation> _transformations;

Also implement the ProcessReportDefinition() method that will be called by reporting services. This is the mother-transformation process. What we will do here is load the report and one-by-one call the registered transformations on the report. Find your ProcessReportDefinition() method and replace it with the following (you will need extra using statements).

bool IReportDefinitionCustomizationExtension.ProcessReportDefinition(byte[] reportDefinition, IReportContext reportContext, IUserContext userContext, out byte[] reportDefinitionProcessed, out IEnumerable<RdceCustomizableElementId> customizedElementIds) {

 //load report from byte[]
 XmlDocument d = new XmlDocument();
 MemoryStream ms = null;
 using (ms = new MemoryStream(reportDefinition)) {
  d.Load(ms);
  ms.Position = 0;
 }
 XElement report = XElement.Load(new XmlNodeReader(d));

 //run all transformations
 if (_transformations != null)
 {
  foreach (ITransformation t in _transformations)
  {
   report = t.Transform(report, reportContext, userContext);
  }
 }

 //convert report to byte[] so it can be returned to SSRS
 System.Text.Encoding enc = new System.Text.UTF8Encoding();
 reportDefinitionProcessed = enc.GetBytes(report.ToString());

 //we have to inform SSRS about what we changed. In this sample we only change the body part of the report.
 //Other values are: DataSets, Page, PageHeader, PageFooter
 List<RdceCustomizableElementId> ids = new List<RdceCustomizableElementId>();
 customizedElementIds = new List<RdceCustomizableElementId>();
 (customizedElementIds as List<RdceCustomizableElementId>).Add(RdceCustomizableElementId.Body);

 return true;
}

//load report from byte[]

XmlDocument d = new XmlDocument();

MemoryStream ms = null;

using (ms = new MemoryStream(reportDefinition)) {

d.Load(ms);

ms.Position = 0;

}

XElement report = XElement.Load(new XmlNodeReader(d));

//run all transformations

if (_transformations != null)

{

foreach (ITransformation t in _transformations)

{

report = t.Transform(report, reportContext, userContext);

}

//convert report to byte[] so it can be returned to SSRS

System.Text.Encoding enc = new System.Text.UTF8Encoding();

reportDefinitionProcessed = enc.GetBytes(report.ToString());

//we have to inform SSRS about what we changed. In this sample we only change the body part of the report.

//Other values are: DataSets, Page, PageHeader, PageFooter

List<RdceCustomizableElementId> ids = new List<RdceCustomizableElementId>();

customizedElementIds = new List<RdceCustomizableElementId>();

(customizedElementIds as List<RdceCustomizableElementId>).Add(RdceCustomizableElementId.Body);

return true;

}

This method first retrieves the report definition. Then all transformations are applied, after which the resulting report definition is returned and SSRS is informed about what we changed.

Following Holger’s framework we use the SSRS config file to define transformations. This might not be the ideal solution for your scenario since it involves editing the SSRS config file. However, on the plus side this allows you to register new transformations as required without having to redeploy or change the RDCE. Basically we need to change the rsreportserver.config (mine was in C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer) file and add something along these lines (do not forget to make a backup first!) as last entry in the <Extensions> node:

<!-- RDCE -->
<ReportDefinitionCustomization>
 <Extension Name="SSRSMultilingualRDCE" Type="SSRSMultilingualRDCE.MyRDCE,SSRSMultilingualRDCE">
  <Configuration>
  <!-- Transformations go here-->
   <Transformations>
    <Transformation Name="MyTranslator" Type="SSRSMultilingualRDCE.Transformations.MyTranslator,SSRSMultilingualRDCE">
     <Property Name="SampleProperty" Value="c:\temp\myDictionary.txt"/>
    </Transformation>
   </Transformations>
  </Configuration>
 </Extension>
</ReportDefinitionCustomization>

</Transformation>

</Transformations>

</Configuration>

</Extension>

</ReportDefinitionCustomization>

You can specify multiple Transformations by repeating the Transformation node as necessary. Note that Name and Type specify the name of the class and the namespace. Also, each Transformation can have custom properties set (name, value pairs).

Now, we can use the SSRS SetConfiguration() methods to get to the configuration settings in the rsreportserver.config file. This enables us to read the list of transformations registered and fill the list created earlier. We will do this by implementing our final function of the IReportDefinitionCustomizationExtension: SetConfiguration() (you will need another using statement at the top of your class).

void IExtension.SetConfiguration(string configuration) {
 if (string.IsNullOrEmpty(configuration)) {
  return;
 }
 _transformations = new List<ITransformation>();
 //load the configuration
 XElement config = XElement.Parse(configuration);
 //retrieve the transformations
 if (config.Name.LocalName == "Transformations")
 {
  //get the transformations
  var trans = from transformation in config.Descendants("Transformation")select new {
  Name = transformation.Attribute("Name").Value,
  Transformator = transformation.Attribute("Type").Value.Split(',')[0],
  CodeBase = transformation.Attribute("Type").Value.Split(',')[1],
  Properties = transformation.Descendants("Property")
  };
  //lets add a transformation step for each transformation found
  foreach (var t in trans)
  {
   ITransformation transformation = (Activator.CreateInstance(t.CodeBase, t.transformator).Unwrap() as ITransformation);
   //if we specified additional properties, set them now
   if(t.Properties!=null) {
    foreach(var p in t.Properties) {
     string propName = p.Attribute("Name").Value;
     string propValue = p.Attribute("Value").Value;
     PropertyInfo propertyInfo = transformation.GetType().GetProperty(propName);
     if(propertyInfo!=null) {
      propertyInfo.SetValue(transformation, propValue,null);
     }
    }
    //add the transformer to the list
    _transformations.Add(transformation);
   }
  }
 }
}

void IExtension.SetConfiguration(string configuration) {

if (string.IsNullOrEmpty(configuration)) {

return;

}

_transformations = new List<ITransformation>();

//load the configuration

XElement config = XElement.Parse(configuration);

//retrieve the transformations

if (config.Name.LocalName == "Transformations")

{

//get the transformations

var trans = from transformation in config.Descendants("Transformation")select new {

Name = transformation.Attribute("Name").Value,

Transformator = transformation.Attribute("Type").Value.Split(',')[0],

CodeBase = transformation.Attribute("Type").Value.Split(',')[1],

Properties = transformation.Descendants("Property")

};

//lets add a transformation step for each transformation found

foreach (var t in trans)

{

ITransformation transformation = (Activator.CreateInstance(t.CodeBase, t.transformator).Unwrap() as ITransformation);

//if we specified additional properties, set them now

if(t.Properties!=null) {

foreach(var p in t.Properties) {

string propName = p.Attribute("Name").Value;

string propValue = p.Attribute("Value").Value;

PropertyInfo propertyInfo = transformation.GetType().GetProperty(propName);

if(propertyInfo!=null) {

propertyInfo.SetValue(transformation, propValue,null);

}

//add the transformer to the list

_transformations.Add(transformation);

}

Now that we have a functioning RDCE framework it is time to put it to work. In this sample I will build a simple translator transformation which will translate some texts in a report by looking them up in a database table. I added a new class to my solution, named it MyTranslator and put it in the SSRSMultilingualRDCE.Transformations namespace as configured in the reportserver.config file above. The code pretty much speaks for itself. It implements the Transform method from the ITransformation interface, retrieves the user language preference setting and retrieves the translation from the database:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.ReportingServices.Interfaces;
using System.Xml.Linq;
using System.Data;
using System.Data.SqlClient;

namespace SSRSMultilingualRDCE.Transformations
{
    class MyTranslator : ITransformation
    {
        private SqlConnection _sqlConn;

        public MyTranslator()
        {
            _sqlConn = new SqlConnection("server=.;Database=SSRSMultiLingual;Trusted_Connection=True");
        }

        public XElement Transform(XElement report, IReportContext reportContext, IUserContext userContext)
        {
            if(reportContext == null)
            {
                throw new ArgumentException("ReportContext not initialized");
            }

            if(!(_sqlConn.State == ConnectionState.Open)){
               _sqlConn.Open(); 
            }

            //get the users userName and look up the preferred language.
            SqlCommand cmd = new SqlCommand("SELECT Language from UserConfiguration where UserName='" + userContext.UserName + "'",_sqlConn);
            SqlDataReader sdr = cmd.ExecuteReader();
            sdr.Read();
            string language = (string) sdr["Language"];
            sdr.Close();

            //translate all textbox items (<TextRun><Value>TEXT</Value></TextRun>)
            //we will skip expressions (starting with =)
            report.Descendants().Where(x => x.Name.LocalName == "Value" && x.Parent.Name.LocalName == "TextRun" && (!x.Value.StartsWith("=")))
                .ToList()
                .ForEach(x => x.Value = Translate(x.Value, language));

            _sqlConn.Close();
            return report;
        }

        private string Translate(string p, string language)
        {
            if (string.IsNullOrEmpty(p))
            {
                return "";
            }

            string translated = p;
            //get translation from database
            SqlCommand cmd = new SqlCommand("SELECT T.Value from Translations T inner join Items I on T.Item=I.Id WHERE I.Name='"+p+"' AND T.Language='"+language+"'",_sqlConn);
            SqlDataReader sdr = cmd.ExecuteReader();
            sdr.Read();
            translated = (string)sdr["Value"];
            sdr.Close();

            //have we got a translation? Of not, fall back to Microsoft Translator
            if (string.IsNullOrEmpty(translated))
            {
                //connect to Microsoft Translator and return translation
            }
            return translated;
        }
    }
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.ReportingServices.Interfaces;

using System.Xml.Linq;

using System.Data;

using System.Data.SqlClient;

namespace SSRSMultilingualRDCE.Transformations

{

class MyTranslator : ITransformation

{

private SqlConnection _sqlConn;

public MyTranslator()

{

_sqlConn = new SqlConnection("server=.;Database=SSRSMultiLingual;Trusted_Connection=True");

}

public XElement Transform(XElement report, IReportContext reportContext, IUserContext userContext)

{

if(reportContext == null)

{

throw new ArgumentException("ReportContext not initialized");

}

if(!(_sqlConn.State == ConnectionState.Open)){

_sqlConn.Open();

}

//get the users userName and look up the preferred language.

SqlCommand cmd = new SqlCommand("SELECT Language from UserConfiguration where UserName='" + userContext.UserName + "'",_sqlConn);

SqlDataReader sdr = cmd.ExecuteReader();

sdr.Read();

string language = (string) sdr["Language"];

sdr.Close();

//translate all textbox items (<TextRun><Value>TEXT</Value></TextRun>)

//we will skip expressions (starting with =)

report.Descendants().Where(x => x.Name.LocalName == "Value" && x.Parent.Name.LocalName == "TextRun" && (!x.Value.StartsWith("=")))

.ToList()

.ForEach(x => x.Value = Translate(x.Value, language));

_sqlConn.Close();

return report;

}

private string Translate(string p, string language)

{

if (string.IsNullOrEmpty(p))

{

return "";

}

string translated = p;

//get translation from database

SqlCommand cmd = new SqlCommand("SELECT T.Value from Translations T inner join Items I on T.Item=I.Id WHERE I.Name='"+p+"' AND T.Language='"+language+"'",_sqlConn);

SqlDataReader sdr = cmd.ExecuteReader();

sdr.Read();

translated = (string)sdr["Value"];

sdr.Close();

//have we got a translation? Of not, fall back to Microsoft Translator

if (string.IsNullOrEmpty(translated))

{

//connect to Microsoft Translator and return translation

}

return translated;

}

Deploying a RDCE

Now that we have successfully built a translator we’re done with development and are ready for deployment. To deploy your RDCE to the SSRS server follow these steps:

First, copy you RDCE assembly (.dll file, in my case SSRSMultilingualRDCE.dll) into the Reporting Services bin directory (which in my case was C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin). If you have referenced any assemblies not in the Global Assembly Cache (maybe you implemented some logging framework) do not forget to copy them here as well.

Next we need to modify the SSRS configuration, starting with the rsreportserver.config file. It is located in C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer. Open it and lookup the <Service> element and add the following to that element (I added it to the bottom):

<IsRdceEnabled>True</IsRdceEnabled>

1	<IsRdceEnabled>True</IsRdceEnabled>

Next configuration change will be done in the rssrvpolicy.config file in the same directory as before. Open the file and add a CodeGroup in the PolicyLevel element for your RDCE assembly, like so:

<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="SSRSMultilingualRDCE" Description="SSRSMultilingualRDCE">
 <IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\SSRSMultilingualRDCE.dll"/>
</CodeGroup>

</CodeGroup>

You will need to specify the correct Name for the CodeGroup (just let it match the nam of your RDCE) and the correct location and name of the DLL you copied.

You may have to set up the permissions in the web.config file as Holger reports in his blog, although I have not had to do that.

To make your reports work with the RDCE there is a little thing we need to do; we need to link our reports with the RDCE deployed. The official way of doing this is using the Reporting Services Management Service. However, to use it you either need to have a deployment tool or be willing to write one. The unsupported way however is directly updating the report properties in the SSRS catalog. Please note that this is unsupported and I give no guarantees whatsoever regarding damage afflicted or the working of this on future versions. If you want to follow along take the following steps:

Open SQL Server Management Studio and execute the following query on your SSRS Service Database (not the TempDB) (you will have to adapt the where clause to match the name of your report):

select ItemID,Path,Name,Description, cast(Property as xml) as Property, cast(Parameter as xml) as Parameter
from dbo.Catalog
where name like '%RDCE%'

select ItemID,Path,Name,Description, cast(Property as xml) as Property, cast(Parameter as xml) as Parameter

from dbo.Catalog

where name like '%RDCE%'

This returns among others the ItemID, which you will need for the next statement. What we need to do is add <RDCE>Name_Of_Your_RDCE</RDCE> to the properties of the reports that need to use the RDCE.

Your will need to execute a query that looks like mine:

update dbo.Catalog set Property='<Properties> <Language>en-US</Language>  <HasUserProfileQueryDependencies>False</HasUserProfileQueryDependencies>
 <HasUserProfileReportDependencies>False</HasUserProfileReportDependencies>
 <PageHeight>279.4</PageHeight>
 <PageWidth>215.9</PageWidth>
 <TopMargin>25.4</TopMargin>
 <BottomMargin>25.4</BottomMargin>
 <LeftMargin>25.4</LeftMargin>
 <RightMargin>25.4</RightMargin>
 <RDCE>SSRSMultilingualRDCE</RDCE>
 </Properties>'
where ItemID='BBA6EBDD-F094-4E10-892A-D9DF2834A787'

update dbo.Catalog set Property='<Properties> <Language>en-US</Language> <HasUserProfileQueryDependencies>False</HasUserProfileQueryDependencies>

<HasUserProfileReportDependencies>False</HasUserProfileReportDependencies>

<RDCE>SSRSMultilingualRDCE</RDCE>

</Properties>'

where ItemID='BBA6EBDD-F094-4E10-892A-D9DF2834A787'

I have simply copied and pasted the ItemID in the where clause and also copied and pasted the value of property from the previous query into this query. I have appended the <RDCE> element just before the closing of the properties element. My report will now use the RDCE.

Debugging

When rendering a report that uses a RDCE SSRS will display any errors thrown by your code. This helps in debugging. Also note that if you make any changes to the code you will have to recopy your assembly and restart the SSRS service for SSRS to pick up the new version.

Putting it all together

All that remains now is showing that this works. I have created a very simple report for this:

This report contains two textboxes with labels in them. These labels were defined in the database and the database contains a translation for it. As a result my RDCE will translate these labels, depending on the user’s preferred language.

So, when a user with preference for English logs in this will be displayed:

And when a user with preference setting for Dutch opens the report it displays as follows:

My solution works with a configuration database setting for the user’s preference. However, just as easily one could adapt the code to read the setting from the SharePoint profile (if using SSRS in SharePoint integrated mode) or for example from Active Directory.

That concludes my lengthy blog on RDCEs. You can download my solution on Github. It includes a database project that will create the simple database I used and enter some sample data. Also, it includes all code discussed and the sample report I used. The configuration changes are for security reasons not included.

I am looking for feedback! Please let me know what you think!

Multilingual SSRS reports – Scenario 2: Change RDL

on August 20, 2013

Business Intelligence

by Jeroen ter Heerdt 1 Comment

This is the third post in my series about multilingual SSRS reports. If you missed the introduction, you can find it here.

In this post we will talk about the second implementation scenario, which changes the RDL after creating it. The diagram below helps to understand this:

This means that developing the report is independent of making it available in multiple languages.
This means there is no impact on the process of creating a report, where with the first scenario (custom assembly) there was an impact (and a rather big one!).

The downside of this solution however is that there will be a separate process manipulating the RDL after it has been developed. This has a downside however: if the RDL language schema changes (and it does just about every new release of SQL) you will have to check if your code still works.

Now, the process that changes the RDL could do two things: 1) change the original RDL and add localization that will actually localize the report at run time or 2) change the original RDL and make a copy of it for every language (essentially you get the same report multiple times).

The first option here is just an automated version of scenario number 1 (the custom assembly) which we discussed earlier. It however eliminates the biggest issue with scenario 1: the fact that it is a manual process and has to be repeated for every label. However, what this option doesn’t do is allow you to translate parameter prompts, which option 2 does. Downside of option 2 however is that multiple copies of the report get created (one for each language). Creating multiple copies of the report (one for each language) would have no impact on rendering the report and may be a good choice if you want to manage each language separately. You will need to decide for yourself what you want to do, the basic architecture of this scenario stay the same.

In this post we will deal with the latter option (option 2).

I envision the process that changes the RDL as just a process that gets executed periodically. The process reads the RDL and translates any text it finds again using the translation table, resource file or whatever solution you picked for storing translations.

Implementing the process is out of scope for this blog because it is a matter of reading an XML file (RDL is XML structured) changing some items and writing it to disk. Any .NET developer could do it, for example using XPath.

The trick of course is knowing what to find in the RDL and what to change.

The simplified structure of RDL (SQL 2012) is the following (I stripped away all that is not related to localization):

<?xml version="1.0" encoding="utf-8"?>
<Report>
    <DataSources/>
    <DataSets>
        <DataSet/>
    </DataSets>
    <ReportSections>
        <ReportSection>
            <Body>
                <ReportItems>
                    <TextBox/>
                    <Tablix/>
                    <Chart/>
                </ReportItems>
            </Body>
            <Page>
                <PageHeader>
                    <ReportItems>
                        <TextBox/>
                    </ReportItems>
                </PageHeader>
                <PageFooter>
                    <ReportItems>
                        <TextBox/>
                    </ReportItems>
                </PageFooter>
            </Page>
        </ReportSection>
    </ReportSections>
    <ReportParameters>
        <ReportParameter/>
    </ReportParameters>
    <Language/>
</Report>

<?xml version="1.0" encoding="utf-8"?>

</DataSets>

<Body>

</ReportItems>

</Body>

<Page>

</ReportItems>

</PageHeader>

</ReportItems>

</PageFooter>

</Page>

</ReportSection>

</ReportSections>

</ReportParameters>

</Report>

As you can see, there a just a couple of items we need to look for when scanning the RDL:

Report.DataSets.DataSet
DataSets define the queries to the source systems. If we want to localize result sets we need to manipulate the query here.
Report.ReportSections.ReportSection.Body.ReportItems / Report. ReportSections.ReportSection.Page.PageHeader.ReportItems / Report. ReportSections.ReportSection.Page.PageFooter.ReportItems
ReportItems can be TextBox, Chart, Tablix, which will be discussed in more detail later.
Report.ReportParameters.ReportParameter
Parameter prompts can be localized here.

Localizing a DataSet
A dataset defines the <CommandText> which essentially is the query to the source system. When changing the RDL one can easily add a where-clause to the query indicating the language to render:

Where translationtable.language='en-us'.

1	Where translationtable.language='en-us'.

What you will be looking for is Report.DataSets.DataSet.CommandText to do this.

Localizing ReportItems
ReportItems can be TextBoxes, Charts, and Tablixes each of which carry one or more labels that need localization.
The structure of a TextBox looks like this:

<TextBox>
    <Paragraphs>
        <TextRuns>
            <TextRun>
                <Value/>
            </TextRun>
        </TextRuns>
    </Paragraphs>
</TextBox>

</TextRun>

</TextRuns>

</Paragraphs>

</TextBox>

You will be wanting to localize what is inside <Value></Value> tag.

For Tablixes you will also be looking for the <Value></Value> tags inside TextRuns on Cells, which are just TextBoxes. Here is the basic structure of a Tablix:

<Tablix>
    <TablixColumns/>
    <TablixRows>
        <TablixRow>
            <Height/>
            <TablixCells>
                <TablixCell>
                    <CellContents>
                        <TextBox/>
                    </CellContents>
                </TablixCell>
            </TablixCells>
        </TablixRow>
    </TablixRows>
</Tablix>

</CellContents>

</TablixCell>

</TablixCells>

</TablixRow>

</TablixRows>

</Tablix>

You will want to change what is inside the <Value></Value> tag of each TextBox. The TextBox here has the same structure as above.

Finally, Charts are a bit different, their basic structure is like this:

<Chart>
    <ChartCategoryHierarchy/>
    <ChartSeriesHierarchy>
        <ChartMembers>
            <ChartMember>
               <Label/>
            </ChartMember>
        </ChartMembers>
    </ChartSeriesHierarchy>
    <ChartData>
        <ChartSeriesCollection>
            <ChartSeries Name=""/>
        </ChartSeriesCollection>
    </ChartData>
    <ChartAreas>
        <ChartArea>
            <ChartCategoryAxes>
                <ChartAxis>
                    <ChartAxisTitle>
                        <Caption/>
                    </ChartAxisTitle>
                </ChartAxis>
            </ChartCategoryAxes>
            <ChartValueAxes>
                <ChartAxis>
                    <ChartAxisTitle>
                        <Caption/>
                    </ChartAxisTitle>
                </ChartAxis>
            </ChartValueAxes>
        </ChartArea>
     </ChartAreas>
     <ChartLegends>
        <ChartLegend>
            <ChartLegendTitle>
                <Caption/>
            </ChartLegendTitle>
        </ChartLegend>
     </ChartLegends>
     <ChartNoDataMessage>
         <Caption/>
     </ChartNoDataMessage>
</Chart>

<Chart>

</ChartMember>

</ChartMembers>

</ChartSeriesHierarchy>

</ChartSeriesCollection>

</ChartData>

</ChartAxisTitle>

</ChartAxis>

</ChartCategoryAxes>

</ChartAxisTitle>

</ChartAxis>

</ChartValueAxes>

</ChartArea>

</ChartAreas>

</ChartLegendTitle>

</ChartLegend>

</ChartLegends>

</ChartNoDataMessage>

</Chart>

You can localize the following items on charts:

Series name: Chart.Chartdata.ChartSeriesCollection.ChartSeries.Name
Axis Title: Chart.ChartAreas.ChartArea.ChartCategoryAxes.ChartAxis.ChartAxisTitle.Caption and ChartAreas.ChartArea.ChartValueAxes.ChartAxis.ChartAxisTitle.Caption
Chart legend title: Chart.ChartLegens.ChartLegend.ChartLegendTitle.Caption
No data message: Chart.ChartNodataMessage.Caption

Localizing Report Parameters
ReportParameters define data types, default values, valid values and also the prompts. The last one you will be wanting to localize.

The basic structure of the ReportParameter definition is:

<ReportParameter>
   <DataType/>
   <DefaultValue/>
        <Prompt/>
   <ValidValues/>
   <MultiValue/>
</ReportParameter>

</ReportParameter>

You will be looking for the ReportParameter.Prompt tag.

That concludes our overview of changing the RDL to implement localization. I agree it is not the most elegant solution as it increases dependency and complexity in your environment, however it is fairly simple to implement and provides a complete localization opportunity, from datasets to report items and even parameter prompts.

Stay tuned for the next implementation scenario!

Multilingual SSRS reports – Scenario 1: Assembly

on July 2, 2013

Business Intelligence

by Jeroen ter Heerdt 4 Comments

This is the second post in my series about making SSRS report multilingual. You just missed the first post, the introduction and comparison of the solution scenarios.

This solution consists of an assembly registered in each report that should support multiple languages / localization. The assembly retrieves translation from a source, such as a data source, resource file, web service or online translation service. Anything is possible here.

This solution is limited in the fact that it will not translate dataset results nor parameter prompts. In addition there is an impact on the report creation process as you will see down the line. The upsides are that it is a relatively straightforward solution that has only little impact on report rendering performance.

1. Creating the assembly
To implement this solution we first need the assembly that will do the actual translation / localization. To build this assembly fire up Visual Studio and create a new Class library project. I will use C# here (and in all my samples on this blog, but another language would work just as well). I named the project SSRSMultiLingualAssembly.

When the new solution and project has been created, rename Class1 to something that makes more sense. I renamed it to SSRSMultiLingual.

Open the class and add the following method to it:

public string Translate(String cultureInfo, String item)
        {
            //Put logic here
            //return the translated string
            return "PLACEHOLDER: " + item +" translated into "+ cultureInfo;
        }

public string Translate(String cultureInfo, String item)

{

//Put logic here

//return the translated string

return "PLACEHOLDER: " + item +" translated into "+ cultureInfo;

}

What you would need to do is implement the class and translate the item using the cultureInfo and return the translated result. This is very dependent on your actual situation and also fairly independent on the actual scenario you choose to implement.

For now, let’s continue to the SSRS side of things to tie things together. Later we can deal with actually making it do something.

After you have added the code, build the solution and make sure there are no errors.

2. Copy assembly to the SSRS folders

To make the assembly you have just built assessable from reports, you will need to copy it over to the SSRS directories, including any resource files or whatever your assembly needs to work. Of course you could make this part of a custom build action and kind of automate this action, but for now here is the manual method.

Copy the assembly into the Report Server path: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin
(this path may change based on installation location and version of SQL you have installed). This will register the assembly with the report server, which is strictly only required after deployment of the reports to the server and not during design / development time.

In order to make it accessible during design time, the assembly also needs to be copied to the report designer folder. Copy and paste the assembly to C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies (again, this path may change based on installation location and version of SQL / Visual Studio you are working with). Note the (x86) in C:\Program Files (x86), since the same directory also exists in C:\Program Files if you are working on a x64 system (which I assume you are).

By the way, if you use reporting services in native mode (i.e. not integrated with SharePoint) you also might have to copy the same assembly to C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportManager\bin. I have not tried this since I am running SSRS in SharePoint integrated mode.

Note that you could also add the assembly to the global assembly cache (GAC).

3. Add the assembly to your reports
In order for your reports to successfully use the assembly to translate text open a report in Report Designer (your Visual Studio environment).

From the report menu select report properties. In the dialog box click references.

Click add and then click on the ellipsis button (…) at the end of the newly added row. In the next dialog box (add reference) click browse, locate the assembly and click ok.

Verify that your assembly has been added. In the bottom part of the dialog enter your class name (assemblyname.classname) and set up an instance name. Note that the class name is case sensitive. Your instance name can be anything, I chose to set my instance name to ‘myML’.

Close the dialog by clicking ok.

Now that we have added the custom assembly to the report it is time to call the translate function to translate text!

4. Calling the Translate function
Now that the report has a reference to the custom assembly, let’s wire up the last part to actually translate text. Start by either adding a textbox to your report or selecting a textbox (or any other label for that matter). Right-click the textbox and select expression… (Expressions allow you to do some limited programming that gets executed when the report renders). In the expression dialog box, enter the following code:

=Code.<your instance name>.Translate(user.Language,"<your text to translate>")

1	=Code.<your instance name>.Translate(user.Language,"<your text to translate>")

For example, with the settings I made the expression dialog box looks like this:

Click ok to close this dialog. Never mind the red squiggly line.

Now, the big moment is there! In report designer click preview and see the results of your labor!

Come on, pat yourself on your back, you have successfully registered a custom assembly and used it in a report. That wasn’t too hard now was it?

Note that you will have to set up this expression with the correct parameter value for every single text on the report that you want to translate. This is very laborious and error prone. Also, this is the reason why this solution does not translate parameters.

5. Change Reporting Services configuration and deploy report
Once you have completed the report and am certain it works OK it is time to deploy the report to the report server. For the assembly to work there you will need to have the assembly in the appropriate places (see above) make some configuration changes and you will have to restart your Report Server after any change in the assembly, including first registration.

If you do not make the required configuration changes you will get the following error when deploying:

Assuming you have already copied the assembly to the correct directories as indicated above, let’s make the required configuration changes. You will have to open the rssvPolicy.config file located in C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer. Open the file and find the <CodeGroup> tags. There will be a bunch of them. Basically what we need to do is add one of our own after the last <CodeGroup> tag. This is what you will have to add:

<IMembershipCondition Version="1" Url="C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\SSRSMultiLingualAssembly.dll" />

1	<IMembershipCondition Version="1" Url="C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\SSRSMultiLingualAssembly.dll" />

Change the Url value to match your situation. This code group gives the assembly (your assembly in the directory specified above (which you will have to change based on your installation) full trust permissions. For testing purposes you could get rid of the SSRSMultiLingualAssembly.dll part and give any assembly in the directory full trust, however in production you will have to set up the code group as specific as above, so why not do it right from the start?

Now it is time to restart the Reporting Services service. To do this either use services.msc or use SQL Configuration Manager.

Now, deploy the report and render the report. You can test different languages by switching your Internet Explorer to another language (Settings à Internet Options à Languages).

Rejoice! You have successfully implemented this scenario of localization of SSRS reports. It may be time to check out the other implementation scenarios.

Multilingual SSRS reports

on June 25, 2013

Business Intelligence

by Jeroen ter Heerdt 4 Comments

When thinking about supporting multiple languages for SSRS reports, most people think about changing the display language of one or more of the following items:

Labels (text boxes, axis labels, table headers, etc.)
Dataset results
Parameter prompts

When considering presenting SSRS reports in multiple languages, one also needs to consider where to store translations. Translations can either be:

stored in a data source (database, cube) or resource file, either accessed directly from the report or through a service call
retrieved from an external translation service, such as Bing Translate

In most solutions retrieving a predefined translation would be preferable to doing an automatic translation using an external translation service, since the quality of the results can be questionable. A scenario I come across often is that the external translation service is only used when a predefined translation is not available. The scenarios discussed in this series of posts do not pose any requirement on how the translation is retrieved. Reporting Services can pass the user’s language setting along. That can then be used to get the right translation.

Additionally, some of the items considered when thinking about a multilingual SSRS solution are:

Can the report be developed once and presented in multiple languages? All of the scenarios discussed in this series provide this capability.
Impact on report creation process. Does the solution chosen require manual activities when designing the report?
Performance
Implementation complexity

In this series of blog posts we talk about options for implementing multilingual SSRS reports. Below is a quick scoring of each option on the above capabilities and requirements. Of course the importance of requirements and the correct choice depends on the situation.

Each scenario will be discussed in a post in this series.

Requirement / Scenario	Assembly	Change RDL	Report Definition Customization	Custom ReportViewer
Translate labels (textboxes, axis labels, table headers, etc.)	Yes	Yes	Yes	Yes
Translate dataset results	No	Yes	Yes	Yes
Translate parameter prompts	No	Yes	No	Yes
Impact on report creation	High	Low	Low	Low
Impact on report rendering performance	Low	Low	Medium	Medium
Implementation complexity	Low	Medium	High	Very High