lbrt.Net

Pinoy Software Engineer

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.

Advertisements

11 responses to “Reporting Services: Creating A Dynamic Column Tablix Report using RDL Generator

  1. 4son 2012/03/30 at 8:19 PM

    This looks cool but does not build. ReportColumn class cannot be resolved. Some Linq expressions are ambiguous. I am probably missing something simple…. Is there a compiled sample site with this working?

    Thanks.

  2. lbrt21 2012/04/02 at 4:06 AM

    Hi 4son,

    The ReportColumn is a simple c# class. will add it above.

    Thanks,
    lbrt

  3. Satya Prakash 2012/04/25 at 1:29 PM

    hi, i tried your code bu not able to make a class ” ConvertHelper”. can you plz help me.
    thanks.

    • lbrt 2012/04/28 at 4:27 AM

      Hi Satya, Convert Helper is basically a reflection approach in converting the Attributes an properties to a report column list. Will add the code once I get to a pc.

  4. Pingback: Add and remove columns from a Tablix in rdlc report | PHP Developer Resource

  5. Rajesh 2012/07/05 at 7:40 AM

    Can we post the code how u r passing parameters for RetrivePropertyNameList()

  6. Rajesh 2012/07/05 at 7:41 AM

    Can you please post the code how u r passing parameters for RetrivePropertyNameList()

    • lbrt 2012/07/05 at 8:33 AM

      In the method CreateDynamicColumnRDL -> there is a call for RetrivePropertyNameList as shown below:
      var fieldList = ConvertHelper.RetrivePropertyNameList(objectList);

      my current RetrivePropertyNameList sample though is a different one from the RetrivePropertyNameList above since i’ve already done changes on my code.

      So to put it simply, RetrivePropertyNameList is just a method which converts the your object (e.g. List where T is any poco object) to a List.

      • Kham Lyna 2012/12/17 at 2:41 AM

        You can show about ConvertHelper & ConvertHelper.RetrivePropertyNameList(objectList)

  7. noxy 2012/12/17 at 5:30 PM

    can you provide demo project (asp.net / mvc) ?

  8. Myron Joy 2013/06/17 at 5:01 PM

    If you don;t want to do the coding, take a look at http://www.rptgen.com.
    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: