Reporting Services: Creating A Dynamic Column Tablix Report using RDL Generator

One approach I’ve learned just recently in creation of Reports with Dynamic column is by using a Streamed report created programmatically using a RDL Generator class as describe in this link. This tutorial will teach you how to create a dynamic column tablix report rdlc using the RDL Generator.

First is create the RDL Generator Class, this will serve as the creator of the rdl in xml format.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.IO;

namespace SampleRDLGenerator
{
    public class RDLGenerator
    {
        public RDLGenerator()
        {
        }

        //Enter Methods Here
    }
}

Add method to Create the document and the report base

public XmlDocument CreateDocument()
{
	XmlDocument doc = new XmlDocument();

	var sb = new StringBuilder();
	sb.Append("<report ");
	sb.AppendFormat("xmlns:rd=\"{0}\" ", "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner");
	sb.AppendFormat("xmlns=\"{0}\">", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition");
	sb.Append("</Report>");
	string xmlData = sb.ToString();
	doc.Load(new StringReader(xmlData));

	return doc;
}
public XmlElement CreateReportBase(XmlDocument doc)
{
	XmlElement report;
	XmlElement dataSources;
	XmlElement dataSource;
	XmlElement connectionProperties;

	report = (XmlElement)doc.FirstChild;
	AddElement(report, "AutoRefresh", "0");
	AddElement(report, "ConsumeContainerWhitespace", "true");

	dataSources = AddElement(report, "DataSources", null);
	dataSource = AddElement(dataSources, "DataSource", null);
	AddAttribute(dataSource, doc, "Name", "SandsReportReportModels");

	connectionProperties = AddElement(dataSource, "ConnectionProperties", null);
	AddElement(connectionProperties, "DataProvider", "System.Data.DataSet");
	AddElement(connectionProperties, "ConnectString", "/* Local Connection */");

	return report;
}

Now add method that will create the data set from a list of field names

public void CreateReportDataSet(XmlElement report, XmlDocument doc, string dataSetName, List<ReportColumn> propertyList)
{
	XmlElement dataSets;
	XmlElement dataSet;
	XmlElement fields;
	XmlElement query;

	//DataSets element
	dataSets = AddElement(report, "DataSets", null);
	dataSet = AddElement(dataSets, "DataSet", null);
	AddAttribute(dataSet, doc, "Name", dataSetName);

	fields = AddElement(dataSet, "Fields", null);

	foreach (var property in propertyList.OrderBy(x => x.Order))
	{
		CreateDataSetFields(fields, doc, property.Name);
	}

	//Query
	query = AddElement(dataSet, "Query", null);
	AddElement(query, "DataSourceName", "ReportModels");
	AddElement(query, "CommandText", "/* Local Query */");
}

public void CreateDataSetFields(XmlElement fields, XmlDocument doc, string fieldName)
{
	XmlElement field;
	field = AddElement(fields, "Field", null);
	AddAttribute(field, doc, "Name", fieldName);
	AddElement(field, "DataField", fieldName);
	AddRdElement(field, "rd:TypeName", "System.String");
}

Then create the report body

public XmlElement CreateReportBody(XmlElement report, XmlDocument doc, string dataSetName, List<ReportColumn> propertyList)
{
	XmlElement tablix;
	XmlElement tablixBody;
	XmlElement tablixColumns;
	XmlElement tablixRows;
	XmlElement tablixCellHeaders;
	XmlElement tablixCellRows;
	XmlElement reportItems;
	int columnCount = propertyList.Count;

	//Create Report Body And Tablix
	reportItems = CreateReportItems(report, doc);
	tablix = CreateTablix(reportItems, doc, dataSetName, propertyList);

	//Create Tablix Columns And Rows
	tablixBody = AddElement(tablix, "TablixBody", null);
	tablixColumns = AddElement(tablixBody, "TablixColumns", null);
	foreach (var property in propertyList.OrderBy(x => x.Order))
	{
		CreateColumn(tablixColumns, property.Width);
	}
	tablixRows = AddElement(tablixBody, "TablixRows", null);
	tablixCellHeaders = CreateRow(tablixRows);
	tablixCellRows = CreateRow(tablixRows);
	foreach (var property in propertyList.OrderBy(x => x.Order))
	{
		CreateHeaderCell(tablixCellHeaders, doc, property.Name, property.HeaderName);
		CreateRowCell(tablixCellRows, doc, property.Name);
	}

	return report;
}
public XmlElement CreateReportItems(XmlElement report, XmlDocument doc)
{
	XmlElement body;
	XmlElement reportItems;
	XmlElement page;

	page = AddElement(report, "Page", null);
	//landscape
	AddElement(page, "PageHeight", "8.5in");
	AddElement(page, "PageWidth", "11in");
	AddElement(page, "LeftMargin", "1in");
	AddElement(page, "RightMargin", "1in");
	AddElement(page, "TopMargin", "1in");
	AddElement(page, "BottomMargin", "1in");
	AddElement(page, "Style", null);

	AddElement(report, "Width", "11in");
	body = AddElement(report, "Body", null);
	AddElement(body, "Height", "1.85546in");
	reportItems = AddElement(body, "ReportItems", null);

	return reportItems;
}
public XmlElement CreateTablix(XmlElement reportItems, XmlDocument doc, string dataSetName, List<ReportColumn> propertyList)
{
	XmlElement tablix;
	XmlElement tablixColumnHierarchy;
	XmlElement tablixMembers;
	XmlElement tablixMember;
	XmlElement tablixRowHierarchy;
	XmlElement group;
	XmlElement tablixMembersNested;

	tablix = AddElement(reportItems, "Tablix", null);
	AddAttribute(tablix, doc, "Name", "Tablix1");
	AddElement(tablix, "DataSetName", dataSetName);
	AddElement(tablix, "Top", "0.5in");
	AddElement(tablix, "Left", "0.5in");
	AddElement(tablix, "Height", "0.5in");
	AddElement(tablix, "Width", "3in");

	tablixColumnHierarchy = AddElement(tablix, "TablixColumnHierarchy", null);
	tablixMembers = AddElement(tablixColumnHierarchy, "TablixMembers", null);
	foreach (var property in propertyList.OrderBy(x => x.Order))
	{
		CreateTablixMember(tablixMembers, property.Name);
	}

	tablixRowHierarchy = AddElement(tablix, "TablixRowHierarchy", null);
	tablixMembers = AddElement(tablixRowHierarchy, "TablixMembers", null);
	tablixMember = AddElement(tablixMembers, "TablixMember", null);
	AddElement(tablixMember, "KeepWithGroup", "After");
	AddElement(tablixMember, "KeepTogether", "true");

	tablixMember = AddElement(tablixMembers, "TablixMember", null);
	AddElement(tablixMember, "DataElementName", "Detail_Collection");
	AddElement(tablixMember, "DataElementOutput", "Output");
	AddElement(tablixMember, "KeepTogether", "true");

	group = AddElement(tablixMember, "Group", null);
	AddAttribute(group, doc, "Name", "Table1_Details_Group");
	AddElement(group, "DataElementName", "Detail");

	tablixMembersNested = AddElement(tablixMember, "TablixMembers", null);
	AddElement(tablixMembersNested, "TablixMember", null);

	return tablix;
}
public XmlElement CreateColumn(XmlElement tablixColumns, string width)
{
	XmlElement tablixColumn;

	tablixColumn = AddElement(tablixColumns, "TablixColumn", null);
	AddElement(tablixColumn, "Width", width);

	return tablixColumn;
}
public XmlElement CreateRow(XmlElement tablixRows)
{
	XmlElement tablixRow;
	XmlElement tablixCells;

	tablixRow = AddElement(tablixRows, "TablixRow", null);
	AddElement(tablixRow, "Height", "0.27373in");
	tablixCells = AddElement(tablixRow, "TablixCells", null);

	return tablixCells;
}
public void CreateTablixMember(XmlElement tablixMembers, string propertyname)
{
	XmlElement tablixMember;
	tablixMember = AddElement(tablixMembers, "TablixMember", null);
}

the next methods create the cells for the tablix

public void CreateRowCell(XmlElement tablixCells, XmlDocument doc, string fieldname)
{
	XmlElement tablixCell;
	XmlElement cellContents;
	XmlElement textbox;
	XmlElement paragraphs;
	XmlElement paragraph;
	XmlElement textRuns;
	XmlElement textRun;
	XmlElement style;
	XmlElement border;

	tablixCell = AddElement(tablixCells, "TablixCell", null);
	cellContents = AddElement(tablixCell, "CellContents", null);
	textbox = AddElement(cellContents, "Textbox", null);
	AddAttribute(textbox, doc, "Name", fieldname);
	AddElement(textbox, "KeepTogether", "true");
	paragraphs = AddElement(textbox, "Paragraphs", null);
	paragraph = AddElement(paragraphs, "Paragraph", null);
	textRuns = AddElement(paragraph, "TextRuns", null);
	textRun = AddElement(textRuns, "TextRun", null);
	AddElement(textRun, "Value", "=Fields!{0}.Value".Set(fieldname));

	style = AddElement(textRun, "Style", null);
	style = AddElement(textbox, "Style", null);
	border = AddElement(style, "Border", null);
	AddElement(border, "Color", "LightGrey");
	AddElement(border, "Style", "Solid");
	AddElement(style, "PaddingLeft", "2pt");
	AddElement(style, "PaddingRight", "2pt");
	AddElement(style, "PaddingTop", "2pt");
	AddElement(style, "PaddingBottom", "2pt");
}
public void CreateHeaderCell(XmlElement tablixCells, XmlDocument doc, string fieldName, string fieldHeaderName)
{
	XmlElement tablixCell;
	XmlElement cellContents;
	XmlElement textbox;
	XmlElement paragraphs;
	XmlElement paragraph;
	XmlElement textRuns;
	XmlElement textRun;
	XmlElement style;
	XmlElement border;

	tablixCell = AddElement(tablixCells, "TablixCell", null);
	cellContents = AddElement(tablixCell, "CellContents", null);
	textbox = AddElement(cellContents, "Textbox", null);
	AddAttribute(textbox, doc, "Name", "Header{0}".Set(fieldName));
	AddElement(textbox, "KeepTogether", "true");
	paragraphs = AddElement(textbox, "Paragraphs", null);
	paragraph = AddElement(paragraphs, "Paragraph", null);
	textRuns = AddElement(paragraph, "TextRuns", null);
	textRun = AddElement(textRuns, "TextRun", null);
	AddElement(textRun, "Value", "{0}".Set(fieldHeaderName));

	style = AddElement(textRun, "Style", null);
	style = AddElement(textbox, "Style", null);
	border = AddElement(style, "Border", null);
	AddElement(border, "Color", "LightGrey");
	AddElement(border, "Style", "Solid");
	AddElement(style, "PaddingLeft", "2pt");
	AddElement(style, "PaddingRight", "2pt");
	AddElement(style, "PaddingTop", "2pt");
	AddElement(style, "PaddingBottom", "2pt");
	AddElement(style, "BackgroundColor", "PaleTurquoise");
}

the next methods are utility methods used for creating the report xml

public XmlElement AddElement(XmlElement parent, string name, string value)
{
	XmlElement newelement = parent.OwnerDocument.CreateElement(name,
		"http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition");
	parent.AppendChild(newelement);
	if (value != null) newelement.InnerText = value;
	return newelement;
}
public XmlElement AddRdElement(XmlElement parent, string name, string value)
{
	XmlElement newelement = parent.OwnerDocument.CreateElement(name,
		"http://schemas.microsoft.com/SQLServer/reporting/reportdesigner");
	parent.AppendChild(newelement);
	if (value != null) newelement.InnerText = value;
	return newelement;
}
public XmlAttribute AddAttribute(XmlElement parentElement, XmlDocument doc, string attributeName, string attributeValue)
{
	XmlAttribute attr = parentElement.Attributes.Append(doc.CreateAttribute(attributeName));
	attr.Value = attributeValue;

	return attr;
}

Now to use this RDLGenerator Class

public void CreateDynamicColumnRDL(string datasetName, IEnumerable<object> objectList)
{
	var rdlGenerator = new RDLGenerator();

	//Retrive field/property name list from objectList
	//this could be done through reflection that i would discuss in another topic
	var fieldList = ConvertHelper.RetrivePropertyNameList(objectList);

	var doc = rdlGenerator.CreateDocument();
	var report = rdlGenerator.CreateReportBase(doc);
	rdlGenerator.CreateReportDataSet(report, doc, datasetName, fieldList);
	rdlGenerator.CreateReportBody(report, doc, datasetName, fieldList);

	//Convert to Stream
	byte[] myByteArray = System.Text.Encoding.UTF8.GetBytes(doc.OuterXml);
	MemoryStream ms = new MemoryStream(myByteArray);

	//Supply Stream to ReportViewer
	ReportViewer1.LocalReport.LoadReportDefinition(ms);
	//Supply DataSource
	ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource(datasetName, objectList));
	//Refresh Report Viewer
	ReportViewer1.LocalReport.Refresh();
}

Here is the structure for the report column

    public class ReportColumn
    {
        public string Name { get; set; }
        public string HeaderName { get; set; }
        public string Width { get; set; }
        public int Order { get; set; }
        public string FieldType { get; set; }
    }

Here is a snapshot of code within the ConvertHelper List. Basically it retrive a list of ReportColumn to publish to the rdl generator, the code below has other dependecies such as retriving the report column definitions “_reportDefinition” which is not covered anymore in this post. to completely cuztomize this for your own use, you would need knowledge in .Net reflection. but the idea simply is convert the list of objects with attributes into a list of reportcolumn to be passed to the rdl generator.

“Disclaimer: Convert Helper below is just a sample, you’ll need to implement your own reflection approach in retriving the list of reportcolumn based on your object.”

 public static List<ReportColumn> RetrivePropertyNameList(string dataSetname, IEnumerable<object> Data)
        {
            try
            {
                var model = Data.FirstOrDefault();
                if (model != null)
                {
                    var modelType = model.GetType();
                    var propertyInfo = modelType.GetProperties();
                    var propertyList = new List<ReportColumn>();
                    var propertyCount = propertyInfo.Count();
                    var reportDefinitionColumns = _reportDefinition.ReportColumns;

                    var orderCount = 0;
                    foreach (var property in propertyInfo)
                    {
                        var reportColumn = new ReportColumn();
                        var columnName = property.Name;

                        var columnInDefinition = reportDefinitionColumns.Where(x => x.Id == columnName).FirstOrDefault();

                        if (columnInDefinition != null)
                        {
                            if (orderCount != 0 && columnInDefinition.Order > orderCount)
                            {
                                orderCount += 1;
                                reportColumn.Order = orderCount;
                            }
                            else 
                            {
                                reportColumn.Order = columnInDefinition.Order;
                            }

                            reportColumn.Width = columnInDefinition.Width;
                            
                            reportColumn.HeaderName = columnInDefinition.Header;
                            reportColumn.Name = columnInDefinition.Id;
                            reportColumn.FieldType = columnInDefinition.FieldType ?? Constants.ReportFieldType.String;

                            propertyList.Add(reportColumn);
                        }
                        else 
                        {
                            columnInDefinition = reportDefinitionColumns.Where(x => columnName.Contains(x.Id)).FirstOrDefault();

                            if (columnInDefinition != null) 
                            {
                                if (orderCount == 0)
                                {
                                    orderCount = columnInDefinition.Order;
                                    reportColumn.Order = columnInDefinition.Order;
                                }
                                else 
                                {
                                    orderCount += 1;
                                    reportColumn.Order = orderCount;
                                }

                                reportColumn.Width = columnInDefinition.Width;

                                var toReplace = "{0}_".Set(columnInDefinition.Id);
                                reportColumn.HeaderName = columnName.Replace(toReplace, "").Replace("_"," ");
                                reportColumn.Name = columnName;
                                reportColumn.FieldType = columnInDefinition.FieldType ?? Constants.ReportFieldType.String;

                                propertyList.Add(reportColumn);
                            }
                        }
                    }

                    return propertyList;
                }
            }
            catch (Exception exception)
            {
                Console.WriteLine("An error occurred: " + exception.Message);
            }

            return default(List<ReportColumn>);
        }

Conclusion?

Just simply copy all methods to RDLGenerator create the whole class.

Lastly to note was this is created as a generic report which would display data from a gridview,
to customize the code would require familiarizartion on the xml schema of the sql rdl itself which I’ve done through opening the rdl file in xml editor view. anyway, hope this helps anyone! thanks!

2012-04-02 – Added the report column object for reference. Thanks to 4son!
2012-04-28 – Added a sample on retrieving the report column list from an object through reflection.