One of the problems I encountered in creating a Sql Report was in positioning a series of tables. I had a set of 6 tables. One on the left, three in the middle and two on the right. All had different and sometimes expanding heights. The problem is they were not being positioned where I placed them or as I would expect. Sometimes with large spaces in between tables, sometimes perfectly positioned. SSRS does its best to figure out where to put them but its guessing and I think I figured out what it does.
What seems to happen is it creates a grid based on the height of tables going left to right. I had three tables on the top row, with their tops aligned. If the left most table was taller than the middle then the middle two ‘rows’ of tables would have a large blank space between them. The second of those tables appears to have been placed inside a hidden row. If I extended the leftmost table so that its bottom was predefined to be below the top of the middle’s second table then this space was not there but now that invisib le row is defined by the middle rows second interior row of tables which could cause the same sort of issue with the rightmost tables. Its as if it creates two large rectangles to define columns and merges the cells of the middle. Now the problem is as you develop the report the expectations of where things will end up can get weird if you forget or cannot anticipate some of these positioning tricks.
In the below example there appears to be an invisible row that contains the first table on the left, the two middle tables, and the top right most table. The bottom table in the middle has been told that it can leak into the first row by overlapping it with the tables above it but having its top above the leftmost tables bottom. Yeah, its complicated and I fail to carry it through to the last table on the bottom right. The bottom table of the rightmost column cannot leak/overlap and so it sits inside an invisible second row that starts at the bottom of the leftmost table.
How can you fix it so you get what you expect? Put in rectangles to define your own grid exactly the way you expect and to disassociate the heights of tables from defining the height of the invisible grid SSRS is using. So my five tables end up in three rectangles. One for each column and these are assumed to all be in the same invisible grid row SSRS will create to contain these rectangles. Now of course within these rectangles SSRS is going to have the same time trying to figure out placement, so you can have your own rectangles to further define positions, if necessary.
Below you will see on the left the designer for SSRS and the fund fact sheet report. On the right you will see the same report represented in Crystal Reports. The tables I am going for have to be re-positioned to fit and work properly in SSRS, though strictly speaking because they were not working properly in Crystal, being cutoff and ignoring the need to expand them there.
The following shows my final layout for the tables. If you look closely you see three rectangles that define the outermost columns.
So the answer to positioning issues is to use rectangles to separate out the grid system created by various objects from one another and help to control how SSRS defines positions of tables and subreports.
Local SQL Report (.rdlc) Processing
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.
Aurelia View with SQL Reporting Client vs Server Side Execution
I have an Aurelia.io application with a view where I want to embed a SQL Server Report result into an iframe. Do I simply use an external src or go with a server to server execution and return the result locally?
-
[Im just learning the limitations of wordpress with respect to it removing iframe code and messing with code snippets. Ill have to make my own server and port this over to deal with this myself]
The site is tested in a secure page so the first thing to know is that if you call the report directly as the src of the iframe that it also needs to be secure or there will be a mixed mode error returned by the client. Just make sure the url to the report is https.
The second issue is that the content for the report is served up from a different domain. So there would be same origin errors resulting from this call as well. What we need to do is have the server we are calling return the appropriate headers to allow the content on a different origin. This is done by adding the Embed parameter to the url of the report.
The next problem is that the report server requires authentication. So going to the page will result in a prompt for a network username and password through basic authentication for the content coming from the report server. That part is dealt with by the user entering the information when prompted or by configuring the report server to not require authentication. We will not discuss configuring the report server to not require authentication.
The following is an aurelia.io view snippet that binds the id of a plan object to build the url required to return the report that would otherwise be viewed in report server. We will remove the parameter inputs and the toolbar and just show the report itself.
This assumes a project called Report Project1 and a report named Report1.
Assume the following applies to an iframe and these are the attributes required. The iframe itself is not shown because WordPress strips the code.
if.bind="plan.id"
src.bind="'https://localhost/ReportServer?
%2fReport%20Project1/Report1&rs:Embed=true&rc:Parameters=false&rs:Command=false&PlanId=' + plan.id"
width="100%"
height="400"
Now the issue is the user could view the src of this iframe and change the plan to be a plan they do not have rights to and execute it in the browser. Barring authorization inside the report itself we need another way to control security on this file. What we can do instead is call our api to Response.Write the result of this call to the reporting service and in our api perform whatever security we have on the user and access to plans. Lets assume we have a controller named PlanApiController with a method named ReportHtml that takes in the id of the plan we want to use in our report.
Note, we will not validate the plan in the following code, but we could. So lets change our Aurelia view as follows:
Assume the following applies to an iframe and these are the attributes required.
if.bind="plan.id"
src.bind="'https://localhost:44319/api/planapi/ReportHtml/' + plan.id" width="100%"
height="400"
One thing we have to account for here is that report server still needs authentication to take place. The client will not receive a prompt. So we are both handling this annoyance and also creating a way for our api to verify the user parameters to the report before calling it. We will not actually perform that validation as it is trivial.
using System.Net;
using System.Net.Http;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
[HttpGet]
[Route("[action]/{id:int}")]
public async Task ReportHtml(int id, CancellationToken cancellationToken) {
var asPDF = "&rs:Format=PDF"; //leave blank to return the html as if it were embedded and called directly from ui
//If the site its hosted in is https then a mixed mode error will result, in this case it isn't really required since its server to server and the browser wont
//throw an error but for sake of consistency between client based examples we will do the same here.
var protocal = "https://"; //https is required when using this clientside, but server to server does not require it because clientside does not see the return as mixed mode
var reportPath = "/Report Project1/Report1"; //path should start with a slash
var embed = "&rs:Embed=true"; //embed gets around same origin issues as it directs the report server to add necessary headers to the result
var hideParameters = "&rc:Parameters=false"; //dont show the parameters inputs
var hideCommandBar = "&rs:Command=false"; //dont show the navigation and control toolbar
var planId = $"&PlanId={id}"; //this is a parameter required by the report itself
var url = $"{protocal}localhost/ReportServer?{reportPath}{asPDF}{embed}{hideParameters}{hideCommandBar}{planId}";
Console.WriteLine("GET: + " + url);
//reference url to how to do this
//https://rocky1978.wordpress.com/2012/12/10/c-how-to-access-website-with-windows-credential-ntlm/
//Credentials are those needed to access the report server site itself,
//not required to access the database inside the report. It is
//possible to set the server up to not require a login and
//we might do this in an intranet scenario
var credential = new NetworkCredential("username", "password", "domain");
var myCache = new CredentialCache { { new Uri($"{protocal}localhost/"), "NTLM", credential } };
// Add the target Uri to the CredentialCache with credential object
// Create an HttpClientHandler to add some settings
var handler = new HttpClientHandler {
AllowAutoRedirect = true,
Credentials = myCache
};
// ... Use HttpClient.
HttpClient client = new HttpClient(handler);
HttpResponseMessage response = await client.GetAsync(url, cancellationToken);
HttpContent content = response.Content;
// ... Check Status Code
Console.WriteLine("Response StatusCode: " + (int)response.StatusCode);
if (string.IsNullOrEmpty(asPDF)) {
// ... Read the string. when html output
var result = await content.ReadAsStringAsync();
await Response.WriteAsync(result, cancellationToken);
}
else {
var result = await content.ReadAsByteArrayAsync();
await Response.Body.WriteAsync(result, 0, result.Length, cancellationToken);
}
}
Resources used while researching this feature
https://docs.microsoft.com/en-us/sql/reporting-services/export-a-report-using-url-access?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/reporting-services/report-builder/rendering-to-html-report-builder-and-ssrs?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/reporting-services/report-builder/export-reports-report-builder-and-ssrs?view=sql-server-2017
https://social.msdn.microsoft.com/Forums/SECURITY/en-US/9ef2f893-648b-46d3-9339-9fd06075570a/ssrs-prompting-for-usernamepassword-when-trying-to-access-the-report-installed-on-a-server-from?forum=sqlreportingservices
https://docs.microsoft.com/en-us/sql/reporting-services/security/configure-windows-authentication-on-the-report-server?view=sql-server-2017
https://stackoverflow.com/questions/12166202/reporting-services-ssrs-prompts-with-username-password
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/350d4ced-548c-48b5-87f0-0afaaa0f834f/how-to-hide-parameters-in-reporting-url-in-ssrs-2008?forum=sqlreportingservices
https://app.pluralsight.com/library/courses/sql-server-reporting-playbook/table-of-contents
https://hajloo.wordpress.com/2010/05/06/how-to-convert-rdl-files-to-rdlc-files/
https://docs.microsoft.com/en-us/sql/reporting-services/report-data/specify-credential-and-connection-information-for-report-data-sources?view=sql-server-2017
httpclient with credentials
https://gist.github.com/bryanbarnard/8102915
https://rocky1978.wordpress.com/2012/12/10/c-how-to-access-website-with-windows-credential-ntlm/ <—this one works