Introduction
This is a common scenario: You have a web site, written in ASP.NET (or MVC or SharePoint) and you would like to display some reports. You might be planning to write some new reports and you are trying to decide on which technology to use or you might have several SSRS reports that were already made, earlier, and you want to run them from your ASP.NET site.
There are many good articles on CodeProject that show how to use an RDLC file to run a report, either from ASP.NET or WinForms, etc. After reading them, it would seem that your choices are:
- Put a report into SSRS and use the
ReportViewer control to call SSRS to run your report - Add the .RDL or .RDLC file into your project and create some objects to house the data so the report has an interface to the database
I wanted a third choice: - Just run it like SSRS does, but without installing the SSRS server. I’m talking bare-minimum footprint. I just want to pass the name of the .RDL or .RDLC file to an ASPX page and have it run. That is how SSRS does it. I should be able to do that too.
This is an implementation of the third choice.
Prerequisites
- To run this, you still need to install the reporting run-time. You can add it as Nuget packages "Microsoft.ReportViewer.Runtime.Common" and "Microsoft.ReportViewer.WebForms" (or WinForms). The redistributable is available on Microsoft Downloads site.
- I had some old reports, which were made with the old Business Intelligence Development Studio (2008). I also have some new reports which were made with
ReportBuilder (built-into SSRS web). This solution will work with either.
Strategy
A .RDLC file is all XML. If you open it with Notepad and inspect the contents, you can see that the XML describes the display/design, but it also contains several other useful characteristics. One of them is the query (to get the data) for the report.
My strategy is to extract the database query, set up any parameters, run the query, store the results in a DataTable(s) and feed it into the report.
My goal is to run my reports from a generic page and pass the report name and any query parameters via the URL QueryString, like this:
.../View.aspx?Report=Example.rdlc&StartDate=1/1/2012&EndDate=12/31/2012
For simplicity, I will just use the same DB connection string that is used by the rest of the app, but I will wrap it in a local factory method, for maintainability.
Using the Code
Starting with the End in Mind
This is where I started. I borrowed from other articles on CodeProject (please see the credits, near the end of the article). The examples worked great, but didn’t have a means to use the query that was embedded in the report. In the code-block (below), you can see that I created a class called Report (namespace RDL) to encapsulate the RDLC’s content/structure. My RDL.Report class also contains a factory method to help with converting the XML into objects.
protected void ShowReport()
{
System.IO.FileInfo reportFullPath = this.ReportFile;
if (reportFullPath != null)
{
rvReportViewer.LocalReport.ReportPath = reportFullPath.FullName;
rvReportViewer.LocalReport.DataSources.Clear();
RDL.ReportreportDef = RDL.Report.GetReportFromFile(reportFullPath.FullName);
foreach (RDL.DataSet ds in reportDef.DataSets)
{
ds.AssignParameters(this.ReportParameters);
System.Data.DataTable tbl = ds.GetDataTable(this.DBConnectionString);
ReportDataSource rds = new ReportDataSource();
rds.Name = ds.Name;
rds.Value = tbl;
rvReportViewer.LocalReport.DataSources.Add(rds);
}
rvReportViewer.LocalReport.Refresh();
}
}
The Rest of the Story
The (above) block of code shows the heart of the application; running queries and attaching data to the report, then running the report. Now, let’s look at the parts that get the data.
Get the Query from the RDLC File
Inside the .RDLC file, the XML for the query looks like this (after you remove everything else):
<Report>
<DataSets>
<DataSet Name="IrrelevantToThisExample">
<Query>
<DataSourceName>DataTableName</DataSourceName>
<CommandText>SELECT * FROM sys.Tables</CommandText>
</Query>
</DataSet>
</DataSets>
</Report>
On my first attempt, I used XPath to extract the query from the XML (inside of the RDLC file). It worked for simple queries. However, I realized that things got messy if the query had any parameters (or for Stored Procedures, etc.).
On my second attempt, I took a different approach. I realized that the code would be much easier if I deserialized the XML into a stack of objects. It sounds complicated and scary, but once you see it, you will realize how incredibly simple XML Serialization/Deserialization can be.
The (simplified) classes that match this XML look like this:
[Serializable(), System.Xml.Serialization.XmlRoot("Report")]
public class Report : SerializableBase
{
public List<DataSet> DataSets = new List<DataSet>();
}
public class DataSet
{
[System.Xml.Serialization.XmlAttribute]
public string Name;
public Query Query = new Query();
}
public class Query
{
public string DataSourceName;
public string CommandText;
}
Once you deserialize the XML, you can easily extract the query like this:
Report report =Report.Deserialize(xml, typeof(RDL.Report));
String commandText = report.DataSets[0].Query.CommandText;
The SerializableBase object is something I have reused from several projects. It makes it simple to serialize or deserialize any object to XML or vice-versa. Here is the code:
[Serializable]
public class SerializableBase
{
public static SerializableBase Deserialize(String xml, Type type)
{
System.Xml.Serialization.XmlSerializer ser =
new System.Xml.Serialization.XmlSerializer(type);
using (System.IO.StringReadersr = new System.IO.StringReader(xml))
{
return (SerializableBase)ser.Deserialize(sr);
}
}
}
Set Up Any Parameters
As I mentioned earlier, the code was very simple until I dealt with parameterized queries and stored procedures. I had to add several more classes for deserialization. For brevity, I will include them in the downloaded code but save you the headache of reading the code here. Don’t worry. They are very simple (boring) classes that match the structure of the XML, just like the serialization classes above.
Refactored
The rest of this code started out in utility classes. After looking at them, I realized that it would be more OO pure if I encapsulated the utility code within the serialization classes as methods rather than as external helper utility functions. It makes the serialization classes seem more complicated. That is why, for this article, I started by describing the original classes (above) in their simplest form.
Report Parameters/Query Parameters
Unfortunately, in the RDLC files, the query block defines its parameters, but doesn’t define types for them. The DB will choke on types that don’t cast easily, such as: DateTime, Numeric, and Integer. Luckily, the parameter types are defined in a separate part of the RDLC’s XML. I just need to copy those into the query parameter definitions. Unfortunately, it makes the code seem a little hackish, but it does get the job done reliably.
private void ResolveParameterTypes()
{
foreach (ReportParameter rParam in this.ReportParameters)
{
foreach (DataSet ds in this.DataSets)
foreach (QueryParameter qParam in ds.Query.QueryParameters)
{
if (qParam.Value == "=Parameters!" + rParam.Name + ".Value")
{
qParam.DataType = rParam.DataType;
}
}
}
}
public static Report Deserialize(string xml, Type type)
{
Report re;
re = (Report)SerializableBase.Deserialize(xml, type);
re.ResolveParameterTypes();
return re;
}
URL Parameters
Now, I copy the parameters from the (URL) QueryString, into the report’s params. Naturally, I am making some big assumptions about the QueryString parameter names matching those of the report. If they don’t match, there will be an error but it should be simple to figure-out what went wrong. I could also add some diagnostics to detect which parameters didn’t get values assigned to them (maybe later).
private System.Collections.Hashtable ReportParameters
{
get
{
System.Collections.Hashtable re = new System.Collections.Hashtable();
foreach (string key in Request.QueryString.AllKeys)
{
if (key.ToLower() != "path")
{
re.Add(key, Request.QueryString[key]);
}
}
return re;
}
}
public void AssignParameters(System.Collections.HashtablewebParameters)
{
foreach (RDL.QueryParameter param in this.Query.QueryParameters)
{
string paramName = param.Name.Replace("@", "");
if (webParameters[paramName] != null)
param.Value = webParameters[paramName].ToString();
}
}
Run the Query and Fill the DataTable
This is pretty 101. Set up the command object, add parameters, then just use a DataAdapter to populate a table.
public System.Data.DataTable GetDataTable(string DBConnectionString)
{
System.Data.DataTable re = new System.Data.DataTable();
using (System.Data.OleDb.OleDbDataAdapter da =
new System.Data.OleDb.OleDbDataAdapter(this.Query.CommandText, DBConnectionString))
{
if (this.Query.QueryParameters.Count > 0)
{
foreach (RDL.QueryParameter param in this.Query.QueryParameters)
{
string paramName = param.Name.Replace("@", "");
using (System.Data.OleDb.OleDbCommand cmd = da.SelectCommand)
cmd.CommandText = cmd.CommandText.Replace(param.Name, "?");
using (System.Data.OleDb.OleDbParameterCollection params = da.SelectCommand.Parameters)
switch (param.DataType)
{
case "Text":
params.Add(new OleDbParameter(paramName, OleDbType.VarWChar)
{ Value = param.Value });
break;
case "Boolean":
params.Add(new OleDbParameter(paramName, OleDbType.Boolean)
{ Value = param.Value });
break;
case "DateTime":
params.Add(new OleDbParameter(paramName, OleDbType.Date)
{ Value = param.Value });
break;
case "Integer":
params.Add(new OleDbParameter(paramName, OleDbType.Integer)
{ Value = param.Value });
break;
case "Float":
params.Add(new OleDbParameter(paramName, OleDbType.Decimal)
{ Value = param.Value });
break;
default:
params.Add(new OleDbParameter(paramName, param.Value));
break;
}
}
}
da.fill(re);
re.TableName = this.Name;
return re;
}
Follow-up (Refactor)
I did refactor this code (in the download) which made it a little messy. I wanted to make it flexible, so I could use it in several projects. Since I couldn’t be sure the db connection string will always be an OLEDB or SqlClient connection, I checked the connection string and used the appropriate set of libraries (OLEDB/SQLClient) for either one. The code became twice as long, but more portable.
Update: Other Things That Cause Errors
A friend helped me run some test reports and it revealed that the ReportViewer control does not generate any good/useful error messages, if there is a problem. In contrast, the "Download" button that I put on the View page, will easily report errors during processing. From this, I learned a few things:
- If a report has an external graphic or report parts, those files need to be available (meaning, you need those files in the /reports folder too) and in the correct path. For my test example, the report used graphics from a folder /Reports/Web Parts/.
- If a report has required parameters (or not "optional"), those must be provided or the report will not run.
- Parameters can be case-sensitive. Maybe it is just .NET. Either ways, if your report won't run, check to make sure you are providing a "
PrintID" or a "PrintId" and not just a "printid".
Finally, I recently updated this example to run with Visual Studio 2013. I added a "Diagnostic" page, to check a few settings. I improved the "Download" option. I improved this example to work with external images, and to detect required report parameters.
I hope you find that it works better for you. If not, I would appreciate feedback and maybe even an example file (.rdl) so I can work-out any errors.
Conclusion
That is all it takes to extract the query from an RDLC file and run it in ASP.NET.
SSRS was originally written by Microsoft as an example of how to use these technologies to accomplish what I have shown here. Of course, SSRS has many features that go way beyond what I have shown, but if you don’t need all of those rich features, this code should be quick and portable for you and your .NET projects.
Advanced Options
This article has covered a very simple way of running simple RDLC files. However, you may have some more complicated reports. Here are a few resources that you can continue reading about to cover more advanced topics that build on this concept:
Credit
I borrowed some of this code from other projects. These guys did some excellent work and deserve credit for some of the tricky parts of my project.
History
- Original version (current)
- Feb 2016: Update for Visual Studio 2013, RDL 2010 (SSRS 2012 reports), and better diagnostics
