Click here to Skip to main content
15,867,756 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

 
GeneralMy vote of 5 Pin
sudarvendhan16-Oct-12 19:22
sudarvendhan16-Oct-12 19:22 
QuestionImage Pin
Shargon_855-Jun-12 20:34
Shargon_855-Jun-12 20:34 
GeneralMy vote of 5 Pin
Reza Ahmadi21-Apr-12 1:37
Reza Ahmadi21-Apr-12 1:37 
GeneralFor some people Pin
ltlsoft21-Mar-12 1:57
ltlsoft21-Mar-12 1:57 
For those who can't get the code to work in VS 2008 or after that, add

ReportViewer1.RefreshReport();

Other than that the code is just beautiful. Thank you kLeZ-hAcK for sharing it.
QuestionThanks to all for your comments Pin
kLeZ-hAcK23-Feb-12 23:56
kLeZ-hAcK23-Feb-12 23:56 
AnswerRe: Thanks to all for your comments Pin
vipul200819-Aug-15 22:25
vipul200819-Aug-15 22:25 
Questiongreat work Pin
sethy12323-Feb-12 23:39
sethy12323-Feb-12 23:39 
GeneralGood work Pin
c#ivan14-Jun-11 4:59
c#ivan14-Jun-11 4:59 
GeneralMy vote of 5 Pin
raja_krish31-May-11 10:07
raja_krish31-May-11 10:07 
GeneralMy vote of 5 Pin
Pranay Rana29-Jul-10 23:56
professionalPranay Rana29-Jul-10 23:56 
GeneralGood job Pin
a codeproject fan9-Jun-10 6:27
a codeproject fan9-Jun-10 6:27 
GeneralCode is not accessible Pin
kaminm8-Feb-10 13:48
professionalkaminm8-Feb-10 13:48 

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.