Click here to Skip to main content
15,867,330 members
Articles / Programming Languages / C#

Dynamic Reports with Reporting Services

Rate me:
Please Sign up or sign in to vote.
4.90/5 (22 votes)
8 Feb 2010GPL33 min read 123.4K   4.8K   40   37
This article demonstrates how to generate SSRS reports based on an input DataTable.

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:

C#
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.

C#
#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:

C#
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).

C#
// 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.

C#
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.

C#
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

  • 8th February, 2010: Initial post

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)


Written By
Software Developer (Junior)
Italy Italy
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionHow to add a rectangle and Image in this report Pin
Member 1407435629-Apr-20 0:57
Member 1407435629-Apr-20 0:57 
QuestionNeed a code for latest RDLC report generation - Urgent Pin
Member 141752697-Mar-19 23:29
Member 141752697-Mar-19 23:29 
QuestionUrgent Need to generate a 2010 version report service code Pin
Member 141752697-Mar-19 21:44
Member 141752697-Mar-19 21:44 
QuestionLatest RDLC Generation Pin
Member 141752397-Mar-19 21:00
Member 141752397-Mar-19 21:00 
QuestionAdd Space and Numbers Pin
Member 1332960323-Aug-17 20:12
Member 1332960323-Aug-17 20:12 
QuestionHow to implement this code Pin
sazan8021-Aug-16 17:35
sazan8021-Aug-16 17:35 
AnswerRe: How to implement this code Pin
kLeZ-hAcK27-Sep-16 1:35
kLeZ-hAcK27-Sep-16 1:35 
QuestionAdding Header Information Pin
Prajwal Bhat26-Nov-15 2:45
professionalPrajwal Bhat26-Nov-15 2:45 
QuestionHow I can use this code to add multi datatable at runtime from select database Pin
TaherAlqadsi16-Nov-15 1:36
TaherAlqadsi16-Nov-15 1:36 
Questionthis works! Pin
Rochelle Velasquez-Macazo9-Nov-15 10:51
Rochelle Velasquez-Macazo9-Nov-15 10:51 
QuestionCan we do the same in SSRS 2008 R2 Pin
vipul200819-Aug-15 22:26
vipul200819-Aug-15 22:26 
AnswerRe: Can we do the same in SSRS 2008 R2 Pin
vipul200819-Aug-15 22:36
vipul200819-Aug-15 22:36 
QuestionHow can I add grouping on columns using the same code Pin
Member 1153438624-Mar-15 3:01
Member 1153438624-Mar-15 3:01 
QuestionColumn Header Widths Pin
Member 462571318-Jul-14 7:07
Member 462571318-Jul-14 7:07 
QuestionGrouping of Rows Pin
aggaton20-Dec-13 10:35
aggaton20-Dec-13 10:35 
QuestionBrilliant and oh, so very useful! Pin
Member 462571317-Dec-13 8:01
Member 462571317-Dec-13 8:01 
GeneralTruly Genius Pin
Britto Antony23-Oct-13 23:34
Britto Antony23-Oct-13 23:34 
QuestionPage throws Error Pin
davita-ostati16-Sep-13 4:44
davita-ostati16-Sep-13 4:44 
QuestionDynamic Reports with Reporting Services Pin
xanilkumar25-Jul-13 21:43
xanilkumar25-Jul-13 21:43 
QuestionHow to add parameters to this report Pin
MithunKhadloya4-Jun-13 21:47
MithunKhadloya4-Jun-13 21:47 
AnswerRe: How to add parameters to this report Pin
coded0075-Jun-13 5:01
professionalcoded0075-Jun-13 5:01 
GeneralRe: How to add parameters to this report Pin
MithunKhadloya5-Jun-13 6:44
MithunKhadloya5-Jun-13 6:44 
QuestionDisplay data of multiple tables Pin
GauravDac20-Feb-13 3:31
GauravDac20-Feb-13 3:31 
AnswerRe: Display data of multiple tables Pin
kLeZ-hAcK20-Feb-13 4:10
kLeZ-hAcK20-Feb-13 4:10 
GeneralRe: Display data of multiple tables Pin
GauravDac20-Feb-13 20:13
GauravDac20-Feb-13 20:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.