Microsoft Reporting without SQL Server Reporting Services

• Download demo project - 1.34 MB

Introduction

As developers, displaying reports is one thing we all spend a lot of time on. There are so many ways to show reports - formatted HTML, GridViews, Repeaters - the list goes on and on and on. When one of my clients asked for a reporting engine that allowed for dynamic reports - that is, user defined parameters define the data shown in the reports, I began to look at the ReportViewer control. A great feature about this control is the ability to save the report straight into Excel. It also has paging, zoom, and much more. I initially discussed this topic on my blog, but decided CodeProject is a better location.

It should be noted that there are two versions of the ReportViewer control. The first is located in the Microsoft.Reporting.WinForms library, and is for Windows application development. The second is in Microsoft.Reporting.WebForms, and is designed to be used in ASP.NET. Both controls are only available for use in Microsoft Visual Studio 2005 or later and the .NET 2.0 Framework.

Background

My first task in using the control was to determine how to actually go about using it. It uses a .rdlc file which serves as the template for both the data that is passed in and the formatting inside the ReportViewer. Along with the RDLC file, the ReportViewer needs the actual data bound to it. The simplest way is to use SQL Server 2005 installed with Reporting Services, but I found, with a little exploration, that the process of binding these two objects into the ReportViewer is flexible enough to support using any data source, if you don't mind a little elbow grease.

Decoding the RDLC

To determine how the RDLC is generated, we have to create a sample. Reports can be as simple as tabular data, a chart, or a series of both, with images, logos, etc.

In Visual Studio 2005, create a new project. The type doesn't really matter, but for testing purposes, it is best to choose the type of application you are going to use the ReportViewer in. Once you have your application, add a DataSet to the project. Define a table with a few columns.

Next, add a report to the project. Along the left side, you should see a window where you can add a data source. Go ahead and add the DataSet we created as the data source.

I know what you're thinking. If we are building a DataSet object, why don't we just connect it to the database, use it as our single source, and just add a new RDLC for each report? Well, that's a good question. We could, in fact, create a DataSet object, add the tables and columns we need, hook up each field to reports we want, and have as many RDLCs in our project as reports that we need. That's a great solution if you only need one or two reports and don't mind managing each of them by hand. What if your user has 30 different reports they want? Also, you would lose flexibility in the data source. After the initial building of the classes, a reporting engine built on these principals could be re-used time and time again. Also, it becomes very easy to standardize a report layout across any number of reports, and then change that standardization if your company updates their logo or decides to change their report look and feel. Plus, you don't have to manage the report files individually. If you want to add a column to the data, just change the SQL query. Anyway, let's get back to the task at hand.

In the design window, design your report to appear how you want it to be. If your report needs specific features, make sure to add them here so you can see how they are handled in the RDLC. If your users want a logo or any special formatting, now is the time to do it. You will be able to see how it is done with the RDLC and then mimic it in your own RDLC generator.

Once your layout is complete, navigate to the folder where the project is located, and save the .rdlc file as a .xml file. This can be saved elsewhere. Open up the XML file in your favorite XML editor. I prefer to just use Visual Studio. This file shows you much of the formatting options you have to work with. Now that we have a template to work from, let's pick it apart.

Here is the part of the RDLC corresponding to the actual DataSet. DataSet1 is the name of our DataSet, and each data field is specified in the list. You should note that the data sources we will be passing in later will be a single DataTable added to a DataSet. This is due to the means by which we bind the data. It appears to be possible to create a report using multiple tables, but I haven't tried it out, so don't take my word for it.

<DataSources>
    <DataSource Name="DataSet1" />
</DataSources>
<DataSets>
    <DataSet Name="DataSet1">
        <Fields>
            <Field Name="Column1" DataField="ColumnName" />
            ...
        </Fields>
    </DataSet>
</DataSets>

By changing "DataSet1" to any value, you can pass in any named DataSet. We'll discuss the significance of this find in just a bit. For now, let's continue picking the RDLC apart.

If you add a table to your report, you will see a Details section with your columns listed out. Let's take a look.

<Details>
    <TableRows>
        <TableRow>
            <TableCells>
                <TableCell>
                    <ReportItems>
                        <TextBox Name="textBox1" ZIndex="1" />
                        <Value>=Fields!Column1.Value</Value>
                    </ReportItems>
                </TableCell>
                ...
            </TableCells>
        </TableRow>
    </TableRows>
</Details>

We see in this block of the RDLC that the columns in our DataSet are defined here as to where the data is placed in the report. One of the complicated tasks I wanted to achieve was to have the rows alternate background colors. By using a special reporting syntax, this can be completed by setting this attribute on the TextBox: BackgroundColor="=iff(RowNumber(Nothing) Mod 2, "#000000", "#FFFFFF")". Yes, you need the double quotes.

Using these simple steps, you can pick apart what the RDLC definition is for the features you want on your report. You would follow a similar process for determining how to generate a chart in your report, display your company logo, or any other feature you want to implement.

How to Use the RDLC

Now that we have a basic understanding of how the RDLC works to define the report, we can write a class that builds the XML contained in the RDLC and then use that XML in the report. In the code samples I have provided, you can see a basic implementation of an RDLC builder for both a chart and a tabular report. These are simple classes building simple RDLCs. In the future, I want to modularize the whole process, splitting out parts of the RDLC into little specialized classes, and then pulling the whole thing together in one reporting class that lets you add multiple datasets, tabular forms, charts, define formatting, etc.

When I first wrote the code to generate an RDLC, I was writing the XML to a file and then loading up the file into the ReportViewer. This is fine in a Windows application environment where it is installed per user, but sometimes, writing files to disk just isn't a good idea, especially in web development. I played around with the ReportViewer, and discovered a way to pass the RDLC as a System.IO.MemoryStream object instead of writing it to file. This was a much better solution, considering the security horrors there are around writing files on a web server and the sheer WTF-ness of writing an RDLC to a file each time the report needs to be displayed. Let's take a look at how to bind our RDLC and data source to the ReportViewer.

DataTable customerDataTable = GetCustomerData();

customerDataTable.TableName = "CustomerDataTable";

System.Data.DataSet customerData = customerDataTable.DataSet;
customerData.DataSetName = "CustomerData";

Rdlc report = new Rdlc(customerData);

reportViewer1.LocalReport.DataSources.Add(
      new Microsoft.Reporting.WinForms.ReportDataSource(customerData.DataSetName,
      customerDataTable));
reportViewer1.LocalReport.LoadReportDefinition(report.GetRdlcStream());

this.reportViewer1.RefreshReport();

GetCustomerData() is a method that returns a System.Data.DataTable. Technically, what I would typically do is run a query against a database, load the data into a DataSet, and then return the DataSet - ADO.NET 101. This data could come from anywhere - WebService XML, a delimited file, or any database you want to use. The trick is to load it into a System.Data.DataTable and add the DataTable to a DataSet if it doesn't already belong to one. In the case of XML, you can just load the XML into the DataSet, but be sure to uniquely name the DataSet and DataTable. The RDLC class will pick out the names and build the RDLC to match the data passed in. This crude class currently does not support filtering columns; however, it has a way to override the column names by passing in a string array of header names. It's not the best method, but this code isn't by any means a complete reporting library.

Once we have the DataSet and DataTable properly named, it is time to bind it to the ReportViewer. As you can see, I am using the WinForms version of the control and must use the WinForms version of the ReportDataSource class. There is a corresponding ReportDataSource for the web version. Just change WinForms to WebForms. After that, we are loading the RDLC from a System.IO.MemoryStream.

Source Code

I have included four classes that contain the logic to build RDLCs for simple tabular reports and simple charts. On the charting side, only the pie chart has been tested, so there might be bugs with this code. In fact, any of this code could be buggy because it's still in the "proof of concept" phase.

• Chart - The class that generates an RDLC for a chart.
• ChartType - An enum for the type of chart (pie, line, scatter plot, etc.).
• ChartSubType - An enum for the chart subtype (plain, stacked, etc.). Note: not all subtypes work with all chart types.
• Rdlc - The class that generates an RDLC for tabular data.

Let's examine the simplest class, Rdlc.

The formatting options are currently managed with properties. There are better ways, but that's for another article. Let's get right to the meat and potatoes: GetRdlcString().

XmlTextWriter _rdl = new XmlTextWriter(writer);

DataTable data = _data.Tables[0];

_rdl.Formatting = Formatting.Indented;
_rdl.Indentation = 3;
_rdl.Namespaces = true;

int _columns = data.Columns.Count;

This code builds the section for the DataSet passed in to the constructor.

// DataSource element
_rdl.WriteStartElement("DataSources");
_rdl.WriteStartElement("DataSource");
_rdl.WriteAttributeString("Name", null, data.DataSet.DataSetName);
_rdl.WriteStartElement("ConnectionProperties");
_rdl.WriteElementString("DataProvider", "Oracle");
_rdl.WriteElementString("ConnectString", "ItsaSecret");
_rdl.WriteElementString("IntegratedSecurity", "true");
_rdl.WriteEndElement(); // ConnectionProperties
_rdl.WriteEndElement(); // DataSource
_rdl.WriteEndElement(); // DataSources

// DataSet element
_rdl.WriteStartElement("DataSets");
_rdl.WriteStartElement("DataSet");
_rdl.WriteAttributeString("Name", null, data.DataSet.DataSetName);

// Query element
_rdl.WriteStartElement("Query");
_rdl.WriteElementString("DataSourceName", data.DataSet.DataSetName);
_rdl.WriteElementString("CommandType", "Text");
_rdl.WriteElementString("CommandText", "wouldntyouliketoknow");
_rdl.WriteElementString("Timeout", "30");
_rdl.WriteEndElement(); // Query

// Fields elements
_rdl.WriteStartElement("Fields");

for (int x = 0; x < _columns; x++)
{
    _rdl.WriteStartElement("Field");
    _rdl.WriteAttributeString("Name", null, data.Columns[x].ColumnName);
    _rdl.WriteElementString("DataField", null, data.Columns[x].ColumnName);
    _rdl.WriteEndElement(); // Field
}

// End previous elements
_rdl.WriteEndElement(); // Fields
_rdl.WriteEndElement(); // DataSet
_rdl.WriteEndElement(); // DataSets

The Query node is a section in the DataSet declaration that I left in for future support. It is possible to have the report run a query. This is all fine and dandy, but I prefer to just pass in the already compiled data. You can test this part of the RDLC by adding a query to your DataSet object.

// Query element
_rdl.WriteStartElement("Query");
_rdl.WriteElementString("DataSourceName", data.DataSet.DataSetName);
_rdl.WriteElementString("CommandType", "Text");
_rdl.WriteElementString("CommandText", "wouldntyouliketoknow");
_rdl.WriteElementString("Timeout", "30");
_rdl.WriteEndElement(); // Query

Next, we have the Details section with the column specifications. It simply loops through the columns in the DataTable and adds each cell to the RDLC. This has the logic for alternating row colors and text color with the row.

_rdl.WriteStartElement("", "Details", null);
_rdl.WriteStartElement("", "TableRows", null);
_rdl.WriteStartElement("", "TableRow", null);
_rdl.WriteStartElement("", "TableCells", null);

int _detailIndex = _columns * 2;

//write all the detail items
for (int x = 0; x < _columns; x++)
{
    int _zindex = (_detailIndex + x);
    string _name = "textbox" + _zindex;
    string _value = "=Fields!" + data.Columns[x].ColumnName + ".Value";

    //Alternate the colors by row
    string _bgcolor = "=iif(RowNumber(Nothing) Mod 2, \"" +
        System.Drawing.ColorTranslator.ToHtml(BackgroundColorBody) +
        "\", \"" + System.Drawing.ColorTranslator.ToHtml(BackgroundColorBodyAlternate) +
        "\")";
    string _textcolor = "=iif(RowNumber(Nothing) Mod 2, \"" +
        System.Drawing.ColorTranslator.ToHtml(TextColorBody) + "\", \"" +
        System.Drawing.ColorTranslator.ToHtml(TextColorBodyAlternate) + "\")";

    AddCell(_rdl, _name, _bgcolor, _textcolor, TextAlignFooter, 0, _zindex, _value);
}

//end TableCells
_rdl.WriteEndElement();

_rdl.WriteStartElement("", "Height", null);
_rdl.WriteString("0.25in");
_rdl.WriteEndElement();

//end TableRow
_rdl.WriteEndElement();

//end TableRows
_rdl.WriteEndElement();

//end detail
_rdl.WriteEndElement();

To make things easy, I have a method called AddCell which creates the XML for each cell definition. It has some formatting that I pass in.

private static void AddCell(XmlTextWriter _writer, string name,
    string backgroundColor, string textColor,
    string textAlign, int fontWeight, int ZIndex, string value)
{
    _writer.WriteStartElement("", "TableCell", null);
    _writer.WriteStartElement("", "ReportItems", null);

    _writer.WriteStartElement("", "Textbox", null);
    _writer.WriteAttributeString("Name", name);

    _writer.WriteStartElement("", "ZIndex", null);
    _writer.WriteString(ZIndex.ToString(CultureInfo.InvariantCulture));
    _writer.WriteEndElement();

    _writer.WriteStartElement("", "Style", null);

    _writer.WriteStartElement("", "TextAlign", null);
    _writer.WriteString(textAlign);
    _writer.WriteEndElement();

    _writer.WriteStartElement("", "Color", null);
    _writer.WriteString(textColor);
    _writer.WriteEndElement();

    _writer.WriteStartElement("", "BackgroundColor", null);
    _writer.WriteString(backgroundColor);
    _writer.WriteEndElement();

    _writer.WriteStartElement("", "FontWeight", null);
    if (fontWeight == 0)
    {
        _writer.WriteString("100");
    }
    else
    {
        _writer.WriteString(fontWeight.ToString(CultureInfo.InvariantCulture));
    }
    _writer.WriteEndElement();

    _writer.WriteStartElement("", "PaddingLeft", null);
    _writer.WriteString("2pt");
    _writer.WriteEndElement();

    _writer.WriteStartElement("", "PaddingBottom", null);
    _writer.WriteString("2pt");
    _writer.WriteEndElement();

    _writer.WriteStartElement("", "PaddingRight", null);
    _writer.WriteString("2pt");
    _writer.WriteEndElement();

    _writer.WriteStartElement("", "PaddingTop", null);
    _writer.WriteString("2pt");
    _writer.WriteEndElement();

    //end Style
    _writer.WriteEndElement();

    _writer.WriteStartElement("", "CanGrow", null);
    _writer.WriteString("true");
    _writer.WriteEndElement();

    _writer.WriteStartElement("", "Value", null);
    _writer.WriteString(value);
    _writer.WriteEndElement();

    //end TextBox
    _writer.WriteEndElement();

    //End ReportItems
    _writer.WriteEndElement();

    //end TableCell
    _writer.WriteEndElement();
}

Caveats

On the web based version, it is not possible to reload the ReportViewer once it has been loaded. You must hit the page again and use the query string, session, view state, or other means of passing data to change the report. The WinForms version does not have this issue.

If the XML tags in the RDLC are not formatted exactly right, and sometimes if you leave out certain elements, the report will not load. The ReportViewer will display a vague error if it cannot load the report.

Conclusion

It is possible to use Microsoft Reporting with any data source you want. Microsoft and all the companies that sell charting application libraries (sorry Dundas) won't want you to know that. I haven't delved into the charting feature enough to find any limitations, but I'm sure there are a few. With the basics I have presented here, any developer should be able to build their own reporting library that utilizes the power of the ReportViewer and connect to any data source they might be using for their applications.