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:
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:
Figure 2. Proposed solution overview
2. Background
To be able to understand this article, you need:
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:
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):
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).
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
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:
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
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
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.
Figure 10. Shared Data Source general properties
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.
Figure 12. Reports on the Sample Reports project
- The Design view of "NamesReport.rdl" looks like the following:
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:
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
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:
Figure 16. Data Source at the Report level general properties
- Now let's look at the 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:
Figure 18. DataSet query properties at the Report level
- If we preview the report, it should look like the following pointing to PRODUCTION data:
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):
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.
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.
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.
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.
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.
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.
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.
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
- 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)
{
}
else
{
}
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)
{
if (ip.Name == "RDCE_Report_Data_Source")
{
hasParameter = true;
if (ip.QueryParameter == 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();
using (mstream = new MemoryStream(rs.GetItemDefinition("/Reports/NamesReport")))
{
doc.Load(mstream);
mstream.Position = 0;
}
XElement xreport = XElement.Load(new XmlNodeReader(doc));
string dns = "{" + xreport.GetDefaultNamespace() + "}";
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:"));
bool hasParameters = xreport.Element(dns + "ReportParameters") != null;
if (!hasParameters)
{
XElement parent = new XElement(dns + "ReportParameters");
parent.Add(entry);
xreport.Element(dns + "DataSets").AddAfterSelf(parent);
}
else
{
xreport.Element(dns + "ReportParameters").AddFirst(entry);
}
System.Text.Encoding encoding = new System.Text.UTF8Encoding();
reportDefinitionProcessed = encoding.GetBytes(xreport.ToString());
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())
{
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")
{
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();
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
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
Figure 32. Customizable Elements of an RDL file
This means that the following elements cannot be customized during runtime:
- DataSources
- ReportParameters
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:
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.
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".
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)
{
return false;
}
if (reportContext.QueryParameters["RDCE_Report_Data_Source"] == null)
{
return false;
}
if (reportContext.QueryParameters["RDCE_Report_Data_Source"].Values.Length == 0)
{
return false;
}
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.
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));
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:
var data_sources = xreport.Element(dns + "DataSources").Elements(dns + "DataSource");
bool dataSourceExists = false;
foreach (XElement xe_ds in 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)
{
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:
var data_sets_data_sources = xreport.Element(dns + "DataSets").Elements(dns + "DataSet");
foreach (XElement xe in data_sets_data_sources)
{
if ((xe.Element(dns + "Query").Element(dns +
"DataSourceName").Value).ToUpper() == dataSourceToReplace.ToUpper())
{
xe.Element(dns + "Query").Element(dns + "DataSourceName").Value = ParamValue.ToUpper();
datasourcewaschanged = true;
}
}
if (!datasourcewaschanged)
{
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:
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);
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:
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:
xreport.Element(dns + "Body").Element(dns + "ReportItems").Add(entry);
System.Text.Encoding encoding = new System.Text.UTF8Encoding();
reportDefinitionProcessed = encoding.GetBytes(xreport.ToString());
List<RdceCustomizableElementId> ids = new List<RdceCustomizableElementId>();
ids.Add(RdceCustomizableElementId.DataSets);
ids.Add(RdceCustomizableElementId.Body);
customizedElementIds = ids;
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...].
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:
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:
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:
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.
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.
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.
var report_parameters_data_set_references = xreport.Elements(dns + "ReportParameters").Elements(
dns + "ReportParameter").Elements(dns + "ValidValues").Elements(
dns + "DataSetReference").Elements(dns + "DataSetName");
var data_sets = xreport.Elements(dns + "DataSets").Elements(dns + "DataSet");
string parameter_value = report_data_source.ToUpper();
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());
rs.SetItemDefinition(path, reportDefinitionProcessed, null);
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":
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".
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:
Figure 46. Names Report by Department from the RSExplorer++ tool (parameter) pointing to PRODUCTION
And the end result is:
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.
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:
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:
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.
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).
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:
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.
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:
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:
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:
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.
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:
var report_parameters_data_set_references = xreport.Elements(dns + "ReportParameters").Elements(
dns + "ReportParameter").Elements(dns + "ValidValues").Elements(
dns + "DataSetReference").Elements(dns + "DataSetName");
var data_sets = xreport.Elements(dns + "DataSets").Elements(dns + "DataSet");
if (report_parameters_data_set_references.Count() > 1)
{
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 (!rightDataSetDataSource)
{
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);
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;
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.