Dynamic Reports with Reporting Services

• Download source #1 - 10.18 KB

Introduction

The article explains a simple method to generate simple reports using Reporting Services 2005 and a few XML on C#. SSRS does not allow us to generate dynamic tables based on the content of a C# data container object, but sometimes it is useful to create a report programmatically based on DataTable (or DataSet) structure.   

The Code

My need was generating reports from a generic DataTable, because I cannot access the database directly, so I've written this code.
First of all I studied .rdl files, in fact this file format is simple XML with a few required tags you require to know.
I used XmlWriter class to write the XML, and ReportViewer's LocalReport mode has a method LoadReportDefinition that accepts a Stream object, that is our XML produced with XmlWriter.
These are the private variables that are required for my class:

private CultureInfo ci = new CultureInfo("en-US");
private string nsRd = "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
private string ns = 
	"http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition";

The CultureInfo object serves because my default is it-IT, and Reports require a dot as the float decimal separator.
Other two strings are the namespaces required for the report in order to be validated and accepted by ReportViewer.
XmlWriter class requires, in my case, a StringBuilder and an XmlSettings object, this is how I've set this last.

#region Settings
XmlWriterSettings settings = new XmlWriterSettings();
settings.CheckCharacters = true;
settings.CloseOutput = true;
settings.Encoding = Encoding.UTF8;
settings.Indent = true;
settings.IndentChars = "\t";
settings.NewLineChars = "\r\n";
settings.NewLineHandling = NewLineHandling.Replace;
settings.NewLineOnAttributes = false;
settings.OmitXmlDeclaration = false;
#endregion

Writing XML with XmlWriter is quite simple and does not require much explanation, but setting the columns' width based on passed data requires it.
This is the method that gets the size of a string and I used it in my code as below:

private SizeF GetDynamicSize(string s)
{
	Font f = new Font(FontFamily.GenericSansSerif, 10);
	Bitmap bmp = new Bitmap(1, 1);
	Graphics g = Graphics.FromImage(bmp);
	g.PageUnit = GraphicsUnit.Millimeter;
	SizeF ret = SizeF.Empty;
	ret = g.MeasureString(s, f);
	g.Dispose();
	return ret;
}

//...

// This is the TableColumns section which pretends the same number of tags
// as columns of data table, and requires the width of each column
// This is how I've implemented (dt variable is the DataTable object with data inside):

writer.WriteStartElement("TableColumns");
{ // These brackets are only for more readability of the code. 
  // XML is folded and my code too.
	for (int i = 0; i < dt.Columns.Count; i++)
	{
		writer.WriteStartElement("TableColumn");
		{
			DataColumn dc = dt.Columns[i];
			float sizeWidthComputed = 0.0F;
			float RowMaxLength = 
				GetDynamicSize(dt.Rows[0][i].ToString()).Width / 10;
			float HeaderMaxLength = 
				(GetDynamicSize(dc.ColumnName).Width / 10) + 0.2F;
			foreach (DataRow row in dt.Rows)
			{
				float rowSizeWidth = 
				    GetDynamicSize(row[i].ToString()).Width / 10;
				if (rowSizeWidth > RowMaxLength)
					RowMaxLength = rowSizeWidth;
			}

			if (RowMaxLength > HeaderMaxLength)
				if (RowMaxLength > MaxWidth)
					sizeWidthComputed = MaxWidth;
				else
					sizeWidthComputed = RowMaxLength;
			else
				sizeWidthComputed = HeaderMaxLength;

			writer.WriteElementString("Width", 
				(sizeWidthComputed).ToString(ci) + "cm");
		}
		writer.WriteEndElement();
	}
}
writer.WriteEndElement();

Another point of interest of my code is setting dataset and datasource in the report XML. Obviously report requires only one dataset and datasource because one datatable is passed, but you can implement more than one, but you have to make a distinction between tables.
The dataset is important and is more important than dataset and datasource has the same name as the ReportDataSource object required for using the code (in the below named section).

// dsName is the datasource name and in fact it is the name you give 
// at the data source in report viewer control, and dt is the datatable with data inside.
writer.WriteStartElement("DataSets");
{
	writer.WriteStartElement("DataSet");
	writer.WriteAttributeString("Name", dsName);
	{
		writer.WriteStartElement("Fields");
		{
			for (int i = 0; i < dt.Columns.Count; i++)
			{
				writer.WriteStartElement("Field");
				writer.WriteAttributeString
				("Name", dt.Columns[i].ColumnName);
				{
					writer.WriteElementString
					("DataField", dt.Columns[i].ColumnName);
					writer.WriteElementString
					("rd", "TypeName", nsRd, 
					dt.Columns[i].DataType.ToString());
				}
				writer.WriteEndElement();
			}
		}
		writer.WriteEndElement();

		writer.WriteStartElement("Query");
		{
			writer.WriteElementString("DataSourceName", dsName);
			writer.WriteElementString
				("CommandText", ""); // the command text in 
				// query tag may be blank because 
				// we don't have a query at all. This isn't wrong.
			writer.WriteElementString("rd", 
				"DataSourceName", nsRd, "true");
		}
		writer.WriteEndElement();
	}
	writer.WriteEndElement();
}
writer.WriteEndElement();

// The DataSource section
writer.WriteStartElement("DataSources");
{
	writer.WriteStartElement("DataSource");
	{
		writer.WriteAttributeString("Name", dsName);
		writer.WriteElementString("DataSourceReference", dsName);
	}
	writer.WriteEndElement();
}
writer.WriteEndElement();

Rest of the code is building the other XML and setting paddings (in pt) and dimensions, positions and more (in cm or inches if you want). You can get all that in the code, but the most important functions are these.

Oh most important is that you could name details values in textboxes contained in tablecells as "=Fields!{0}.Value", the same as below.

CellColors colors = null; // this is a custom object I made, 
	// fell free to recreate and improve as you want, but make me aware of that.
// Sorry for the italian names :-)
switch (sezione) // Translated "section" (I mean a section of the table)
{
	case SezioneTabella.Header: // This is a custom enum, "TableSection" translated.
		{
			nomeSezione = "Header";
			templateValore = "{0}";
			colors = new CellColors(Color.Black, Color.White);
			break;
		}
	case SezioneTabella.Details:
		{
			nomeSezione = "Details";
			templateValore = "=Fields!{0}.Value";
			break;
		}
	case SezioneTabella.Footer:
		{
			nomeSezione = "Footer";
			templateValore = "{0}";
			break;
		}
}
writer.WriteStartElement(nomeSezione);
{
	if (sezione == SezioneTabella.Header)
		writer.WriteElementString("RepeatOnNewPage", "true");
	writer.WriteStartElement("TableRows");
	{
		writer.WriteStartElement("TableRow");
		{
			writer.WriteElementString
				("Height", height.ToString(ci) + "cm");
			writer.WriteStartElement("TableCells");
			{
				for (int i = 0; i < dt.Columns.Count; i++)
				{
					writer.WriteStartElement("TableCell");
					{
						writer.WriteStartElement
							("ReportItems");
						{
							valore = String.Format
							(templateValore, 
							dt.Columns[i].ColumnName);
							// This method generates 
							// a textbox for the 
							// report, it is in the 
							// code and below.
							GeneraTextBox(writer, 
							"textbox" + nomeSezione + 
							i, RectangleF.Empty, 
							padding, colors, valore);
						}
						writer.WriteEndElement();
					}
					writer.WriteEndElement();
				}
			}
			writer.WriteEndElement();
		}
		writer.WriteEndElement();
	}
	writer.WriteEndElement();
}
writer.WriteEndElement();

//Generation of a text box for the report table cell.
//Padding and CellColors are my custom objects, in the zip
private void GeneraTextBox(XmlWriter writer, string textboxName, 
	RectangleF dimensioni, Padding padding, CellColors colors, string value)
{
	writer.WriteStartElement("Textbox");
	writer.WriteAttributeString("Name", textboxName);
	{
		writer.WriteElementString("rd", "DefaultName", nsRd, textboxName);
		if (dimensioni != RectangleF.Empty)
		{
			writer.WriteElementString("Top", 
				dimensioni.Top.ToString(ci) + "cm");
			writer.WriteElementString("Left", 
				dimensioni.Left.ToString(ci) + "cm");
			writer.WriteElementString("Width", 
				dimensioni.Width.ToString(ci) + "cm");
			writer.WriteElementString("Height", 
				dimensioni.Height.ToString(ci) + "cm");
		}
		writer.WriteElementString("CanGrow", "true");
		writer.WriteElementString("Value", value);
		if (padding != null)
		{
			writer.WriteStartElement("Style");
			{
				writer.WriteStartElement("BorderStyle");
				{
					writer.WriteElementString
						("Default", "Solid");
				}
				writer.WriteEndElement();

				if (colors != null)
				{
					writer.WriteElementString
					("Color", colors.ForegroundColor.Name);
					writer.WriteElementString
					("BackgroundColor", 
					colors.BackgroundColor.Name);
				}

				writer.WriteElementString("PaddingLeft", 
					padding.Left.ToString(ci) + "pt");
				writer.WriteElementString("PaddingRight", 
					padding.Right.ToString(ci) + "pt");
				writer.WriteElementString("PaddingTop", 
					padding.Top.ToString(ci) + "pt");
				writer.WriteElementString("PaddingBottom", 
					padding.Bottom.ToString(ci) + "pt");
			}
			writer.WriteEndElement();
		}
	}
	writer.WriteEndElement();

//My custom objects

public enum SezioneTabella
{
	Header,
	Details,
	Footer
}

public class CellColors
{
	public CellColors(Color bg, Color fore)
	{
		this.bg = bg;
		this.fore = fore;
	}
	private Color bg = Color.Empty;
	private Color fore = Color.Empty;

	public Color BackgroundColor { get { return bg; } }
	public Color ForegroundColor { get { return fore; } }
}

public class Padding
{
	public Padding(float Top, float Left, float Bottom, float Right)
	{
		TopLeft = new PointF(Left, Top);
		BottomRight = new PointF(Right, Bottom);
	}

	private PointF TopLeft { get; set; }
	private PointF BottomRight { get; set; }

	public float Top { get { return TopLeft.Y; } }
	public float Left { get { return TopLeft.X; } }
	public float Bottom { get { return BottomRight.Y; } }
	public float Right { get { return BottomRight.X; } }
}

I think this is pretty much all of the highlights I have to do with this code, you can try more or you can use the code, that generates a simple table of nearly any dataset.
An advertise, DataSet and DataSource names have to be only Letters, no special characters and no other chars than letters, I've written below.

Using the Code

All you have to do with this simple class is instantiate a ReportDataSource object and pass it to the ReportGenerator, then call GenerateReport() method as argument for the LoadReportDefinition method of the ReportViewer control.

DataTable data = new DataTable(); 	//This would be your own DataTable, 
				//none particular features requested.
string dsName = "", displayName = ""; // These are your display name 
				// and data source name,
//display can be anything you want but data source only accepts Letter characters, 
//no spaces, no underscores,
//none other than Letters (or numbers but this is not the case).
//I give you a little snippet for the data source name:
/*
char[] ctext = stringToCleanForDataSource.ToCharArray();
for (int i = 0; i < ctext.Length; i++)
        if (Char.IsLetter(ctext[i])) dsName += ctext[i];
*/
ReportDataSource ds = new ReportDataSource(dsName, data);
ReportGenerator gen = new ReportGenerator(data, dsName);
ReportViewer1.Reset();
ReportViewer1.LocalReport.DataSources.Add(ds);
ReportViewer1.LocalReport.DisplayName = displayName;
ReportViewer1.LocalReport.LoadReportDefinition(gen.GeneraReport());

The complete ReportGenerator class is in the zip file and can be downloaded from the link at the top of this article.

History

• 8^th February, 2010: Initial post