You can run reports via SQL Reporting Services but you also have the option to run them locally and serve them via your own code.
Setup Visual Studio by downloading two extensions
Enable RDLC Reporting in Visual Studio 2017
Go to Visual Studio Marketplace
Microsoft Rdlc Report Designer for Visual Studio –
Rdlc Designer and projects for ASP.Net and Windows Forms for Reporting Services Report Viewer Control
Microsoft Reporting Services Projects –
The Microsoft RDL report designer, projects and wizards for creating professional reports.
Download both package and install.
To create and edit Client Report Definition (.rdlc) Files, create a Visual c# Reports Application project. Have a schema file available for the dataset you want to use with your report(s).
Add new item, choose XML Schema. In XML Schema Explorer choose the xsd file and right click. Select to view code. Paste in your code from your saved schema file.
To edit this schema in the future and get back to XML Schema Explorer:
Right click on your existing .xsd file and select “open with”.
Select “XML Schema Designer”
Then in the template that comes up, click the link that says “XML Schema Explorer”.
Now in your report, to make data available to a report section choose Datasets, right mouse click and Add Dataset. For DataSource there is a dataset available from the schema you added. In Available datasets pick the table you want to reference. Name it appropriately as the same as the table for instance. I will add a reference to PlanDetail and FundData.
For my purposes I add a textbox with an expression of
=First(Fields!PlanName.Value, "PlanDetail")
I added a table and then right clicked on the header cell (the one that shows up when you enter the table, and selected Tablix Properties. I pointed it to Dataset name FundData. After which I added cells with text boxes that used expressions that pointed to data points from that dataset.
=Fields!Ticker.Value
or
=Fields!LegalName.Value
To run the report and generate a pdf file you will need a separate project.
Create a c# Class Library (.NET Framework) project and install the nuget package Microsoft.ReportView.Windows.
using System.Data;
using System.IO;
using Microsoft.Reporting.WinForms;
namespace ClassLibrary1 {
public class Class1 {
public void CreatePDF(string reportPath, DataSet ds) {
// Variables
Warning[] warnings;
string[] streamIds;
string mimeType = string.Empty;
string encoding = string.Empty;
string extension = string.Empty;
// Setup the report viewer object and get the array of bytes
ReportViewer viewer = new ReportViewer {ProcessingMode = ProcessingMode.Local};
viewer.LocalReport.ReportPath = reportPath;
var xml = new XmlDocument();
xml.Load(reportPath);
//add namespaces so that xml may be traversed
//http://microsoft.public.sqlserver.reportingsvcs.narkive.com/U1JHd8Nj/unable-to-parse-rdlc-with-xpath
var ns = new XmlNamespaceManager(xml.NameTable);
ns.AddNamespace("ns", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition");
ns.AddNamespace("rd", "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner");
// ReSharper disable once PossibleNullReferenceException
foreach (XmlElement dataset in xml.SelectNodes("//ns:Report/ns:DataSets/ns:DataSet", ns)) {
var name = dataset.GetAttribute("Name");
var table = dataset.SelectSingleNode("rd:DataSetInfo/rd:TableName", ns)?.InnerText;
viewer.LocalReport.DataSources.Add(new ReportDataSource(name, ds.Tables[table]));
}
byte[] bytes = viewer.LocalReport.Render("PDF", null, out mimeType, out encoding, out extension, out streamIds, out warnings);
using (FileStream stream = new FileStream("c:\\temp\\data\\test.pdf", FileMode.OpenOrCreate, FileAccess.Write, FileShare.Read)) {
stream.Write(bytes, 0, bytes.Length);
}
//// Now that you have all the bytes representing the PDF report, buffer it and send it to the client.
//Response.Buffer = true;
//Response.Clear();
//Response.ContentType = mimeType;
//Response.AddHeader("content-disposition", "attachment; filename=" + fileName + "." + extension);
//Response.BinaryWrite(bytes); // create the file
//Response.Flush(); // send it to the client to download
}
}
}
To add an additional .rdlc report to your project, add a new item to your project and select Visual C# Items. Report or Report Wizard should be in the list at that level. To simply add an empty report choose Report. You have a datasource in the project per description above, so you can then add a dataset, or reference to a table, and add controls to your report at that point.
Code referenced here is also used in a project that lets you pick an xml data file and a Client Report Definition (.rdlc) File to preview. With this program you can modify the report and refresh the report control as you make changes.
RDLC Preview project on GitHub
One thing I ran across in further development was the need to use subreports. A report is based off of context, which is my term here that simply means the initial data reference for controls on a page. That context could be a blank page that lets you throw text boxes on it that reference a dataset and you could have many text boxes that each reference a different dataset. You could have a table and a chart each referencing a different dataset. That is because there is no starting context, no dataset that each of these controls references by default.
But lets say you want to have a report that shows some details about a list of objects. For my purposes I need to show fund fact sheets, details about investment funds. I have a dataset that contains a list of funds and some details about it. So I put a table on the report and make it one column that has a cell that is as large as a single page. Ill end up with a page for each fund in the dataset that describes all the properties of that fund like the name of the fund and maybe a chart showing performance history (oops, that is going to be an issue. We’ll get to that). Inside that cell I put text boxes for the various details. I have another dataset that is broken down by fund ids so it is possible to have text boxes with expressions that lookup a detail in that other dataset using the fund id as the key if all I need to do is show a single piece of data. Lets say its the category name of the fund. We have the category id associated with that fund in the FundData dataset but the name is stored in the CategoryData dataset. That is perfectly fine given we have a Lookup function that we can use in the expression of the textbox.
However, if I want to show a table of data from another dataset embedded inside this cell then I cannot do that. I can show a chart if it is based off of individual fields in the current dataset or uses expressions to lookup a field, but not a series. The issue is ,unlike a blank page that has no set context, I am inside of a cell in a table that has the FundData dataset as a context. You cannot reference datasets inside of controls for other datasets in this way.
Update, there are Lookup functions that let you step outside of the table’s defined dataset for specific queries. If you need outside data, it may be available, especially if its a single item that you need.
The Portfolio Analysis section in the image above is the subreport. So I go from a series of text boxes referring to the fund, to this analysis section which will be a series of tables from other datasets, and then back to the Operations & Expenses section which will be a series of text boxes from the FundData dataset or that will use a Lookup function in the expression.
What I could do is normalize all of the data and do some grouping so that there is only one dataset. That either may be complex or impossible to get right depending on what you want to do. So how do I put a table in this cell that references another dataset? I need a subreport. I need to break that context out and go back to a blank context by shifting the reporting process over to a subreport and it should be noted that a subreport is just like this main report, its just another .rdlc file.
Unlike Crystal Reports, subreports in .rdlc files are separate files, they are not embedded in the main report. When the report is run, the runtime will pull in the subreport based on the file name you reference in configuration for the subreport. However, it also needs to do a little more than that. As part of getting the main report set up in the reportviewer control you assigned a datasource to the viewer control, but this subreport needs to be setup as well. Our program needs to load the datasets and reference them appropriately for that subreport. You handle this with SubreportProcessingEventHandler(object sender, SubreportProcessingEventArgs e). So after we set the datasource of our main report add this line
// Add a handler for SubreportProcessing
reportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(DemoSubreportProcessingEventHandler);
and this method which will load the same dataset used to process the main report and pull in various tables defined in the subreport as accessed by SubreportProcessingEventArgs.DataSourceNames. We are going to use a convention that the tables in this System.Data.Dataset correspond to Sql Report Dataset names. Don’t confuse a System.Data.Dataset which can have one to many tables with a Sql Reporting Dataset which is just one table of data. The Sql Reporting DataSource is in our case going to come from the System.Data.DataSet though we will load it one table at a time. A table in the System.Data.Dataset becomes a Dataset in the DataSolurce of the report. So again we will use the name of the table in the System.Data.Dataset as the name of our Dataset in the report which is is not technically required but it is required for this example.
void DemoSubreportProcessingEventHandler(object sender, SubreportProcessingEventArgs e) {
var ds = new DataSet();
ds.ReadXml(_dataPath, XmlReadMode.ReadSchema);
foreach (var name in e.DataSourceNames) {
e.DataSources.Add(new ReportDataSource(name, ds.Tables[name]));
}
}
One thing to note here is that this event is called each time the subreport is processed. So if you have a thousand funds in your table in the main report and a subreport defined in a cell in that table then it will be called a thousand times. The reason we need a subreport is because the context at the point we reference the table is not blank, its the dataset of the cell. What if we need 10 separate tables to show at a certain point in our main report? Should we have 10 subreports? It depends I guess but in my case I can get away with one. A new subreport that has a blank context. On that subreport I can throw any number of tables (in this case 10) and charts that reference multiple datasets. I wont have a problem with this, it doesn’t violate the rules of Sql reports. Now if I need to embed another table inside of one of those tables that uses a different dataset than its parent then I’ll need yet another subreport. Fortunately Sql Reporting allows you to embed subreports inside of subreports, Crystal Reports does not. It probably isn’t a good idea from a performance stand point since the event to load the data will fire for each of those embedded subreports.
In the code for CreatePDF above you’ll see this line.
var ns = new XmlNamespaceManager(xml.NameTable);
ns.AddNamespace("ns", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition");
ns.AddNamespace("rd", "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner");
It worked at the point of testing the code above but in followup work I loaded a new version of Visual Studio and the namesspace Uri changed to 2016/1/reportdefinition. The code kind of stopped working at that point and I didn’t know why. Obviously I figured it out. So here is the fix.
var ns = new XmlNamespaceManager(xml.NameTable);
foreach (XmlAttribute nsdef in report.Attributes) {
var attributeName = nsdef.Name;
if (attributeName.StartsWith("xmlns")) {
if (attributeName.Contains(":")) {
ns.AddNamespace(attributeName.Split(':')[1], nsdef.InnerText);
}
else {
ns.AddNamespace("default", nsdef.InnerText);
}
}
}
I know its a lot to process and I need some pictures and color formatting of the code but the Github project is up to date.
