Multilingual SSRS reports – Scenario 2: Change RDL

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>

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'.

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>

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>

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>

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>

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!