Reporting Services - translating labels into different languages

Question

I'm finishing up my reports in my SQL Server 2008 Reporting Services project, and as one of the last steps, I need to make things translateable.

Since I have a bunch of reports, and they all share some identical labels, I decided to put all those labels I need to show into a SQL Server table, and I am surfacing that contents as a DataSet dsReportLabels in my reports.

This DataSet basically contains two fields: LabelName is the name of the label (e.g. "Count of items"), and Caption contains the text in the chosen language to be shown on the report.

But now here comes my mental block: how do I assign the dsReportLabels.Caption value to a e.g. textbox, based on the dsReportLabels.LabelName ?

So I need something like (pseudo-LINQ statement):

Textbox1.Value = from dsReportLabels 
                 where LabelName = "some value" 
                 select Caption;

but how do I express that in a Reporting Services code snippet?

I know how to reference things like Parameters!MyParameterName.Value and so on - but that doesn't really work here when I'm trying to extract a value from one column of the DataSet, given the value of the other column in that DataSet.

I bet this is totally easy to do in the end.... just can't seem to wrap my head around this right now.... anyone out there know how to do this?

Answer 1

This MSDN blog post describes one way of doing it. Essentially:

1. Create a lookup table with the LabelID, Language, and Caption.
2. Create a Stored Proc that gets all of the labelIDs and captions for a specified language.
3. Store the results of the SP in a dataset.
4. Store the dataset in a multi-value parameter.
5. Use the multi-value parameter in a custom lookup function.

So, the expression in your label textbox would call the custom function with the labelID, which would get the appropriate caption for the appropriate language.

Report Server 2008 also has a built-in Lookup function that may allow you to skip steps 4 and 5. If this is the case, your expression would call the built-in lookup function, which would go directly to the dataset. I don't have RS 2008, so I can't test this.