Multi-lingual SSRS reports

So you want to make your reports multi-lingual, sensitive to each user's browser language preference. Wow! That sounds like a big job. That's what I thought first of all, but there was a requirement, so a bit of investigation later, it seems like things have got a bit easier...

First of SSRS reports expose a global parameter [User!Language] which reflects the user's primary language (top of list in IE). This is very helpful because it's not possible to find this out client-side in Javascript (after much trying!). It's only available as a server-side parameter.

Secondly, thanks to Google's TranslateAPI, you can automate a 'rough-cut' translation for the labels and tooltips which you will want to be language-sensitive.

Thirdly, in reports you can substitute your 'hard-coded' label for an embedded function call to retrieve something that is language-sensitive. Although it's a little annoying that you must pass into this function any references as the functions are operating in a 'sandbox'. Anyway, A decision I made was to store all the translations in the report RDL itself, rather than in the database - this might not be best for everyone. The translations can be stored as hidden parameters and retrieved by the embedded function.

eg. =Code.GetLabel(User!Language, Parameters!Labels, "Contact owner")

You've got all the tips now, but it still seems some work to do a number of reports. So the job really comes down to mechanising the whole operation, and for that I can show some code I used. Credit must go to all the sources I used to find most of the techniques, of which there were several.

Anyway I knocked up a utility to load up the RDL DOM, make a nodelist of the labels and tooltips and substitute the function call, whilst taking the previously existing (en) text, 'Googlising' it into 5 other languages and storing it as hidden parms.

Voila. Enjoy!

XmlDocument xml = new XmlDocument();
xml.Load(sFile);
//temporarily remove default namespace to avoid excess references
//also simplifies the code considerably!
string defns = xml.DocumentElement.NamespaceURI;
xml.LoadXml(xml.OuterXml.Replace("xmlns=\"" + defns + "\" ", ""));
XmlNode code = xml.SelectSingleNode("//Code");
if (code == null)
{//then make this node
XmlNode ds = xml.SelectSingleNode("//DataSets");
code = xml.CreateElement("Code");
xml.DocumentElement.InsertBefore(code, ds);
}
if (! code.InnerText.Contains("Public Function GetLabel"))
{//then add required function
code.InnerText += "Public Function GetLabel(Lang as String, P as Parameter, Label as String) as String" + Environment.NewLine;
code.InnerText += "Dim i As Integer" + Environment.NewLine;
code.InnerText += "For i = 0 to Ubound(P.Value)" + Environment.NewLine;
code.InnerText += "If (P.Value(i) = Left(Lang,2) + \"_\" + Label) Then Return P.Label(i)" + Environment.NewLine;
code.InnerText += "Next i" + Environment.NewLine;
code.InnerText += "Return Label" + Environment.NewLine;
code.InnerText += "End Function";
}

XmlNode parms = xml.SelectSingleNode("//ReportParameters");
XmlNode parm = parms.SelectSingleNode("//ReportParameter[@Name='Labels']");
if (parm == null)
{//then make this node
parm = xml.CreateElement("ReportParameter");
parms.AppendChild(parm);
XmlAttribute nm = xml.CreateAttribute("Name");
nm.Value = "Labels";
parm.Attributes.Append(nm);
parm.InnerXml = "String";
parm.InnerXml += "";
parm.InnerXml += "true";
parm.InnerXml += "";
parm.InnerXml += "true";
}
XmlNode defvals = parm.SelectSingleNode("DefaultValue/Values");
XmlNode valvals = parm.SelectSingleNode("ValidValues/ParameterValues");
string[] lang = { "en", "it", "fr", "de", "nl", "es" };
Language[] lenum = { Language.English, Language.Italian, Language.French, Language.German, Language.Dutch, Language.Spanish };
XmlNodeList lbls = xml.SelectNodes("//Textbox/Value|//Textbox/ToolTip");
foreach (XmlNode lbl in lbls)
{
if (! lbl.InnerText.StartsWith("="))
{
string def = lbl.InnerText;
lbl.InnerText = "=Code.GetLabel(User!Language, Parameters!Labels, \"" + def + "\")";

for (int i = 0; i < 6; i++)
{
XmlNode defval = xml.CreateElement("Value");
defval.InnerText = lang[i] + "_" + def;
defvals.AppendChild(defval);
XmlNode valval = xml.CreateElement("ParameterValue");
valval.InnerXml = "" + lang[i] + "_" + def + "";
valval.InnerXml += "" + Translator.Translate(def, Language.English, lenum[i]) + "";
valvals.AppendChild(valval);
}
}
}
//add back the default namespace
XmlAttribute rdef = xml.CreateAttribute("xmlns");
rdef.Value = defns;
xml.DocumentElement.Attributes.Prepend(rdef);
xml.Save(sFile);