SQL Server Reporting Services is an open system that can be enhanced by the implementation of so-called extensions. Before SQL Server 2008 those extensions fell into the following categories:

• Data Processing. By implementing a data processing extension, you can define custom data sources that transfer arbitrary data to a report dataset.
• Delivery. Delivery extensions allow you to define the means of delivering the completed report. By default, a report may be delivered via email or by storing it in a file share. If those two alternatives are not adequate for you, you may develop your own delivery extension e.g. for delivering reports via SMS or FTP.
• Rendering. Reports can be rendered in a variety of formats, like Excel, Word, or PDF. If you need your own custom format, you’re free to develop one and add it to the system.
• Security. By default, reports are secured through Microsoft Windows Authentication. You may, however, implement your own set of security rules for particular security requirements.

Starting with SQL Server 2008, another interesting extension has been added: the Report Definition Customization Extension (will be abbreviated by the term RDCE through the rest of the article). In this article I will show you what RDCEs are good for, and how to develop and deploy your own RDCE. We start by answering a couple of obvious questions and afterwards develop an example RDCE that allows the implementation of multi-language reports.

What is a RDCE?

There’s a very simple answer to that question: a RDCE takes an existing report definition and modifies it before handing the modified version over to the SSRS rendering engine. A report definition is stored in a particular XML Format (called RDL for Report Definition Language). So what a RDCE is all about is receiving a XML report definition (imagine this as an RDL document), modifying or transforming it, and providing it to the rendering engine.

The following picture shows the simplest illustration of this process:

What can an RDCE be used for?

We could also ask this question from a more technical perspective: What transformations are possible or conceivable?

As soon as you start reflecting about this topic, you certainly come up with a lot of thoughts and ideas of your own. Here are only a few suggestions:

• Translation. Static texts inside any report definition reside in TextBox elements. This offers an easy way for translating a report “on the fly”. We can have our RDCE replacing those texts with language-specific versions. In a broader sense, we may want to translate special technical terminology. For example, some clients could describe a financial year as fiscal year, others may call it financial year.
• Hide report elements. This can be useful, if some parts of a report are redundant for the current data set. Imagine a table column that reveals the same value for all rows. A column like this isn’t very useful and you may not want to show this column at all. Another interesting opportunity would be hiding elements that a user hasn’t paid for, such as sophisticated diagrams. Furthermore, you could also hide elements that a user is not authorized to look at. (Please be patient, a little further down I’ll show you, how easy it is, to retrieve user information.)
• Modifying data sets. Data sets may also be altered at runtime. You may, for example, further limit the number of rows and thereby add additional user dependent filter criteria.

How is an RDCE developed?

An RDCE is developed as a .NET class library. For SQL Server 2008 and above, be careful to select .NET Framework 3.5 as target framework, since 4.x won’t work. You also need to include a reference to Microsoft.ReportingServices.Interfaces.dll. This assembly contains the namespace Microsoft.ReportingServices.Interfaces which covers the required interface definitions (actually it’s only one) that you will have to implement. There is more detail on this later in the article.

I’d also suggest that you should include some kind of logging. This is because debugging can be very tricky, so relevant and conclusive logging outputs are really helpful. Open source logging frameworks, like NLog or log4net, are available and I highly advice you to make use of those. The sample project that I introduce in the second part of this article utilizes NLog but of course it’s your choice which one you prefer.

How complex is the process of developing and deploying an RDCE?

Unfortunately, this question doesn’t have an easy answer. On the one hand, coding is very straightforward. There’s only one interface that you have to implement. This interface is named Microsoft.ReportingServices.Interfaces.IReportDefinitionCustomizationExtension and is itself derived from Microsoft.ReportingServices.Interfaces.IExtension. Altogether there are only three methods that need to be developed:

Here’s a short explanation of the three functions. A little further down you’ll see some code examples.

• IExtension.LocalizedName(). This method simply has to return a name for your RDCE.
• IExtension.SetConfiguration(). A RDCE can be configured by incorporating a specific section named <ReportDefinitionCustomization/> into the reportserver.config file. The SetConfiguration() function is called for every configuration element that is included inside the <ReportDefinitionCustomization/> section. You’ll see an example of how this method can be utilized in the second part further down.
• IReportDefinitionCustomizationExtension.ProcessReportDefinition(). This method does the real work. Beside some context information about the report itself and the executing user it also receives the report definition (as a byte stream) and provides the modified definition to the caller. It is inside this function where the transformation takes place and the report definition is modified.

Believe it or not, but implementing these functions really is the easy part. It starts to get complicated as soon as we start talking about testing, debugging, deployment of the RDCE, and also deployment of reports that can make use of the deployed RDCE. This process is somewhat cumbersome and therefore I’ll say that developing a RDCE is both simple and difficult. Coding isn’t too hard at all but integrating everything into an existing SSRS infrastructure is somewhat tricky. Unfortunately, the documentation is consistently lacking, so normally a lot of trial and error is necessary before everything is in place. I’ve gone through this rather painful process already and I am now ready to share my experiences with you. So, hopefully, you will profit from the explanations that I provide to you in this article.

To summarize, here are the key obstacles you’ll have to overcome when implementing a RDCE:

• Debugging is very tricky. The main reason for this is that when developing reports, you can’t integrate a RDCE into the Visual Studio development environment. Therefore it is not possible to test whether it is working whilst you’re developing the report. In order to test your RDCE, you will have to integrate it into an existing SSRS installation (that is, to deploy it). If you have to debug the RDCE there’s no other way than to attach the RDCE DLL to a running SSRS service in the Visual Studio debugger. If Visual Studio doesn’t run on the same machine than the SSRS service, you need remote debugging which is not functional in all VS editions, and even if you have it, it is usually not very straight-forward to have it set up and running. This immediately leads to the two following bits of advice.
• Do a lot of unit testing. You probably know that unit tests should be an integral part of the development process. This simple statement is even more true if debugging is hardly possible at all. So be sure to write a group of substantial tests to ensure that your RDCE is fully functional and ideally bug-free.
• Include sound logging. I repeat it again: logging is essential. It can be a problem to find possible errors if you neglect to include intensive logging, so please be sure not to forget this. Otherwise you may find yourself inspecting the Report Server log files a lot, which can be quite awkward.
• Report deployment must be enhanced manually. If you have successfully deployed your RDCE this doesn’t mean that all your reports also can make use of this RDCE automatically. In fact, every report that is intended to be used with the RDCE must be adapted. Unfortunately, this necessary modification cannot be made during report development, as Visual Studio simply doesn’t support this. You have to modify the deployed report manually; otherwise the report will silently ignore the RDCE. I’ll show you a little further down, how the required modifications can be made.

And finally: always keep in mind that the documentation is not very extensive. It took me some trial and error before I had my RDCE up and running, because it’s quite hard to find examples and cookbook-like explanations inside the documentation.

So, now that the introductory questions are all answered, we can start the adventure: developing and deploying our sample RDCE.

So, finally: lets implement or own RDCE

I hope you’re still curious enough to stick with me and continue the journey. In this part it gets real. We will develop a framework that can be consumed to perform transformations of any kind to an existing report definition. Subsequently we will utilize this framework for an easy implementation of multi-language reports.

A framework for multiple transformations inside a single RDCE

Think back to the first picture in this article and imagine that you will want to include more that one transformation to a report definition. In other words: you may not want to have only one (large) transformation but would prefer to divide or separate the whole transformation into several transformation steps. Each of those steps would perform a well-defined transformation, and all the steps performed in sequence would encompass the required modification of the report definition. We will develop a framework that can process any desired number of transformation steps. With three steps the first picture then looks like this:

The framework should allow the implementation of user-defined transformational steps and the dynamic incorporation of those steps via simple modifications inside the reportserver.config configuration file.

Introducing the ITransformation interface

If we allow custom transformation steps, it is certainly a good idea to define an interface that any of those transformation steps must adhere to. We call this interface ITransformation and include only one method named Transform():

The Transform() method receives a report definition in its XML format and returns the modified definition to the caller. The other two parameter types are part of the Microsoft.ReportingServices.Interfaces namespace. They can be used to retrieve information about the execution context, like report name, or user name, e.g. the execution engine passes this information to the call of IReportDefinitionCustomizationExtension.ProcessReportDefinition. Our framework will simply forward those parameters to every single transformation step, so steps can be adjusted to the execution context (such as individual users not being permitted to see distinct parts of a report).

Performing all transformations

The framework collects all the steps of the transformation in one collection (a List in our implementation). We will ensure that, at the time the ProcessReportDefinition() method is executed, this list is already populated with all the required transformation steps. Inside the ProcessReportDefinition() function, we just need to iterate through the list and perform every included transformation step. Here’s a simplified code snippet that demonstrates this process:

For the sake of readability the code has been simplified somewhat. You don’t see any logging or error handling but it would certainly be there in production! For the complete code please refer to the attachment on top of this article.

Defining transformation steps

You may ask how the single transformation steps are being defined and loaded. Remember, our goal is to have everything dynamic, so it should be possible to develop and include transformations at any time without a single modification being required of the RDCE itself.

We will, therefore, use the configuration file for the specification of transformation steps. Inside the RDCE we use .NET reflection to create concrete instances of each configured transformation step. Please remember that, for a transformation, the framework only expects the implementation of the ITransformation interface. This allows developers to implement their own transformation inside a custom assembly. There’s no need to implement all transformational steps inside the RDCE assembly itself, so you can enhance the system without any changes to the RDCE.

Inside the reportserver.config configuration file we use the section <ReportDefinitionCustomization/> for the arrangement of transformation steps. Here’s a sample of how this section might look:

The <Transformation/> element can be repeated for every transformation that should be processed. Each step has to be configured by providing a name via the Name attribute. In the Type attribute you indicate the class name (including the complete name space), and also the name of the assembly in which this class can be found. Inside a transformation step definition we can also define additional properties. The framework will take care that these properties are initialized according to the definition.

We then rely on the feature that the SSRS engine is calling the IExtension.SetConfiguration() function once for each configuration element. The engine does this, before the method ProcessReportDefinition() is actually executed, so we can be sure that all transformation steps are ready to use, when the transformation is processed.

Inside the SetConfiguration() function we check if the provided element is a transformation, and if it is, we load all contained steps via .Net reflection. The simplified code (again: no logging, no error handling) looks like this:

The SetConfiguration() method is called for any element inside the <Configuration/> section of the extension. In our configuration this call will happen to all declared elements inside the highlighted part:

If you look back you can see that there’s only one element included in our <Configuration/>, named <Transformations/>. The SetConfiguration() function receives this section at once and then iterates through all <Transformation/> elements that reside in the <Transformations/> section. For each element, the code dissects the configuration, loads the interface implementation, and appends it to the list of transformation steps. As an additional feature, it is also possible to specify properties and property values. Our transformation contains a property named TranslationFile which allows the specification of a file name and location. This property is defined by the following line:

You’ll see a little further down what this file is used for.

That’s it. I’m quite sure you agree that these few lines of code are not very hard to write. And yet that’s all you have to do to create a fully functional RDCE.

Now that the framework is ready to use, let’s start with a simple implementation of reports in several languages.

Applying the framework for developing multi-language reports

With the framework on hand we can now develop a simple transformation that allows the implementation of reports in several languages. Our goal is to have one deployed report that is only translated into a target language at the time it is executed (rendered). This task can easily be performed by a transformation step that exchanges texts inside a report definition. Static texts are stored inside TextBox elements, so all we have to do is to replace textbox texts with their translated version. For this assignment we need only one transformation step that firstly reads the original text, then finds the translation and finally replaces the original text with the translation. Does that sound hard? No? Well, it isn’t.

To be precise, texts reside in the <Value> element underneath a <TextRun> element. A typical part of the report definition looks like this:

All we have to do is:

• Find all texts inside TextRun/Value elements.
• Look up the translated version of the text.
• Replace the original version with the translation.

Here’s the simplified code of the ITransformation.Transform() implementation:

The above code uses LINQ to XML to replace all occurrences of static texts inside TextBox elements with their translations. The code does not perform the translation itself. It just iterates through all texts and relies on a call of the function Translate() for the lookup of a translated version of each text.

For the Translate() function, you can have any implementation that you prefer. The sample code that is attached to this article uses a simple text file with one line for each term in different languages, where the first line defines the existing languages. Here’s a sample with three terms in three different languages ([GE]rman, [EN]glish, [RU]ssian):

The location of this file is provided in the configuration section and the sample Translate() function uses this file for the look-up of a term in a particular language. This file is also included in the solution that is attached to this article. Be sure to specify the correct location of the file in the configuration for your experiments (in the example configuration above this location is c:\temp\TranslationTable.txt). Of course you may also have a more sophisticated Translate() function that looks up translated versions in a database table, e.g.

Specification of the target language

If we want to see a report in a particular language, we need to specify this language. In other words: the RDCE needs to know the target language. To my best knowledge, the easiest way to provide this information is by introducing a report parameter. The code above uses a parameter named l__reportLanguage for this task. To be certain that all reports define this parameter, you can develop a report template. This is just a report as any other that – in a default VS 2010 installation – is stored inside the path C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

If you store your template (which actually is no more than a partially defined report) in this location, the template is than included in the list of templates and can be used for report development as any other default template:

You can choose plain text or ComboBox for the parameter type according to your requirements. If you do not want to allow the user to make any choice, you can even hide the parameter and take care that its value is provided over the URL when the report should be rendered. All that’s important is that the parameter has a value whenever the report is rendered. And there’s one more very important thing: the RDCE has access to this parameter and its value only and only if the parameter is declared as query parameter. More on this a little further down.

The sample solution that is provided in the source code of this article contains a report definition List of databases.rdl. You may use this report definition for your experiments.

Deployment

Before any report can make use of a RDCE, you must arrange two important deployment steps:

1. The SSRS engine needs to know about the existence of the RDCE.
2. Any report that should rely on the RDCE must be configured to do so.

Particularly the second point can turn out to be a bit tricky, so in this section I show you, what actions are necessary.

Deploying the RDCE

It requires several steps to deploy your RDCE. You should start by simply copying the RDCE assembly (DLL) into the Report Server bin-directory. For a default installation and a standard instance of SQL Server 2012, you can find this directory under C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin. Please do not forget to also copy referenced assemblies that are not part of the global assembly cache. In particular this should regard those assemblies that are required for logging. Our sample project uses NLog, so don’t forget also to copy Nlog.dll into the SSRS bin-directory.

After the files have been successfully copied, you have to modify your Report Server configuration. This is done by editing three configuration files in a way that is explained in the following section.

Altering SSRS configuration

All in all, there are three configuration files that have to be adapted. All of the three configuration files are located in the directory C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer (again: for a default installation and a standard instance. Your path may vary).

• rsreportserver.config. This file holds the configuration for the SSRS instance. We have to modify this configuration in order to inform the SSRS instance about the existence of our RDCE and also to allow SSRS-RDCE incorporation in general.
• rssrvpolicy.config. This configuration file covers security settings for the SSRS instance. Those settings have to be adapted, to let the SSRS instance know that it’s safe to execute the code inside our RDCE assembly.
• web.config. Modifications in this file are also concerned with security. This part regards the Report Server web site in general. Additionally changes are necessary for the configuration of logging.

Adjusting rsreportserver.config

A few lines above, where we have discussed the SetConfiguration() function, you have already seen the first necessary modification of the reportserver.config file. You have to include a <ReportDefinitionCustomization/> section inside of the element <Extensions/> as shown above. This configuration is processed by the SetConfiguration() function which is part of the IExtension interface. Please remember that you don’t have to care about calling the SetConfiguration() function yourself, as this is ensured by the SSRS engine.

One additional modification is required in order to allow RDCE processing in general. Below the <Service/> element you have to globally enable RDCE processing for the SSRS instance:

Please do not overlook this simple adjustment! If this element is not defined, the SSRS engine will silently ignore all successfully deployed and configured RDCEs. This can be a great source of sleepless nights and cruel headaches!

Modifying rssrvpolicy.config

Modifications in this file are to inform the SSRS engine that it’s safe to execute the code inside our RDCE. In order to do so, we need to include a CodeGroup for the assembly, like so:

Please be sure to adequately modify the path and name of our RDCE.

Changing web.config

At this point it all gets a little vague. In order to get my RDCE working as required, I had to adjust security settings for the Report Server web site. I accomplished this task by setting the trust level to full:

I’m quite sure that this is a bit inflated. It’s probably also possible to modify the trust level named “RosettaSrv”. The original trust level configuration looked like this:

As I didn’t know how to tune the “RosettaSrv” trust level in a way that allowed my RDCE to operate appropriately, I’ve just set the trust level to full instead. In the future I will maybe try to refine this detail but for now, I’ve deferred this refinement.

Also in the web.config I’ve added a section that organized logging. For this purpose, I’ve inserted a section like the following underneath the <configuration/> element:

You may want to adapt this configuration according to your requirements. If so, please have a look into the documentation of the logging framework (NLog in my example).

That’s all for configuration changes. For your convenience, in the sample project you’ll find the file Configurations.xml which summarizes all necessary modifications. You can simply copy and paste configuration sections from this file.

Please be aware that you may have to re-start the SSRS instance before the changes take effect.

Modifying deployed reports

For any report that is intended to make use of the RDCE, the report definition has to be adjusted. Two modifications are necessary:

• All parameters that should be interpreted inside the RDCE must be part of the QueryParameters collection of the report context (IReportContext) that is provided to the RDCE. To be enlisted in this collection, the parameter must have the UsedInQuery property set to true. This property is automatically set during report deployment and cannot be configured manually. If you have to have access to a parameter that is not used in a query (as is the case with our language parameter), this value must be set to true after report deployment. Otherwise there’s no way to query this parameter’s value inside the RDCE.
• Tell a report that it should use a particular RDCE, or in other words: “connect” a report to the RDCE. Any deployed report will not automatically use existing RDCEs. It is mandatory to fit a report definition, so the report can benefit from a RDCE.

At this point it gets a little weird, because there’s no way to modify a report definition in this respect during the development of the report in Visual Studio. Visual Studio (2010, for 2012 I don’t know) simply doesn’t support this. Therefore, you have to alter the report after you’ve deployed it; In other words, you have to modify the report definition directly on the Report Server.

As far as I know, the only documented way to place these two modifications is by calling the Report Server Management Web Service. So usually, you’ll need a deployment tool for this task.

I have found a more quick-and-dirty way to accomplish this mission by modifying the report definition directly in the report server database. The Catalog table holds one line for every report and this line covers any information that we need to initiate the two modifications. It is the columns Parameter and Property that need to be justified. These columns carry parameter information and additional report properties in an XML format.

In the Catalog table, a report is defined by the column ItemID. This column is of the type GUID and forms the primary key for the table. Our first step consists of finding the ItemID and also the values of the two columns Parameter and Property:

With this information at hand we can now update the values for the columns Property and Parameter. Simply take the returned values for these two columns and insert the required parts. An update statement then looks similar to this:

For better readability the above statement has been abbreviated; but the important parts are highlighted. Inside the <Properties/>element be sure to specify the name of your RDCE. That’s exactly the name returned by the IExtension.LocalizedName() function that has to be part of your interface implementation.

I have discovered that the two modifications do not have to be repeated after every report deployment. In my tests, I only needed to make the adaptations once. Subsequent deployments did not overwrite these again. (But you’d better not rely on this statement…)

Although this procedure works, be warned that it’s undocumented and therefore may not work in future releases.

Limitations

If you are fascinated about RDCE capabilities, and want to try out some of the possibilities by yourself, please don’t start before you have read this section. There’s a chance that your enthusiasm will be dampened a bit as soon as you know some of the core limitations.

First, any report that uses a RDCE cannot have subscriptions. That’s just a fact we have to accept.

Secondly, not all parts of a report definition may be modified. Only the following sections can be adjusted:

• Report body
• Page header
• Page footer
• The page itself
• Data sets

In particular, it is not possible to modify data sources. You can, however, modify data sets, to point to a different (already existing) data source. But keep in mind that the data source itself can’t be altered.

Third, parameter prompts can’t be changed. This is especially annoying for multi-language reports. Parameter presentation is not part of the report rendering process, as parameters are already displayed before the report is actually executed (rendered). Therefore, it isn’t possible to have translated versions of parameter prompts. Although your RDCE can modify parameter prompts, the modified version simply won’t show up. This may or may not be important for you. The default parameter presentation is very simple (not to say ugly), so usually, when developing a report portal, you won’t rely on the default parameter appearance but implement your own parameter control instead. In this case you have full control about parameter presentation and may also display parameter prompts in any language you like.

And finally, the installation of service packs may fail. On deployment, you have to modify the reportserver.config file. Also, the folder contents of the SSRS instance are altered. There’s a chance that this modifications will get in the way of successfully installing a service pack or cumulative update. So far, I have not experienced this in practice but be warned that problems may arise on product updates or maintenance.

Example: List of databases

For your convenience, in the sample solution you’ll find a Reporting Services project with one sample report, named List of databases. This report contains only one table that reveals database names, compatibility levels and collations of databases that reside on a server. You can use this report for your own experiments. Here are some hints to obey:

• Deploy the report to a Report Server where all necessary modifications for RDCE usage have been made.
• Modify this report’s Parameter and Property values as explained in the previous section.
• Modify the Data Source, so it links to a server of your choice. The configured user needs appropriate rights for execution of SELECT * from sys.databases.
• Don’t forget to copy the file TranslationTable.txt to the directory that is specified in the configuration section <ReportDefinitionCustomization/>.

After you have accomplished all of the above steps, you should be able to create this report in different languages with respect to columns headers of the contained table.

Here’s the English version:

And here’s what the Russian version looks like this:

Feel free to start your own experiments by adding other languages to the report parameter and the file TranslationTable.txt.

Summary

In this article I have shown what a Report Definition Customization Extension (RDCE) is good for and how an extension like this can be used to modify report definitions on the fly. We have built a simple RDCE framework that allows for a wide variety of modifications of report definitions. After introducing the framework we have used it for a translation of report definitions at rendering time.

I have also revealed some of those things that you need to keep in mind on RDCE deployment and how report definitions have to be adapted to make use of a successfully deployed RDCE.