Table of contents

Introduction

The problem
The proposed solution overview

Background
Preparing the Shared Data Sources (Report Server side)
Preparing the reports (Sample Reports project)
The RSExplorer++ tool

Configuring the RSExplorer++ tool
Using the RSExplorer++ tool
How does the RSExplorer++ tool work?

The RDCE

Understanding RDL files
Creating the RDCE
Enabling the Report Server to recognize and implement the RDCE
Debugging the RDCE

The Report Viewer Web Application

Inputs
Outputs

Special consideration #1: Reports with no parameters or Data Source Independent Parameters

Actions needed

Special consideration #2: Reports with one Data Source Dependent Parameter

Why is this important?
How is this solved?

Special consideration #3: Reports with drill-down Data Source Dependent Parameters

Why is this important?
How is this solved?

Conclusions
References

1. Introduction

Have you chosen Microsoft SQL Reporting Services as your reporting strategy? Are you using Shared Data Sources instead of embedded ones in your RDL files? Do you have multiple environments (Production, Quality Assurance, Development, etc.)? Would you like to be able to only deploy an RDL file once and for the Report Server to figure out what environment to point to?

If you answered yes to all of the questions, this article is for you.

This article may seem long, but I have tried to make it as easy to understand as possible and once you go through it, you will see that once you get used to the process, it becomes simple and straightforward.

1.1. The problem

Currently, to change a report data source at runtime, the report would need to use a client-side processing model (RDLC file). If we want to achieve this using a server-side processing model (RDL files deployed to a Reporting Server), there is no solution until today.

The conventional approach is shown on the following diagram:

Conventional Reporting Server Approach

Figure 1. Conventional reporting server approach

1.2. The proposed solution overview

Microsoft's solution to this problem is the introduction of "Expression-based Connection Strings" (see this article for reference) which unfortunately does not apply to shared data sources.

Fortunately, by using a tool, some report design standards, and a Report Definition Customization Extension (RDCE), it is possible to change the Data Source References on the RDL file to point to the right Server-Database pair.

The overview of the solution is modeled on the following diagram:

Proposed Solution Overview

Figure 2. Proposed solution overview

2. Background

To be able to understand this article, you need:

Intermediate knowledge on SQL Server Reporting Services.
Knowledge on the difference between "Embedded" and "Shared" Data Sources (you can consult this article for further information: Embedded and Shared Data Connections or Data Sources (Report Builder 3.0 and SSRS)).
Basic knowledge on the concept of Reporting Services Extensions (you can consult this article for further information: Reporting Services Extensions).

Note: The databases used throughout the article are based on the AdventureWorks database (you can download it from here).

I will only use two databases (but simulate that we have four):

"AdventureWorksDW" which will be DEVELOPMENT and TEST, and
"AdventureWorksDW2008" which will be PRODUCTION and QUALITY_ASSURANCE.
I altered the data in the DEVELOPMENT database to see the difference when pointing to PRODUCTION or DEVELOPMENT.

An example of the data in PRODUCTION is as follows:

PRODUCTION DimEmployee Table Contents

Figure 3. The DimEmployee table contents in PRODUCTION

An example of the data in DEVELOPMENT is as follows (note that I added "Other Data Source" or "ODS" to some columns to make it obvious when we are pointing to it):

DEVELOPMENT DimEmployee Table Contents

Figure 4. The DimEmployee table contents in DEVELOPMENT

3. Preparing the Shared Data Sources (Report Server side)

To connect to your Report Server by using a Web Browser, use http://<your_report_server>/Reports. For this article, we will have two folders at the Home level:

Data Sources, which will contain the Shared Data Sources definitions.
Reports, which will contain the deployed Reports (RDL files).

The Report Server Environment

Figure 5. The Report Server environment

As this article describes them: "A shared data source specifies connection properties for a data source. If you have a data source that is used by a large number of reports, models, or data-driven subscriptions, consider creating a shared data source to eliminate the overhead of having to maintain the same connection information in multiple places."

To create and manage shared data sources, please refer to the article: Creating, Modifying, and Deleting Shared Data Sources (SSRS).

Make sure your Report Server contains all the Shared Data Sources you need. For the article, I will use:

Data Sources/DEVELOPMENT
Data Sources/QUALITY_ASSURANCE
Data Sources/TEST
Data Sources/PRODUCTION

Data Sources at the Report Server

Figure 6. Data Sources at the Report Server

Please make sure that all the data sources are configured correctly according to your environment. The following image shows the configuration for the DEVELOPMENT database that will be used throughout the article:

Data Sources Contents

Figure 7. DEVELOPMENT Data Source contents

4. Preparing the reports (Sample Reports project)

You can use either the "Business Intelligence Development Studio" or the "Report Builder" to design and deploy the reports. For an overview of the "Business Intelligence Development Studio", you can read this article: Reporting Services in Business Intelligence Development Studio (SSRS). For an overview of the "Report Builder", you can read this article: Getting Started with Report Builder 3.0. I will be using the "Business Intelligence Development Studio" on this section.

The following applies to the Samples Report Project:

Please check that the Project Properties are configured correctly according to your environment.

TargetDataSourceFolder = Data Sources (to be compliant with Figure 5)
TargetReportFolder = Reports (to be compliant with Figure 5)
TragetServerURL = http://<your_report_server>/reportserver

Sample Reports Project Properties

Figure 8. Sample Reports project properties

Add the Shared Data Sources that you have on your Report Server (I will use the four on Figure 6):

DEVELOPMENT
QUALITY_ASSURANCE
TEST
PRODUCTION

Shared Data Sources on the Sample Reports Project

Figure 9. Shared Data Sources on the Sample Reports project

Make sure that each Data Source is configured properly. In the following image, I'm showing the settings that should match those of figure 7.

Shared Data Source Properties 1

Figure 10. Shared Data Source general properties

Shared Data Source Properties 2

Figure 11. Shared Data Source general credentials properties

The project contains three reports. I will focus on "NamesReport.rdl" in this section, even though all of them follow the same conventions.

Reports on the Sample Reports Project

Figure 12. Reports on the Sample Reports project

The Design view of "NamesReport.rdl" looks like the following:

Design view on NamesReport.rdl

Figure 13. Design view on NamesReport.rdl

To prepare the reports for action, they will need to have references to the available shared data sources. To achieve this, open the Report Data window (press "Ctrl + Alt + D" or go to "View - Report Data" on Visual Studio [Business Intelligence Development Studio]). You should see the following window:

The Report Data Window

Figure 14. The Report Data window

On the Data Sources folder, you should have the exact same Data Sources you have at the project level:

DEVELOPMENT
QUALITY_ASSURANCE
TEST
PRODUCTION

Data Sources at the Report Level

Figure 15. Data Sources at the Report level

Each Data Source properties should be pointing to the shared data source at the Report Project level:

Data Source at the Report Level Properties

Figure 16. Data Source at the Report level general properties

Now let's look at the Data Sets at the Report level:

Data Sets at the Report Level

Figure 17. Datasets at the Report level

Each DataSet's properties has to be configured to point to the PRODUCTION data source as follows:

Data Sets Properties at the Report Level

Figure 18. DataSet query properties at the Report level

If we preview the report, it should look like the following pointing to PRODUCTION data:

Report Preview

Figure 19. NamesReport report preview

Deploy the report(s) [Right click on the report - Deploy].
The same report should now be visible at the server (it points to the PRODUCTION data):

Report at the Server Level

Figure 20. Report at the Server level

To summarize, the important steps needed are:

The Report project needs to have all the Shared Data Sources that are at the Report Server.
Each report needs to have all the Shared Data Source references to the Shared Data Sources that are at the Report Server.
Each report needs to have all the DataSets pointing to the PRODUCTION Data Source.

5. The RSExplorer++ Tool

This tool was built on top of the "RSExplorer Sample Application" from the Microsoft Reporting Services Product Samples. You can download the original tool here: RSExplorer Sample Application. Kindly note that this tool falls into the Microsoft Public License (Ms-PL).

You can download the source code and the installer of the RSExplorer++ tool (used in this section) at the top of the page.

The original Microsoft RSExplorer sample application allows you to:

Browse your Report Server
Preview your reports
View an item's properties

The RSExplorer++ tool, in addition, will allow you to:

RDCE Enable/Disable your reports (to be explained shortly).
Add the Data Source parameter to your reports (to be explained shortly).
Check if your reports are RDCE enabled and have the Data Source parameter (to be explained shortly).

5.1. Configuring the RSExplorer++ tool

On the App.config file, make sure the MSQLSRS_ConnectionString connection string has direct access to your ReportServer database as follows:

<connectionStrings>
    <add name="MSQLSRS_ConnectionString" 
      connectionString="Data Source=(local);Initial Catalog=ReportServer;User ID=UserReader;Password=XXXXXXX" 
      providerName="System.Data.SqlClient" /> 
</connectionStrings>

Code Sample 1. [XML] The RSExplorer++ tool Web.config "connectionStrings" section.

5.2. Using the RSExplorer++ tool

Type your report server address at the "Server Address" text box as follows: http://<your_report_server>/reportserver and click on Go. You should see the folders at your root level.

Reports Explorer first screen

Figure 21. The RSExplorer++ tool first screen

Click on the Reports folder and you should see the three reports deployed on section 4 of the article. The three reports will initially have "RDCE Enabled" set to False, "DS Parameter" set to N/A, and "Used In Query" set to N/A. Note: To be able to change the data source at runtime, those three values need to be set to true. The next steps will indicate how to achieve that.

Reports Explorer at the Reports Level

Figure 22. The RSExplorer++ tool at the Reports level

If you double-click on a report, for example the first one, you will be able to preview it.

Report Preview from the RSExplorer++ tool

Figure 23. Report Preview from the RSExplorer++ tool

If you select a report and click on "Show Properties" on the top right part of the screen, the box on the right side will fill up with report properties information.

Report Properties from the RSExplorer++ tool

Figure 24. Report Properties from the RSExplorer++ tool

If you select a report and click on "Enable RDCE" on the top right part of the screen, the record for that report should change its color to Orange, the "RDCE Enabled" value should change to True, and the "DS Parameter" value should change to False. This now means that this particular report will call the RDCE before rendering.

Enabling RDCE on a report from the RSExplorer++ tool

Figure 25. Enabling RDCE on a report from the RSExplorer++ tool

If you select the report and click on "Show Properties" on the top right part of the screen again, you will see that a new property called RDCE with "RDCE" as its value was created. See the "How does the RSExplorer++ tool work?" section for details on the code-behind that achieves this.

The RDCE property on a report from the RSExplorer++ tool

Figure 26. The RDCE property on a report from the RSExplorer++ tool

If you double-click on the report, you will not notice anything, but in fact, the RDCE is now being called to action. Note: See "The RDCE" section for information about this:
If you select the report and click on "Add DS Parameter/Use In Query" on the top right part of the screen, two actions that should be part of a transaction will be executed:

A Report Parameter will be added to the RDL (Report Definition) file. This parameter will be used to determine to which Data Source the report needs to point.
The Report Server should be notified that this new parameter is "Used In Query" so that the RDCE is able to see the value. This value is set to True at the report level on the report designer tool if you are using the parameter at a Data Set. To avoid doing this, I do a trick to tell the server that the parameter is being "Used In Query" even if theoretically is not. See the "How does the RSExplorer++ tool work?" section for details on the code-behind that achieves this.

If the Report Parameter was added but the "Used In Query" action was not performed, this means that the connection string specified at the "Configuring the RSExplorer++ tool" section is wrong and you will get a red highlighted record. Fix the connection string and click on "Add DS Parameter/Use In Query" again.

'Add DS Parameter/Use In Query' transaction failure from the RSExplorer++ tool

Figure 27. 'Add DS Parameter/Use In Query' transaction failure from the RSExplorer++ tool

If the transaction was successful, the report record should be green. This means that the report is fully ready to dynamically point to a given Data Source.

'Add DS Parameter/Use In Query' transaction successful from the RSExplorer++ tool

Figure 28. 'Add DS Parameter/Use In Query' transaction successful from the RSExplorer++ tool

You can double-click on the report now to test that it points to the right data source by writing the Data Source name on the parameter and pressing "View Report". NOTE: These tests will only work if you already configured your Report Server to enable the RDCE to work. See section 6 "The RDCE" for further reference.

DEVELOPMENT
QUALITY_ASSURANCE
TEST
PRODUCTION

Testing a report from the RSExplorer++ tool pointing to DEVELOPMENT

Figure 29. Testing a report from the RSExplorer++ tool pointing to DEVELOPMENT

Note that text was added on the Top Right corner of the report to indicate which Data Source the report is pointing to. Due to the changes done to the "DEVELOPMENT" data base on Section 2 of this article, we can see that the data is different than the PRODUCTION report.

Testing a report from the RSExplorer++ tool pointing to PRODUCTION

Figure 30. Testing a report from the RSExplorer++ tool pointing to PRODUCTION

To summarize, the important steps needed:

The report needs to be RDCE Enabled.
The report needs to have the Data Source parameter.
The report needs to have the Data Source parameter "Used In Query" property set to true.

5.3. How does the RSExplorer++ tool work?

Checking if the report is RDCE enabled: (This is implemented to set the "RDCE Enabled" attribute on the RSExplorer++ tool to True or False)

Microsoft.SqlServer.ReportingServices2010.Property[] props = 
   new Microsoft.SqlServer.ReportingServices2010.Property[1];
Microsoft.SqlServer.ReportingServices2010.Property setProp = 
   new Microsoft.SqlServer.ReportingServices2010.Property();
setProp.Name = "RDCE";
setProp.Value = "RDCE";
props[0] = setProp;

Microsoft.SqlServer.ReportingServices2010.Property[] current_props = 
          rs.GetProperties("Reports/NamesReport", props);
if (current_props.Length == 0)
{
    //The Report is not RDCE Enabled
    //RDCE Enabled = False
}
else
{
    //The Report is RDCE Enabled
    //RDCE Enabled = True
}

Code Sample 2. [C#] Checking if the report is RDCE enabled

Checking if the report has the Data Source parameter and if it is "Used In Query": (This is implemented to set the "DS Parameter" and "Used In Query" attributes on the RSExplorer++ tool to True or False)

bool forRendering = false;
string historyID = null;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ItemParameter[] parameters = null;
parameters = rs.GetItemParameters("/Reports/NamesReport", 
                   historyID, forRendering, values, credentials);

bool hasParameter = false;
bool usedInQuery = false;
foreach (ItemParameter ip in parameters)
{
    //RDCE_Report_Data_Source is the standardized name of the Data Source parameter
    if (ip.Name == "RDCE_Report_Data_Source")
    {
        //DS Parameter = True
        hasParameter = true;
        if (ip.QueryParameter == true)
        {
            //Used In Query = True
            usedInQuery = true;
        }
    }
}

Code Sample 3. [C#] Checking if the report has the Data Source parameter and if it is "Used In Query"

Enabling RDCE on a report:

Microsoft.SqlServer.ReportingServices2010.Property[] props = 
       new Microsoft.SqlServer.ReportingServices2010.Property[1];
Microsoft.SqlServer.ReportingServices2010.Property setProp = 
       new Microsoft.SqlServer.ReportingServices2010.Property();
setProp.Name = "RDCE";
setProp.Value = "RDCE";
props[0] = setProp;

Microsoft.SqlServer.ReportingServices2010.Property[] current_props = 
          rs.GetProperties("/Reports/NamesReport", props);
rs.SetProperties(selItem.Path, props);

Code Sample 4. [C#] Enabling RDCE on a report

Disabling RDCE on a report:

Microsoft.SqlServer.ReportingServices2010.Property[] props = 
         new Microsoft.SqlServer.ReportingServices2010.Property[1];
Microsoft.SqlServer.ReportingServices2010.Property setProp = 
         new Microsoft.SqlServer.ReportingServices2010.Property();
setProp.Name = "RDCE";
setProp.Value = "";
props[0] = setProp;

Microsoft.SqlServer.ReportingServices2010.Property[] current_props = 
          rs.GetProperties("/Reports/NamesReport", props);
rs.SetProperties(selItem.Path, props);

Code sample 5. [C#] Disabling RDCE on a report

Adding the "RDCE_Report_Data_Source" parameter:

byte[] reportDefinitionProcessed;

MemoryStream mstream = null;
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();

//Get RDL from Report Server
using (mstream = new MemoryStream(rs.GetItemDefinition("/Reports/NamesReport")))
{
    doc.Load(mstream);
    mstream.Position = 0;
}

//Load RDL to a XElement
XElement xreport = XElement.Load(new XmlNodeReader(doc));

string dns = "{" + xreport.GetDefaultNamespace() + "}";

//Prepare the parameter
var entry = new XElement(dns + "ReportParameter");
entry.SetAttributeValue("Name", "RDCE_Report_Data_Source");
entry.Add(new XElement(dns + "DataType", "String"));
entry.Add(new XElement(dns + "Prompt", "Report Server:"));

//Check if report already has parameters
bool hasParameters = xreport.Element(dns + "ReportParameters") != null;

if (!hasParameters)
{
    //If it doesn't have parameters, add the "ReportParameters" element
    XElement parent = new XElement(dns + "ReportParameters");
    parent.Add(entry);

    xreport.Element(dns + "DataSets").AddAfterSelf(parent);
}
else
{
    //If it already has parameters, add as first parameter
    xreport.Element(dns + "ReportParameters").AddFirst(entry);
}

System.Text.Encoding encoding = new System.Text.UTF8Encoding();
reportDefinitionProcessed = encoding.GetBytes(xreport.ToString());

//Save new RDL to the Report Server
rs.SetItemDefinition("/Reports/NamesReport", reportDefinitionProcessed, null);

Code Sample 6. [C#] Adding the "RDCE_Report_Data_Source" parameter

Setting the parameter "Used In Query" to true: This is where it gets tricky because it is not at the RDL level that the Report Server knows if a parameter is Used In Query or not. If you run the following SQL statement against your Report Server after adding the RDCE_Report_Data_Source parameter but before setting the "Used In Query" to true, you will get the result below.

SELECT [Parameter]
  FROM [ReportServer].[dbo].[Catalog]
  WHERE [Path] = '/Reports/NamesReport'

Code Sample 7. [SQL] Query to checking if a report is "Used In Query"

<Parameters>
  <UserProfileState>0</UserProfileState>
  <Parameter>
    <Name>RDCE_Report_Data_Source</Name>
    <Type>String</Type>
    <Nullable>False</Nullable>
    <AllowBlank>False</AllowBlank>
    <MultiValue>False</MultiValue>
    <UsedInQuery>False</UsedInQuery>
    <State>MissingValidValue</State>
    <Prompt>Report Server:</Prompt>
    <DynamicPrompt>False</DynamicPrompt>
    <PromptUser>True</PromptUser>
  </Parameter>
</Parameters>

Code Sample 8. [XML] Checking if a report is "Used In Query"

The objective is to change the "UsedInQuery" line directly to the database to True. This is achieved by the following:

string xml_parameters = "";

SqlConnection conn = new SqlConnection(
  ConfigurationManager.ConnectionStrings["MSQLSRS_ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand(string.Format("SELECT [Parameter]  " + 
   "FROM [dbo].[Catalog]  WHERE [Path] = '{0}'", "/Reports/NamesReport"), conn);
command.CommandType = CommandType.Text;

conn.Open();
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
    //Get current paramaters XML
    xml_parameters = reader["Parameter"].ToString();
}

reader.Close();
conn.Close();

MemoryStream mstream = null;
byte[] ascii = System.Text.Encoding.UTF8.GetBytes(xml_parameters);
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
using (mstream = new MemoryStream(ascii))
{
    doc.Load(mstream);
    mstream.Position = 0;
}

XElement xparameters = XElement.Load(new XmlNodeReader(doc));

var dsds = xparameters.Elements("Parameter");
foreach (XElement xe in dsds)
{
    if (xe.Element("Name").Value == "RDCE_Report_Data_Source")
    {
        //This is where the UsedInQuery element is changed to True
        XElement temp = xe.Element("UsedInQuery");
        temp.Value = "True";
    }
}

string new_parameters = xparameters.ToString();

conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSQLSRS_ConnectionString"].ConnectionString);
command = new SqlCommand(
    string.Format("UPDATE [dbo].[Catalog]   SET [Parameter] = '{0}'      " + 
           "WHERE [Path] = '{1}'", new_parameters, "/Reports/NamesReport"), conn);
command.CommandType = CommandType.Text;

conn.Open();

//Changes are written to the database
reader = command.ExecuteReader();

while (reader.Read())
{
}

reader.Close();
conn.Close();

Code Sample 9. [C#] Changing the "UsedInQuery" line directly to the database to True

This summarizes what is done "behind the scenes" by the RSExplorer++ tool. The following section will explain in detail why we need to do all these steps.

6. The RDCE

A Report Definition Customization Extension (RDCE) is a Report Server extensibility feature introduced on SQL Server 2008. It allows us to customize the Report Definition RDL on runtime without actually writing the new RDL back to the server. It basically gets the RDL from the server, customizes it and then sends the customized version to the Report Viewer.

6.1. Understanding RDL files

The Report Definition Language (RDL) is an XML-based file that represents the metadata for defining a Report Server report. The following article gives us an overview of the RDL schema: Report Definition Overview Diagrams. We can appreciate some of the elements of an RDL on the code-behind one of our reports from Section 4:

Body
Page
DataSources
DataSets
ReportParameters

An example of an RDL

Figure 31. An example of an RDL

A Report Definition Customization Extension allows you to customize ONLY the following elements of a report definition:

Body
DataSets
Page
PageFooter
PageHeader

Customizable Elements of an RDL file

Figure 32. Customizable Elements of an RDL file

This means that the following elements cannot be customized during runtime:

DataSources
ReportParameters

Non-Customizable Elements of an RDL file

Figure 33. Non-Customizable Elements of an RDL file

This translates to the fact that we cannot add report parameters on runtime (which makes sense because these would need to load before the report itself and determine the data we are getting from the report). This also means that we cannot modify DataSources. That is the key point here! If we were able to modify the Data Source we would just change the "DataSourceReference" node of the DataSource to point to the one we want. Based on this, we need to add all the DataSources to the report during report design (see Section 4) and let the RDCE change the DataSource that the DataSets are pointing to.

Let's take a look at the DataSources element of one of our reports:

<DataSources>
    <DataSource Name="DEVELOPMENT">
      <DataSourceReference>DEVELOPMENT</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>XXXX-YYYY-ZZZZ</rd:DataSourceID>
    </DataSource>
    <DataSource Name="PRODUCTION">
      <DataSourceReference>PRODUCTION</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>AAAA-YYYY-ZZZZ</rd:DataSourceID>
    </DataSource>
    <DataSource Name="QUALITY_ASSURANCE">
      <DataSourceReference>QUALITY_ASSURANCE</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>WWWWW-YYYY-ZZZZ</rd:DataSourceID>
    </DataSource>
    <DataSource Name="TEST">
      <DataSourceReference>TEST</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>MMMMMM-YYYY-ZZZZ</rd:DataSourceID>
    </DataSource>
</DataSources>

Code Sample 10. [XML] DataSources element of an RDL file

Now let's take a look at the DataSets element of the same report:

<DataSets>
    <DataSet Name="ResultsDataSet">
      <Query>
        <DataSourceName>PRODUCTION</DataSourceName>
        <CommandText>SELECT      DISTINCT  FirstName, LastName
        FROM            DimEmployee
        ORDER BY FirstName, LastName
    </CommandText>
      </Query>
      <Fields>
        <Field Name="FirstName">
          <DataField>FirstName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="LastName">
          <DataField>LastName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
</DataSets>

Code Sample 11. [XML] DataSets element of an RDL file

Since we cannot do anything on the DataSources elements, the RDCE will change the DataSourceName element on each of the DataSets to point to a different DataSource. It is important that the DataSets point to PRODUCTION by default, because that's the DataSourceName that will be replaced.

Note: The DataSource to point to MUST exist on the report definition. This means that this report can point on runtime ONLY to one of the following DataSources:

DEVELOPMENT
QUALITY_ASSURANCE
TEST
PRODUCTION

6.2. Creating the RDCE

The RDCE is a Visual Studio project that will produce a Class Library (DLL). We will name the assembly "rs.rdce" and the default namespace "RS.Extensibility". Click on Project - RDCE Properties to configure that if needed:

RDCE Project Properties

Figure 34. RDCE Project Properties

The Build Events Post-build event command line should be preferably configured to point to your Reporting Services bin folder. This will place the DLL file in the right place after building it and will allow you to debug.

RDCE Project Properties Build Events

Figure 35. RDCE Project Properties Build Events

The command should be the following (check that the path to the Reporting Services bin is correct on your environment):

copy "$(TargetDir)$(TargetName).*" 
   "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin"

Code Sample 12. [Script] Build Events Command

The project needs to have a reference to the Microsoft.ReportingServices.Interfaces wich can be found at "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies".

RDCE Solution Explorer

Figure 36. RDCE Solution Explorer

As you can see, the project has only one class ReportDefinitionCustomizationExtension which implements the IReportDefinitionCustomizationExtension interface found at Microsoft.ReportingServices.Interfaces. This interface exposes the ProcessReportDefinition method which we will focus on. The detail on the parameters and the return value of this method can be found at this article: IReportDefinitionCustomizationExtension.ProcessReportDefinition Method.

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

Code Sample 13. [C#] The ProcessReportDefinition method

As you can see, the method receives the original report definition (reportDefinition), the context of the report (reportContext) and the context of the user session (userContext). The method outputs are the customized report definition (reportDefinitionProcessed) and the collection of elements that were customized (customizedElementIds) which can be the following:

Body
DataSets
Page
PageFooter
PageHeader

The method will return true if the Report Definition was customized and false otherwise.

The reportContext parameter is essential to achieving our goal because it contains the list of QueryParameters and their values. This is why it becomes extremely important for the RSExplorer++ tool to set the RDCE_Report_Data_Source's "Used In Query" attribute to True, because it enables us to get that value and use it to customize our report.

if (reportContext.QueryParameters.Count == 0)
{
    //The report has no "Used In Query" parameters
    //RDL is not customized
    return false; 
}

if (reportContext.QueryParameters["RDCE_Report_Data_Source"] == null)
{
    //The "RDCE_Report_Data_Source" parameter was not found
    //RDL is not customized
    return false; 
}

if (reportContext.QueryParameters["RDCE_Report_Data_Source"].Values.Length == 0)
{
    //The "RDCE_Report_Data_Source" was found but has no value
    //RDL is not customized
    return false; 
}

//The "RDCE_Report_Data_Source" was found and has a value
string ParamValue = reportContext.QueryParameters["RDCE_Report_Data_Source"].Values[0].ToString();

Code Sample 14. [C#] Checking the "RDCE_Report_Data_Source" parameter

Now that we know that the "RDCE_Report_Data_Source" parameter has a value, we can proceed to customize the report.

//We load the report definition to an XML document in memory and then to
//an XElement for easier manipulation
MemoryStream mstream = null;
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
using (mstream = new MemoryStream(reportDefinition))
{
    doc.Load(mstream);
    mstream.Position = 0;
}                

XElement xreport = XElement.Load(new XmlNodeReader(doc));

//We get the XML namespaces yo be able to work with them later on
string dns = "{" + xreport.GetDefaultNamespace() + "}";
string seconddns = "";

if (xreport.GetNamespaceOfPrefix("rd") != null)
{
    seconddns = "{" + xreport.GetNamespaceOfPrefix("rd") + "}";
}

Code Sample 15. [C#] First stage of customization

The "RDCE_Report_Data_Source" parameter value should be one of the following:

DEVELOPMENT
QUALITY_ASSURANCE
TEST
PRODUCTION

We need to check if the Data Source received as a parameter exists:

//Get the DataSources
var data_sources = xreport.Element(dns + "DataSources").Elements(dns + "DataSource");
//Go through the DataSources to see if the Shared Data Source we are trying to
//point to exists
bool dataSourceExists = false;
foreach (XElement xe_ds in data_sources)
{
    //The DataSources must have the DataSourceReference element for them to
    //be pointing to Shared Data Sources
    bool isSharedDataSource = xe_ds.Element(dns + "DataSourceReference") != null;
    if (isSharedDataSource)
    {
        if ((xe_ds.Element(dns + "DataSourceReference").Value).ToUpper() == ParamValue.ToUpper())
        {
            dataSourceExists = true;
        }
    }
}

if (!dataSourceExists)
{
    //The shared data source we are trying to point to does not exist
    //RDL is not customized
    return false; 
}

Code Sample 16. [C#] Checking if the data source we are trying to point to exists

Now we need to go through the Data Sets to point them to the Data Source we want:

//Get the DataSets
var data_sets_data_sources = xreport.Element(dns + "DataSets").Elements(dns + "DataSet");
//Go through the DataSets
foreach (XElement xe in data_sets_data_sources)
{
    //Check if the DataSet is pointing to the default data source to be replaced
    //defined at the beggining of the class:
    //private const string dataSourceToReplace = "PRODUCTION";
    if ((xe.Element(dns + "Query").Element(dns + 
         "DataSourceName").Value).ToUpper() == dataSourceToReplace.ToUpper())
    {
        //Change the data source to point to the one we want to point to
        //This is the key part of the algorithm!
        xe.Element(dns + "Query").Element(dns + "DataSourceName").Value = ParamValue.ToUpper();
        datasourcewaschanged = true;   
    } 
}

if (!datasourcewaschanged)
{
    //There are no data sources pointing to the default data source to be replaced
    //Nothing was changed
    //RDL is not customized
    return false;
}

Code Sample 17. [C#] Checking if the DataSet is pointing to the default data source to be replaced

The code block that follows within the "Special Consideration 3: Reports with drill-down Data Source Dependent Parameters" region will be explained on section 10 of this article.

To be able to acknowledge that the RDCE customized the report, the following code adds a text box to the report to indicate to which data source the report is now pointing to:

//Create a text box to be added to the report
//to indicate that the RDCE processed it
//and customized it
var entry = new XElement(dns + "Textbox");
entry.SetAttributeValue("Name", "tb_RDCE_DataSource");
entry.Add(new XElement(dns + "CanGrow", "true"));
entry.Add(new XElement(dns + "KeepTogether", "true"));

var paragraphs = new XElement(dns + "Paragraphs");                   

var paragraph = new XElement(dns + "Paragraph");

var textruns = new XElement(dns + "TextRuns");
var textrun = new XElement(dns + "TextRun");
textrun.Add(new XElement(dns + "Value", "Data Source: " + ParamValue.ToUpper()));

var style = new XElement(dns + "Style");
style.Add(new XElement(dns + "FontFamily", "Tahoma"));
style.Add(new XElement(dns + "FontSize", "6pt"));
style.Add(new XElement(dns + "Color", "SteelBlue"));

textrun.Add(style);
textruns.Add(textrun);
paragraph.Add(textruns);
paragraphs.Add(paragraph);

entry.Add(paragraphs);

entry.Add(new XElement(seconddns + "DefaultName", "tb_RDCE_DataSource"));
entry.Add(new XElement(dns + "Top", "0.03in"));
entry.Add(new XElement(dns + "Left", "4.84249in"));
entry.Add(new XElement(dns + "Height", "0.26056in"));
entry.Add(new XElement(dns + "Width", "1.52209in"));
entry.Add(new XElement(dns + "ZIndex", "4"));

var style3 = new XElement(dns + "Style");
var border = new XElement(dns + "Border");
border.Add(new XElement(dns + "Style", "None"));
style3.Add(border);

style3.Add(new XElement(dns + "PaddingLeft", "2pt"));
style3.Add(new XElement(dns + "PaddingRight", "2pt"));
style3.Add(new XElement(dns + "PaddingTop", "2pt"));
style3.Add(new XElement(dns + "PaddingBottom", "2pt"));

entry.Add(style3);

//Add data source text box to the report
xreport.Element(dns + "Body").Element(dns + "ReportItems").Add(entry);

Code Sample 18. [C#] Adding the data source text box to the report

The text box will look like the following:

Testing a report from the RSExplorer++ tool pointing to DEVELOPMENT

Figure 37. Testing a report from the RSExplorer++ tool pointing to DEVELOPMENT

Finally, we convert the XML to a byte array and we prepare the List of RdceCustomizableElementId to indicate the sections we customized:

Body
DataSets

//Add data source text box to the report
xreport.Element(dns + "Body").Element(dns + "ReportItems").Add(entry);                

//Convert our XML to a byte array to send as output
System.Text.Encoding encoding = new System.Text.UTF8Encoding();
reportDefinitionProcessed = encoding.GetBytes(xreport.ToString());

//Set up the list of elements we customized to let the Report Server know
List<RdceCustomizableElementId> ids = new List<RdceCustomizableElementId>();
ids.Add(RdceCustomizableElementId.DataSets);
ids.Add(RdceCustomizableElementId.Body);
customizedElementIds = ids;

//RDL is customized
return true;

Code Sample 19. [C#] Final stage of customization

6.3. Enabling the Report Server to recognize and implement the RDCE

Some steps need to be done at the Report Server level for it to recognize and use the Extension.

Enable the extension. Locate the rsreportserver.config file in your Report Server. You may find it at "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer". Add the IsRdceEnabled element with "True" as its value at the end of the "Services" element.

<Service>
    ...
    <IsRdceEnabled>True</IsRdceEnabled>
</Service>

Code Sample 20. [XML] Enabling the RDCE extension at the rsreportserver.config file

Register the extension. The Extensions element contains, among others, the Delivery, Render, Security and Authentication extensions. We need to register the RDCE at the end of the section as follows:

<Extensions>
    <Delivery>
    ...
    </Delivery>
    <DeliveryUI>
    ...
    </DeliveryUI>
    <Render>
    ...
    </Render>
    <Data>
    ...
    </Data>
    <SemanticQuery>
    ...
    </SemanticQuery>
    <ModelGeneration>
    ...
    </ModelGeneration>
    <Security>
    ...
    </Security>
    <Authentication>
    ...
    </Authentication>
    <EventProcessing>
    ...
    </EventProcessing>
    <ReportDefinitionCustomization>
        <Extension Name="RDCE"
            Type="RS.Extensibility.ReportDefinitionCustomizationExtension,rs.rdce">
            <Configuration>
                <RDCEConfiguration>
                </RDCEConfiguration>                
            </Configuration>
        </Extension>
    </ReportDefinitionCustomization>    
</Extensions>

Code Sample 21. [XML] Registering the RDCE extension at the rsreportserver.config file

Configure CAS (Code Access Security). Locate the rssrvpolicy.config file in your Report Server. You may find it at "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer". Insert the following Code Group to add FullTrust to our created assembly.

        ...
        <CodeGroup>
        ...
        </CodeGroup>
        <CodeGroup>
        ...
        </CodeGroup>
        <CodeGroup class="UnionCodeGroup" version="1" Name="RDCE"
        Description="Code group for the Report Definition Customization Extension"
        PermissionSetName="FullTrust">
            <IMembershipCondition class="UrlMembershipCondition" version="1"
            Url="C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin\rs.rdce.dll"/>
        </CodeGroup> 
    </CodeGroup>
</CodeGroup>

Code Sample 22. [XML] Configuring Code Access Security for the RDCE extension at the rssrvpolicy.config file

Place the DLL in the right place. The RDCE project places the DLL automatically on build at "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin\rs.rdce.dll". Check that the paths are correct and that the DLL is there.

6.4. Debugging the RDCE

You can easily check if the RDCE is running and modified the report by checking if the report has the "Data Source: PRODUCTION|DEVELOPMENT|..." text box in it. If you are not able to see this, check the following:

The report server has to have all the shared data sources.
The report project has to have all the shared data sources.
Every single report needs to have the data source that point to the report project data sources.
All the data sets in the report need to be pointing to a default data source (PRODUCTION).
The report needs to be RDCE Enabled (use the RSExplorer++ tool).
The report needs to have the RDCE_Report_Data_Source parameter (use the RSExplorer++ tool).
The report needs to have the RDCE_Report_Data_Source's "Used In Query" attribute set as True (use the RSExplorer++ tool).
The report server needs to have the DLL at the right place: "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin\rs.rdce.dll".
The rsreportserver.config and rssrvpolicy.config files need to be modified accordingly.
If all this is correct, you may try to debug the RDCE by attaching the process to the ReportingServicesService.exe [Debug - Attach to Process...].

Attaching the Debugger to the ReportingServicesService.exe process to Debug it

Figure 38. Attaching the Debugger to the ReportingServicesService.exe process to Debug it

7. The Report Viewer Web Application

This application is just to illustrate how the solution can be integrated to your current environment. Now that we know how everything works, we don't want to expose the RDCE_Report_Data_Source parameter to the end user. What this application mainly does is:

Get the report path and the data source to point to as Query String parameters.
Get any other parameter values as Query String parameters.
Feed the parameters to the report.
Hide the RDCE_Report_Data_Source to the end user.
Check if report is RDCE Enabled and add the RDCE_Report_Data_Source if needed before loading the report.
Set the RDCE_Report_Data_Source as "Used In Query" if needed before loading the report.
Handle "Special Consideration #3: Reports with drill-down Data Source Dependent Parameters" (explained at that section).

To learn more about how the ReportViewer control works, you can check this article: Adding and Configuring the ReportViewer Controls or this one: Configuring ReportViewer for Remote Processing.

7.1. Inputs

Considering that the application is running at "http://localhost/ReportViewerWebApplication", it should be accessed using the following syntax:

http://localhost/ReportViewerWebApplication/Default.aspx
?report_url=<Encoded_Report_URL>
&report_data_source=<DEVELOPMENT|PRODUCTION|QUALITY_ASSURANCE|TEST>

Code Sample 23. [URL] Calling the Report Viewer Web Application from a URL

An example of accessing the Names Report pointing to DEVELOPMENT would be:

http://localhost/ReportViewerWebApplication/Default.aspx
?report_url=http%3A%2F%2Flocalhost%2FReportServer%2FPages%2FReportViewer.aspx
%3F%252fReports%252fNamesReport%26rs%3ACommand%3DRender
&report_data_source=DEVELOPMENT

Code Sample 24. [URL] Example of calling the Report Viewer Web Application from a URL pointing to DEVELOPMENT

Kindly note that the report_url must be encoded. It went from: http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReports%2fNamesReport&rs:Command=Render to http%3A%2F%2Flocalhost%2FReportServer%2FPages%2FReportViewer.aspx%3F%252fReports%252fNamesReport%26rs%3ACommand%3DRender.

7.2. Outputs

The report should be displayed with the RDCE_Report_Data_Source hidden and with the value fed:

Names Report from the Report Viewer Web Application

Figure 39. Names Report from the Report Viewer Web Application

The code-behind of this project will not be covered on this section:

Checking if report is RDCE Enabled was covered on the RSExplorer++ tool.
Adding the RDCE_Report_Data_Source if needed was covered on the RSExplorer++ tool.
Setting the RDCE_Report_Data_Source as "Used In Query" was covered on the RSExplorer++ tool.
Handling "Special Consideration #3: Reports with drill-down Data Source Dependent Parameters" will be covered on that section.

8. Special Consideration #1: Reports with no parameters or Data Source Independent Parameters

Reports that comply with the following (before adding the RDCE_Report_Data_Source parameter) fall under this category:

Have no parameters, or
Have one or more parameters that do not depend on a Data Set.

The NamesReport report is an example of this type of reports because it does not have parameters:

Names Report having no parameters

Figure 40. Names Report having no parameters

An example of a report having parameters that do not depend on a data set would be a report that has a "Start Date" or an open text parameter.

8.1. Actions needed

No further action is needed. All these cases are managed properly with the explained approach.

9. Special consideration #2: Reports with one Data Source Dependent Parameter

Reports that comply with the following (before adding the RDCE_Report_Data_Source parameter) fall under this category:

Have at least one parameter, and
Only one of those parameters depends on a Data Set.

The NamesReportByDepartment report is an example of this type of reports because it has one parameter (Department) and it gets its Available Values from a Data Set:

Names Report by Department having one data source dependent parameter

Figure 41. Names Report by Department having one data source dependent parameter

9.1. Why is this important?

The RDCE does not kick-in when data sets used to populate drop down lists are called since the dataset-dependent parameters are populated first.

This means that even if we set the RDCE_Report_Data_Source parameter to "DEVELOPMENT", for example, we will get the dataset-dependent parameters populated with "PRODUCTION" data which is the DataSource they are pointing to by default on the report definition.

Let's look at the NamesReportByDepartment from the RSExplorer++ tool after RDCE enabling it and adding the RDCE_Report_Data_Source parameter. We add the "DEVELOPMENT" text to the RDCE_Report_Data_Source parameter and then the list of departments that is being shown is displaying "PRODUCTION" data, which shouldn't be the case.

Names Report by Department values of the Department parameter

Figure 42. Names Report by Department values of the Department parameter

If we then click on "View Report" we get an empty result set. We know that the RDCE customization took place because the "Data Source: DEVELOPMENT" text is shown, and the resulting data set was empty because of the data sources mismatch.

Names Report by Department pointing to a different data source at the parameters level

Figure 43. Names Report by Department pointing to a different data source at the parameters level

9.2. How is this solved?

This has to be handled at the Report Viewer Web Application level (or in your case, on whichever application is in charge of displaying the reports).

The trick consists on physically changing the Report Definition on the Report Server to change those DataSets which are used on parameters to point to the DataSource we need. This means that the report definition will physically change every time the report is called from a different DataSource.

//Find the report parameters that have data set references
var report_parameters_data_set_references = xreport.Elements(dns + "ReportParameters").Elements(
    dns + "ReportParameter").Elements(dns + "ValidValues").Elements(
    dns + "DataSetReference").Elements(dns + "DataSetName");
//Get data sets
var data_sets = xreport.Elements(dns + "DataSets").Elements(dns + "DataSet");
//Data source that the report parameters need to point to: PRODUCTION|DEVELOPMENT...
string parameter_value = report_data_source.ToUpper();

//Go through all the data sets identified and point them to the right data source
foreach (XElement xe in report_parameters_data_set_references)
{
    foreach (XElement xerp in data_sets)
    {
        if (xerp.Attribute("Name").Value == xe.Value)
        {
            xerp.Element(dns + "Query").Element(dns + "DataSourceName").Value = parameter_value.ToUpper();
        }
    }
}

System.Text.Encoding encoding = new System.Text.UTF8Encoding();
reportDefinitionProcessed = encoding.GetBytes(xreport.ToString());

//Save the edited report definition to the server
rs.SetItemDefinition(path, reportDefinitionProcessed, null);

//Reset the RDCE_Report_Data_Source as UsedInQuery
ModifiyUsedInQueryProperty(path);

Code Sample 25. [C#] Physically changing the Report Definition on the Report Server to change those DataSets which are used on parameters

With the previous code, now, when we run the report from the Report Viewer Web Application, it will appropriately change the data source of the data set that fetches the department names. We can be sure of it because the values now end with "Other Data Source":

Names Report by Department from the RSExplorer++ tool (parameter)

Figure 44. Names Report by Department from the RSExplorer++ tool (parameter)

We can be sure that now all the report is pointing to DEVELOPMENT when we click on "View Report" because we see that the First Name ends by "Other Data Source".

Names Report by Department from the RSExplorer++ tool (report)

Figure 45. Names Report by Department from the RSExplorer++ tool (report)

If we change the request to point to PRODUCTION, we will see that the parameters are back to the PRODUCTION ones:

Names Report by Department from the RSExplorer++ tool (parameter) pointing to PRODUCTION

Figure 46. Names Report by Department from the RSExplorer++ tool (parameter) pointing to PRODUCTION

And the end result is:

Names Report by Department from the RSExplorer++ tool (report) pointing to PRODUCTION

Figure 47. Names Report by Department from the RSExplorer++ tool (report) pointing to PRODUCTION

This is how Special Consideration #2 is tackled.

10. Special consideration #3: Reports with drill-down Data Source Dependent Parameters

Reports that comply with the following (before adding the RDCE_Report_Data_Source parameter) fall under this category:

Have at least two parameters, and
Both parameters depend on a dataset, and
The selection of one of the parameters triggers the data fetch of the other one (drill-down parameter).

The NamesReportByDepartmentAndTitle report is an example of this type of report because it has two parameters (Department and Title), both parameters depend on a DataSet (Department depends on DepartmentsDataSet and Title depends on TitlesDataSet) and the selection of the Department triggers the fetch of the Titles that exist on that department.

Names Report by Department and Title Report Data

Figure 48. Names Report by Department and Title Report Data

We know that there is a drill-down parameter because the TitlesDataSet contains the "@Department" parameter:

Names Report by Department and Title Report Data with dependency to the Department parameter

Figure 49. Names Report by Department and Title Report Data with dependency to the Department parameter

And also because the final results set depends on both parameters:

Names Report by Department and Title Report Data with dependency to the Department and Title parameter

Figure 50. Names Report by Department and Title Report Data with dependency to the Department and Title parameter

10.1. Why is this important?

We already learned from Special Consideration #2 that the RDCE does not kick-in when data sets used to populate drop down lists are called since the dataset-dependent parameters are populated first. We also learned how to tackle this problem. Unfortunately, this triggers a new problem when we have this scenario.

Imagine USER1 requesting the NamesReportByDepartmentAndTitle using the "Report Viewer Web Application". USER1 wants the report to point to PRODUCTION. When he requests the report, the "Report Viewer Web Application" changes the datasets if needed to point to PRODUCTION if we are on either Special Consideration #2 or #3. USER1 will get the parameters toolbar properly loaded with the first parameter data set pointing to PRODUCTION. USER1 waits for a while before selecting a Department from the list.

Names Report by Department and Title actions by USER1

Figure 51. Names Report by Department and Title actions by USER1

As you can see, the list of departments is properly pointing to PRODUCTION.

On the other hand we have USER2, which is requesting the same NamesReportByDepartmentAndTitle using the "Report Viewer Web Application". USER2 wants the report to point to DEVELOPMENT. By following the way the "Report Viewer Web Application" works, USER2 will get the parameters toolbar properly loaded with the first parameter data set pointing to DEVELOPMENT. USER2 selects the Department from the list (this is happening while USER1 is not doing anything yet).

Names Report by Department and Title actions by USER2

Figure 52. Names Report by Department and Title actions by USER2

As you can see, the list of departments is properly pointing to DEVELOPMENT.

USER2 now selects the "Engineering Other Data Source" value and the Titles dropdown list is filled up as follows:

Names Report by Department and Title actions by USER2

Figure 53. Names Report by Department and Title actions by USER2

We know that this is all correct since both dropdown lists are pointing to DEVELOPMENT.

USER2 now clicks on "View Report" and we can see that everything is fine, the report appears pointing to DEVELOPMENT after the RDCE customized it.

Names Report by Department and Title actions by USER2

Figure 54. Names Report by Department and Title actions by USER2

Let's go back to USER1. He finally wakes up and now decides to select the "Engineering" Department. As a surprise he finds out that the Title dropdown list comes empty:

Names Report by Department and Title actions by USER1

Figure 55. Names Report by Department and Title actions by USER1

When USER1 tries to click on "View Report", he/she will get an error message:

Names Report by Department and Title actions by USER1

Figure 56. Names Report by Department and Title actions by USER1

The only way to solve this is to press F5 for the "Report Viewer Web Application" to do its magic and fix the problem. Unfortunately, at this stage, we are unable to tell the user to press F5 and refresh the browser.

If USER1 refreshes, he/she will now be able to see the right report:

Names Report by Department and Title actions by USER1

Figure 57. Names Report by Department and Title actions by USER1

It is important to know that if this happens between parameter selection, there is no way to warn the user. However, if this conflict happens after parameter values have been selected, the RDCE can kick-in and let the user know that he/she needs to refresh the browser by pressing F5.

Names Report by Department and Title modified by the RDCE to indicate there was a conflict

Figure 58. Names Report by Department and Title modified by the RDCE to indicate there was a conflict

10.2. How is this solved?

Unfortunately, the only solution is for the end user to reload the report by pressing F5 on the browser. We cannot do this for them at the "Report Viewer Web Application" but we can tell the user to do it by checking this at the RDCE and changing the body of the report to just the "To see this report please reload the page. If you are on a PC you may press F5." message.

The trick is done by the following code on the RDCE:

//Get the report parameters that get values from data sets
var report_parameters_data_set_references = xreport.Elements(dns + "ReportParameters").Elements(
    dns + "ReportParameter").Elements(dns + "ValidValues").Elements(
    dns + "DataSetReference").Elements(dns + "DataSetName");
//Get the data sets
var data_sets = xreport.Elements(dns + "DataSets").Elements(dns + "DataSet");

//Check if the parameters that get values from data sets are more than 1
//If it's 0 or 1, we don't have an issue
if (report_parameters_data_set_references.Count() > 1)
{
    //Loop through the parameters and see if they are pointing to the right one
    foreach (XElement xe in report_parameters_data_set_references)
    {
        foreach (XElement xerp in data_sets)
        {
            if (xerp.Attribute("Name").Value == xe.Value)
            {
                if (xerp.Element(dns + "Query").Element(
                         dns + "DataSourceName").Value != ParamValue.ToUpper())
                {
                    rightDataSetDataSource = false;
                    break;
                }
            }
        }
    }
}                   

//If inconsistent data sources where found
if (!rightDataSetDataSource)
{ 
    //Add "inconsistent data sources" text to the report
    var entry2 = new XElement(dns + "Textbox");
    entry2.SetAttributeValue("Name", "tb_Error");
    entry2.Add(new XElement(dns + "CanGrow", "true"));
    entry2.Add(new XElement(dns + "KeepTogether", "true"));

    var paragraphs2 = new XElement(dns + "Paragraphs");

    var paragraph2 = new XElement(dns + "Paragraph");

    var textruns2 = new XElement(dns + "TextRuns");
    var textrun2 = new XElement(dns + "TextRun");
    textrun2.Add(new XElement(dns + "Value", "To see this report please " + 
       "reload the page. If you are on a PC you may press F5."));

    var style2 = new XElement(dns + "Style");
    style2.Add(new XElement(dns + "FontFamily", "Tahoma"));
    style2.Add(new XElement(dns + "Color", "SteelBlue"));

    textrun2.Add(style2);
    textruns2.Add(textrun2);
    paragraph2.Add(textruns2);
    paragraphs2.Add(paragraph2);

    entry2.Add(paragraphs2);

    entry2.Add(new XElement(seconddns + "DefaultName", "tb_Error"));
    entry2.Add(new XElement(dns + "Height", "0.25in"));
    entry2.Add(new XElement(dns + "Width", "8.65625in"));
    entry2.Add(new XElement(dns + "ZIndex", "4"));

    var style22 = new XElement(dns + "Style");
    var border2 = new XElement(dns + "Border");
    border2.Add(new XElement(dns + "Style", "None"));
    style22.Add(border2);

    style22.Add(new XElement(dns + "PaddingLeft", "2pt"));
    style22.Add(new XElement(dns + "PaddingRight", "2pt"));
    style22.Add(new XElement(dns + "PaddingTop", "2pt"));
    style22.Add(new XElement(dns + "PaddingBottom", "2pt"));

    entry2.Add(style22);

    //This replaces the whole body of the report with our text box
    xreport.Element(dns + "Body").Element(dns + "ReportItems").ReplaceAll(entry2);

    System.Text.Encoding encoding2 = new System.Text.UTF8Encoding();
    reportDefinitionProcessed = encoding2.GetBytes(xreport.ToString());

    List<RdceCustomizableElementId> ids2 = new List<RdceCustomizableElementId>();
    ids2.Add(RdceCustomizableElementId.DataSets);
    ids2.Add(RdceCustomizableElementId.Body);
    customizedElementIds = ids2;
    // RDL is customized but to show the error message
    return true; 
}

Code Sample 26. [C#] Changing the body of the report when Special Consideration #3 needs to be tackled

This is how Special Consideration #3 is tackled.

11. Conclusions

A multiple data sources approach using Report Server is not possible by default.
Pointing to Data Sources on run-time is possible by using a Report Definition Customization Extension (RDCE).
The RDCE is the part of the system that does most of the magic, even if data sources cannot be modified directly at that level, however, the datasets and the report body can.
The process might seem long but it is worth it to have the ability to do this with Shared Data Sources.
The disadvantage of this approach may be the one seen on Special Consideration #3 but at least it can be identified.

12. References

Lachev, T. (2008). Applied Microsoft SQL Server 2008 Reporting Services. Prologika Press.

Special thanks

Special thanks to Mr. Wilson Quilindo for his very valuable ideas, support, and guidance throughout the whole development of the solution.

Dynamically Pointing to Shared Data Sources on SQL Reporting Services using a Report Definition Customization Extension (RDCE)