.Net Dojo: Microsoft.Reporting Without SQL Server 2005

I was working for a client who wanted an easy way to build dynamic reports.  They wanted to be able to easily alter the reports, with the option to save them off to Excel.  Now, I could have used a GridView to show the data and written out the Excel sheet, but I think Grid Views are overrated and over used as reporting tools.  So, I started looking at the Microsoft.Reporting namespace.  This namespace provides similar functionality to Crystal Reports.  The only problem: my data was stored in Oracle.  I started by picking apart how the ReportViewer works to see if I could somehow import the data dynamically.  Logically, I thought the Sql 2005 reporting services was essentially building a report based on a dataset selected from the database.  Here’s what I discovered.

The ReportViewer uses two things to display a report.  The first is a dataset with the table of data.  The second is an .RDLC file, which after looking at one is simply an xml file that tells the reporting engine the name of the dataset, data table, the columns, the data that goes into each column, and some formatting options.  When you add a chart, you have nearly the same items, except you chose the column that the series data goes in, along with a few other bits of data.  For the most part though, altering what is displayed became a simple means of swapping a few critical pieces of information.

The best way to determine how the features work is to create an .RDLC file (called a Report) in Visual Studio 2005 or later.  Add the features that you want, and then save off the RDLC as a .XML file.  Open it in Visual Studio and see what the engine generated. Here is the gist of what makes up the Data Source area.

<DataSources>  
 <DataSource Name="DataSetName" />  
</DataSources>
<DataSets>
 <DataSet Name="DataSetName">  
 	<Fields>  
 		<Field Name="ColumnName" DataField="ColumnName" />  
 		...  
 	</Fields>  
 </DataSet>
</DataSets>

DataSetName is the System.Data.DataSet.DataSetName. ColumnName is the actual name of the column in the System.Data.DataTable for that field. I iterate through the Columns in the DataTable to build this field list.Next, there is a section in the body of the document that defines the order of the columns and how they are displayed.

<Details>  
 <TableRows>  
 	<TableRow>  
 		<TableCells>  
 			<TableCell>  
 				<ReportItems>  
 					<TextBox Name="textBox1" ZIndex="1" />  
 					<Value>=Fields!ColumnName.Value</Value>  
 				</ReportItems>  
 			</TableCell>  
 		</TableCells>  
 	</TableRow>  
 </TableRows>
</Details>

Inside ReportItems are also some formatting options, which I have left out for simplicity. One of the complicated tasks I wanted to achiene was to have an alternating background for these Items. This was achieved by setting the propert to “=iif(RowNumber(Nothing) Mod 2, “#000000″, “#ffffff”)” where the 2 colors are actually swapped out depending on values passed in.Once you are able to build the .RDLC xml from scratch, you have to know what to do with it. You have a few options. The first is to generate the RDLC as a file. This works with a windows app where you can save the rdlc to a folder so that it does not need to be re-generated. However, for the web, having your code write files to the server is typically not an option. So, the next best thing is to generate the RDLC as a string, put the string into a memory stream, and then dump the stream into the ReportViewer control.

NOTE: On the web, it is not possible to reload a report after the control has been loaded. You must hit the page again and use the querystring, a cookie, the session, or some other means of passing data to the page in order to generate the report. After the initial load, the Web version of the ReportViewer will not update. The Windows Application version does not have this problem.

Here is a sample block of code that brings in a DataTable of data. In this instance, the DataTable was part of a dataset. You will need to make sure the dataset is not null, and that the DataTable and DataSet have names BEFORE binging them into the rdlc XML.

DataTable customerDataTable = Data.GetUsers();
customerDataTable.TableName = "CustomerDataTable";
DataSet customerData = customerDataTable.DataSet;
customerData.DataSetName = "CustomerData";
Olympus.Reporting.Report report = new Olympus.Reporting.Report(customerData);
reportViewer1.LocalReport.DataSources.Add(new Microsoft.Reporting.WinForms.ReportDataSource(customerData.DataSetName, customerDataTable));
reportViewer1.LocalReport.LoadReportDefinition(report.GetRdlcStream());
reportViewer1.RefreshReport();

reportViewer1 is the control on my form. GetUsers() is a method that queries the database for a list of users and returns a DataTable. This DataTable could be populated from anything - XML, CSV, Oracle, etc. Since I know the DataSet isn’t null, I just set the names on each so that they are different. Olympus is my personal library of tools, and Olympus.Reporting.Report is a class that accepts the dataset as a parameter, and then using that dataset allows my to build an rdlc. In this case we build it as a System.IO.MemoryStream to pass in to LoadReportDefinition(). Also notice how we added the datasource.  This is important because you must pass the name of the DataSet and the table of data itself. This is why we did the naming above and the DataTable must belong to a DataSet.Now that we understand the basics of how Microsoft.Reporting works, you can report on anything, as long as you can put the data into a System.Data.DataTable. Here is how I write the rdlc xml to a string, along with the method of converting the string to a MemoryStream. This class is in the “proof of concept” phase and currently uses public properties to change things like background color, spacing, etc.  Some values are hard coded and others are accepted through the properties.  Eventually, nothing would be hard coded. There are better ways to do this, but that is for another discussion.

public System.IO.MemoryStream GetRdlcStream()  
{  
 byte[] rdlBytes = System.Text.Encoding.UTF8.GetBytes(GetRdlcString());
 return new System.IO.MemoryStream(rdlBytes);  
}
public string GetRdlcString()  
{  
 System.Text.StringBuilder result = new System.Text.StringBuilder();  
 System.IO.StringWriter writer = new System.IO.StringWriter(result);
 XmlTextWriter _rdl = new XmlTextWriter(writer);
 DataTable data = _data.Tables[0];
 _rdl.Formatting = Formatting.Indented;  
 _rdl.Indentation = 3;  
 _rdl.Namespaces = true;
 int _columns = data.Columns.Count;
 try  
 {  
 	_rdl.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");
 	//Report _rdl.WriteStartElement("", "Report", "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition");  
 	#region RDL Header Section (settings)
 	_rdl.WriteStartElement("", "BottomMargin", null);  
 	_rdl.WriteString("0.5in");  
 	_rdl.WriteEndElement();
 	_rdl.WriteStartElement("", "RightMargin", null);  
 	_rdl.WriteString("0.5in");  
 	_rdl.WriteEndElement();
 	_rdl.WriteStartElement("", "InteractiveWidth", null);  
 	_rdl.WriteString("8.5in");  
 	_rdl.WriteEndElement();
 	#endregion
 	#region Data Set
 	// DataSource element  
 	_rdl.WriteStartElement("DataSources");  
 	_rdl.WriteStartElement("DataSource");  
 	_rdl.WriteAttributeString("Name", null, data.DataSet.DataSetName);  
 	_rdl.WriteStartElement("ConnectionProperties");  
 	_rdl.WriteElementString("DataProvider", "Oracle");  
 	_rdl.WriteElementString("ConnectString", "ItsaSecret");  
 	_rdl.WriteElementString("IntegratedSecurity", "true");  
 	_rdl.WriteEndElement();  // ConnectionProperties  
 	_rdl.WriteEndElement();  // DataSource  
 	_rdl.WriteEndElement();  // DataSources
 	// DataSet element  
 	_rdl.WriteStartElement("DataSets");  
 	_rdl.WriteStartElement("DataSet");  
 	_rdl.WriteAttributeString("Name", null, data.DataSet.DataSetName);
 	// Query element  
 	_rdl.WriteStartElement("Query");  
 	_rdl.WriteElementString("DataSourceName", data.DataSet.DataSetName);  
 	_rdl.WriteElementString("CommandType", "Text");  
 	_rdl.WriteElementString("CommandText", "wouldntyouliketoknow");  
 	_rdl.WriteElementString("Timeout", "30");  
 	_rdl.WriteEndElement(); // Query
 	// Fields elements  
 	_rdl.WriteStartElement("Fields");
 	for (int x = 0; x < _columns; x++)  
 	{  
 		_rdl.WriteStartElement("Field");  
 		_rdl.WriteAttributeString("Name", null, data.Columns[x].ColumnName);  
 		_rdl.WriteElementString("DataField", null, data.Columns[x].ColumnName);  
 		_rdl.WriteEndElement(); // Field  
 	}
 	// End previous elements  
 	_rdl.WriteEndElement();
 	// Fields  
 	_rdl.WriteEndElement();
 	// DataSet  
 	_rdl.WriteEndElement();
 	// DataSets
 	#endregion
 	_rdl.WriteStartElement("", "Body", null); _rdl.WriteStartElement("", "ReportItems", null);
 	_rdl.WriteStartElement("", "Table", null);   _rdl.WriteAttributeString("Name", "table1");
 	#region Footer  
 	_rdl.WriteStartElement("", "Footer", null);   _rdl.WriteStartElement("", "TableRows", null);  
 	_rdl.WriteStartElement("", "TableRow", null);   _rdl.WriteStartElement("", "TableCells", null);
 	int _footerIndex = _columns * 3; //write all the footer items  
 	for (int x = 0; x < _columns; x++)  
 	{  
 		int _zindex = (_footerIndex + x);  
 		string _name = "textbox" + _zindex;
 		string _bgColor = System.Drawing.ColorTranslator.ToHtml(BackgroundColorFooter);  
 		string _textColor = System.Drawing.ColorTranslator.ToHtml(TextColorFooter);
 		AddCell(_rdl, _name, _bgColor, _textColor, TextAlignFooter, 0, _zindex, "");  
 	}
 	#endregion
 	//end TableCells  
 	_rdl.WriteEndElement();
 	_rdl.WriteStartElement("", "Height", null);  
 	_rdl.WriteString("0.25in");  
 	_rdl.WriteEndElement();
 	//end TableRow  
 	_rdl.WriteEndElement();
 	//end TableRows  
 	_rdl.WriteEndElement();
 	//end footer  
 	_rdl.WriteEndElement();
 	_rdl.WriteStartElement("", "Top", null);  
 	_rdl.WriteString("0.625in");  
 	_rdl.WriteEndElement();
 	#region Details
 	_rdl.WriteStartElement("", "Details", null);  
 	_rdl.WriteStartElement("", "TableRows", null);  
 	_rdl.WriteStartElement("", "TableRow", null);  
 	_rdl.WriteStartElement("", "TableCells", null);
 	int _detailIndex = _columns * 2;
 	//write all the detail items  
 	for (int x = 0; x < _columns; x++)  
 	{  
 		int _zindex = (_detailIndex + x);  
 		string _name = "textbox" + _zindex;  
 		string _value = "=Fields!" + data.Columns[x].ColumnName + ".Value";
 		//Alternate the colors by row  
 		string _bgcolor = "=iif(RowNumber(Nothing) Mod 2, \"" + System.Drawing.ColorTranslator.ToHtml(BackgroundColorBody) + "\", \"" + System.Drawing.ColorTranslator.ToHtml(BackgroundColorBodyAlternate) + "\")";  
 		string _textcolor = "=iif(RowNumber(Nothing) Mod 2, \"" + System.Drawing.ColorTranslator.ToHtml(TextColorBody) + "\", \"" + System.Drawing.ColorTranslator.ToHtml(TextColorBodyAlternate) + "\")";
 		AddCell(_rdl, _name, _bgcolor, _textcolor, TextAlignFooter, 0, _zindex, _value);  
 	}
 	//end TableCells  
 	_rdl.WriteEndElement();
 	_rdl.WriteStartElement("", "Height", null);  
 	_rdl.WriteString("0.25in");  
 	_rdl.WriteEndElement();
 	//end TableRow  
 	_rdl.WriteEndElement();
 	//end TableRows  
 	_rdl.WriteEndElement();
 	//end detail  
 	_rdl.WriteEndElement();
 	#endregion
 	#region Header
 	_rdl.WriteStartElement("", "Header", null);  
 	_rdl.WriteStartElement("", "TableRows", null);  
 	_rdl.WriteStartElement("", "TableRow", null);  
 	_rdl.WriteStartElement("", "TableCells", null);
 	int _headerIndex = 1;
 	//write all the header items  
 	for (int x = 0; x < _columns; x++)  
 	{  
 		int _zindex = (_headerIndex + x);  
 		string _name = "textbox" + _zindex;
 		string _value = data.Columns[x].ColumnName;
 		//Allow the user to override and use the default column name by passing in an empty string  
 		if (x < _headers.Length && !string.IsNullOrEmpty(_headers[x]))  
 		{  
 			_value = _headers[x];  
 		}
 		string _bgColor = System.Drawing.ColorTranslator.ToHtml(BackgroundColorHeader);  
 		string _textColor = System.Drawing.ColorTranslator.ToHtml(TextColorHeader);
 		AddCell(_rdl, _name, _bgColor, _textColor, TextAlignFooter, 700, _zindex, _value);  
 	}
 	//end TableCells  
 	_rdl.WriteEndElement();
 	_rdl.WriteStartElement("", "Height", null);   _rdl.WriteString("0.25in");  
 	_rdl.WriteEndElement();
 	//end TableRow  
 	_rdl.WriteEndElement();
 	//end TableRows  
 	_rdl.WriteEndElement();
 	//end detail  
 	_rdl.WriteEndElement();
 	#endregion
 	#region Table Settings
 	_rdl.WriteStartElement("", "TableColumns", null);
 	for (int x = 0; x < _columns; x++)  
 	{  
 		_rdl.WriteStartElement("", "TableColumn", null);  
 		_rdl.WriteStartElement("", "Width", null);  
 		_rdl.WriteString("2.16667in");  
 		_rdl.WriteEndElement();
 		//end TableColumn  
 		_rdl.WriteEndElement();  
 	}
 	//end TableColumns  
 	_rdl.WriteEndElement();
 	_rdl.WriteStartElement("", "Height", null);   _rdl.WriteString("0.75in");  
 	_rdl.WriteEndElement();
 	#endregion
 	//end Table  
 	_rdl.WriteEndElement(); //end ReportItems  
 	_rdl.WriteEndElement();
 	_rdl.WriteStartElement("", "Height", null);  
 	_rdl.WriteString("2in");  
 	_rdl.WriteEndElement(); //end Body  
 		_rdl.WriteEndElement();
 	#region Document Footer Section (settings)
 	_rdl.WriteStartElement("", "LeftMargin", null);  
 	_rdl.WriteString("0.5in");  
 		_rdl.WriteEndElement();
 	_rdl.WriteStartElement("", "Width", null);  
 	_rdl.WriteString("8.66667in");  
 	_rdl.WriteEndElement();
 	_rdl.WriteStartElement("", "InteractiveHeight", null);  
 	_rdl.WriteString("11in");  
 	_rdl.WriteEndElement();
 	_rdl.WriteStartElement("", "Language", null);  
 	_rdl.WriteString("en-US");  
 	_rdl.WriteEndElement();
 	_rdl.WriteStartElement("", "TopMargin", null);  
 	_rdl.WriteString("0.5in");  
 	_rdl.WriteEndElement();
 	#endregion
 	//End of report  
 	_rdl.WriteEndElement();
 	//write the document to the memory stream  
 	_rdl.Flush(); return result.ToString();  
 }  
 catch  
 {  
 	throw;  
 }  
 finally  
 {  
 	//close the document  
 	_rdl.Close();  
 }  
}
private static void AddCell(XmlTextWriter _writer, string name, string backgroundColor, string textColor,   string textAlign, int fontWeight, int ZIndex, string value)  
{  
 _writer.WriteStartElement("", "TableCell", null);  
 _writer.WriteStartElement("", "ReportItems", null);
 _writer.WriteStartElement("", "Textbox", null);  
 _writer.WriteAttributeString("Name", name);
 _writer.WriteStartElement("", "ZIndex", null);  
 _writer.WriteString(ZIndex.ToString(CultureInfo.InvariantCulture));  
 _writer.WriteEndElement();
 _writer.WriteStartElement("", "Style", null);
 _writer.WriteStartElement("", "TextAlign", null);  
 _writer.WriteString(textAlign);  
 _writer.WriteEndElement();
 _writer.WriteStartElement("", "Color", null);  
 _writer.WriteString(textColor);  
 _writer.WriteEndElement();
 _writer.WriteStartElement("", "BackgroundColor", null);  
 _writer.WriteString(backgroundColor);  
 _writer.WriteEndElement();
 _writer.WriteStartElement("", "FontWeight", null);  
 if (fontWeight == 0)  
 {  
 	_writer.WriteString("100");  
 }  
 else  
 {  
 	_writer.WriteString(fontWeight.ToString(CultureInfo.InvariantCulture));  
 }  
 _writer.WriteEndElement();
 _writer.WriteStartElement("", "PaddingLeft", null);  
 _writer.WriteString("2pt");  
 _writer.WriteEndElement();
 _writer.WriteStartElement("", "PaddingBottom", null);  
 _writer.WriteString("2pt");  
 _writer.WriteEndElement();
 _writer.WriteStartElement("", "PaddingRight", null);  
 _writer.WriteString("2pt");  
 _writer.WriteEndElement();
 _writer.WriteStartElement("", "PaddingTop", null);  
 _writer.WriteString("2pt");  
 _writer.WriteEndElement();
 //end Style  
 _writer.WriteEndElement();
 _writer.WriteStartElement("", "CanGrow", null);  
 _writer.WriteString("true");  
 _writer.WriteEndElement();
 _writer.WriteStartElement("", "Value", null);  
 _writer.WriteString(value);  
 _writer.WriteEndElement();
 //end TextBox  
 _writer.WriteEndElement();
 //End ReportItems  
 _writer.WriteEndElement();
 //end TableCell  
 _writer.WriteEndElement();  
}

With a little exploration and some trial and error, you can pick apart how these RDLC’s are built, and you can even add charts, graphics, and other cool features to your report. The best part is you don’t have to have the Microsoft Reporting Engine to use the ReportViewer, and you can use it with any form of tabular data.