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

• Download Sample Reports project - 30.9 KB
• Download RSExplorer++ installer - 273 KB
• Download RSExplorer++ source code - 97.1 KB
• Download RDCE source code - 29.9 KB
• Download Report Viewer Web Application source code - 237 KB

Table of contents

1. Introduction
1. The problem
2. The proposed solution overview
2. Background
3. Preparing the Shared Data Sources (Report Server side)
4. Preparing the reports (Sample Reports project)
5. The RSExplorer++ tool
1. Configuring the RSExplorer++ tool
2. Using the RSExplorer++ tool
3. How does the RSExplorer++ tool work?
6. The RDCE
1. Understanding RDL files
2. Creating the RDCE
3. Enabling the Report Server to recognize and implement the RDCE
4. Debugging the RDCE
7. The Report Viewer Web Application
1. Inputs
2. Outputs
8. Special consideration #1: Reports with no parameters or Data Source Independent Parameters
1. Actions needed
9. Special consideration #2: Reports with one Data Source Dependent Parameter
1. Why is this important?
2. How is this solved?
10. Special consideration #3: Reports with drill-down Data Source Dependent Parameters
1. Why is this important?
2. How is this solved?
11. Conclusions
12. 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:

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:

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:

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):

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

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

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:

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:

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



Figure 8. Sample Reports project properties
2. 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



Figure 9. Shared Data Sources on the Sample Reports project
3. 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.



Figure 10. Shared Data Source general properties



Figure 11. Shared Data Source general credentials properties
4. The project contains three reports. I will focus on "NamesReport.rdl" in this section, even though all of them follow the same conventions.



Figure 12. Reports on the Sample Reports project
5. The Design view of "NamesReport.rdl" looks like the following:



Figure 13. Design view on NamesReport.rdl
6. 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:



Figure 14. The Report Data window
7. On the Data Sources folder, you should have the exact same Data Sources you have at the project level:
• DEVELOPMENT
• QUALITY_ASSURANCE
• TEST
• PRODUCTION



Figure 15. Data Sources at the Report level
8. Each Data Source properties should be pointing to the shared data source at the Report Project level:



Figure 16. Data Source at the Report level general properties
9. Now let's look at the Data Sets at the Report level:



Figure 17. Datasets at the Report level
10. Each DataSet's properties has to be configured to point to the PRODUCTION data source as follows:



Figure 18. DataSet query properties at the Report level
11. If we preview the report, it should look like the following pointing to PRODUCTION data:



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



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>

5.2. Using the RSExplorer++ tool

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



Figure 21. The RSExplorer++ tool first screen
2. 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.



Figure 22. The RSExplorer++ tool at the Reports level
3. If you double-click on a report, for example the first one, you will be able to preview it.



Figure 23. Report Preview from the RSExplorer++ tool
4. 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.



Figure 24. Report Properties from the RSExplorer++ tool
5. 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.



Figure 25. Enabling RDCE on a report from the RSExplorer++ tool
6. 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.



Figure 26. The RDCE property on a report from the RSExplorer++ tool
7. 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:
8. 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.



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.



Figure 28. 'Add DS Parameter/Use In Query' transaction successful from the RSExplorer++ tool
9. 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



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.



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
}

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;
        }
    }
}

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);

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);

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);

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'

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

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();

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

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

• Body
• DataSets
• Page
• PageFooter
• PageHeader

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

• DataSources
• ReportParameters

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>

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>

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:

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.

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"

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

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
)

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();

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") + "}";
}

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; 
}

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;
}

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);

The text box will look like the following:

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;

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.

1. 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
2. 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
3. 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
4. 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:

1. The report server has to have all the shared data sources.
2. The report project has to have all the shared data sources.
3. Every single report needs to have the data source that point to the report project data sources.
4. All the data sets in the report need to be pointing to a default data source (PRODUCTION).
5. The report needs to be RDCE Enabled (use the RSExplorer++ tool).
6. The report needs to have the RDCE_Report_Data_Source parameter (use the RSExplorer++ tool).
7. The report needs to have the RDCE_Report_Data_Source's "Used In Query" attribute set as True (use the RSExplorer++ tool).
8. 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".
9. The rsreportserver.config and rssrvpolicy.config files need to be modified accordingly.
10. If all this is correct, you may try to debug the RDCE by attaching the process to the ReportingServicesService.exe [Debug - Attach to Process...].

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:

1. Get the report path and the data source to point to as Query String parameters.
2. Get any other parameter values as Query String parameters.
3. Feed the parameters to the report.
4. Hide the RDCE_Report_Data_Source to the end user.
5. Check if report is RDCE Enabled and add the RDCE_Report_Data_Source if needed before loading the report.
6. Set the RDCE_Report_Data_Source as "Used In Query" if needed before loading the report.
7. 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>

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

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:

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:

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:

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.

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.

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);

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":

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

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

And the end result is:

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.

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

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

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.

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

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:

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.

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:

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

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:

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.

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; 
}

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.