• Article

Lesson 4: Creating Code to Generate the Report Definition File

Now that you have created your connection and retrieved a list of fields for the query, you can generate RDL programmatically using XmlTextWriter.

To generate RDL programmatically

  • Replace the code for the GenerateRdl() method in your project with the following code:
Public Sub GenerateRdl()
    ' Open a new RDL file stream for writing
    Dim stream As FileStream
    stream = File.OpenWrite("Report1.rdl")
    Dim writer As New XmlTextWriter(stream, Encoding.UTF8)

    ' Causes child elements to be indented
    writer.Formatting = Formatting.Indented

    ' Report element
    writer.WriteProcessingInstruction("xml", "version=""1.0"" encoding=""utf-8""")
    writer.WriteStartElement("Report")
    writer.WriteAttributeString("xmlns", Nothing, "https://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition")
    writer.WriteElementString("Width", "6in")

    ' DataSource element
    writer.WriteStartElement("DataSources")
    writer.WriteStartElement("DataSource")
    writer.WriteAttributeString("Name", Nothing, "DataSource1")
    writer.WriteStartElement("ConnectionProperties")
    writer.WriteElementString("DataProvider", "SQL")
    writer.WriteElementString("ConnectString", m_connectString)
    writer.WriteElementString("IntegratedSecurity", "true")
    writer.WriteEndElement() ' ConnectionProperties
    writer.WriteEndElement() ' DataSource
    writer.WriteEndElement() ' DataSources
    ' DataSet element
    writer.WriteStartElement("DataSets")
    writer.WriteStartElement("DataSet")
    writer.WriteAttributeString("Name", Nothing, "DataSet1")

    ' Query element
    writer.WriteStartElement("Query")
    writer.WriteElementString("DataSourceName", "DataSource1")
    writer.WriteElementString("CommandType", "Text")
    writer.WriteElementString("CommandText", m_commandText)
    writer.WriteElementString("Timeout", "30")
    writer.WriteEndElement() ' Query
    ' Fields elements
    writer.WriteStartElement("Fields")
    Dim fieldName As String
    For Each fieldName In m_fields
        writer.WriteStartElement("Field")
        writer.WriteAttributeString("Name", Nothing, fieldName)
        writer.WriteElementString("DataField", Nothing, fieldName)
        writer.WriteEndElement() ' Field
    Next fieldName

    ' End previous elements
    writer.WriteEndElement() ' Fields
    writer.WriteEndElement() ' DataSet
    writer.WriteEndElement() ' DataSets
    ' Body element
    writer.WriteStartElement("Body")
    writer.WriteElementString("Height", "5in")

    ' ReportItems element
    writer.WriteStartElement("ReportItems")

    ' Table element
    writer.WriteStartElement("Table")
    writer.WriteAttributeString("Name", Nothing, "Table1")
    writer.WriteElementString("DataSetName", "DataSet1")
    writer.WriteElementString("Top", ".5in")
    writer.WriteElementString("Left", ".5in")
    writer.WriteElementString("Height", ".5in")
    writer.WriteElementString("Width", (m_fields.Count * 1.5).ToString() + "in")

    ' Table Columns
    writer.WriteStartElement("TableColumns")
    For Each fieldName In m_fields
        writer.WriteStartElement("TableColumn")
        writer.WriteElementString("Width", "1.5in")
        writer.WriteEndElement() ' TableColumn
    Next fieldName
    writer.WriteEndElement() ' TableColumns
    ' Header Row
    writer.WriteStartElement("Header")
    writer.WriteStartElement("TableRows")
    writer.WriteStartElement("TableRow")
    writer.WriteElementString("Height", ".25in")
    writer.WriteStartElement("TableCells")

    For Each fieldName In m_fields
        writer.WriteStartElement("TableCell")
        writer.WriteStartElement("ReportItems")

        ' Textbox
        writer.WriteStartElement("Textbox")
        writer.WriteAttributeString("Name", Nothing, "Header" + fieldName)

        writer.WriteStartElement("Style")
        writer.WriteElementString("TextDecoration", "Underline")
        writer.WriteEndElement() ' Style
        writer.WriteElementString("Top", "0in")
        writer.WriteElementString("Left", "0in")
        writer.WriteElementString("Height", ".5in")
        writer.WriteElementString("Width", "1.5in")
        writer.WriteElementString("Value", fieldName)
        writer.WriteEndElement() ' Textbox
        writer.WriteEndElement() ' ReportItems
        writer.WriteEndElement() ' TableCell
    Next fieldName

    writer.WriteEndElement() ' TableCells
    writer.WriteEndElement() ' TableRow
    writer.WriteEndElement() ' TableRows
    writer.WriteEndElement() ' Header
    ' Details Row
    writer.WriteStartElement("Details")
    writer.WriteStartElement("TableRows")
    writer.WriteStartElement("TableRow")
    writer.WriteElementString("Height", ".25in")
    writer.WriteStartElement("TableCells")

    For Each fieldName In m_fields
        writer.WriteStartElement("TableCell")
        writer.WriteStartElement("ReportItems")

        ' Textbox
        writer.WriteStartElement("Textbox")
        writer.WriteAttributeString("Name", Nothing, fieldName)

        writer.WriteStartElement("Style")
        writer.WriteEndElement() ' Style
        writer.WriteElementString("Top", "0in")
        writer.WriteElementString("Left", "0in")
        writer.WriteElementString("Height", ".5in")
        writer.WriteElementString("Width", "1.5in")
        writer.WriteElementString("Value", "=Fields!" + fieldName + ".Value")
        writer.WriteElementString("HideDuplicates", "DataSet1")
        writer.WriteEndElement() ' Textbox
        writer.WriteEndElement() ' ReportItems
        writer.WriteEndElement() ' TableCell
    Next fieldName


    ' End Details element and children   
    writer.WriteEndElement() ' TableCells
    writer.WriteEndElement() ' TableRow
    writer.WriteEndElement() ' TableRows
    writer.WriteEndElement() ' Details
    ' End table element and end report definition file
    writer.WriteEndElement() ' Table
    writer.WriteEndElement() ' ReportItems
    writer.WriteEndElement() ' Body
    writer.WriteEndElement() ' Report
    ' Flush the writer and close the stream
    writer.Flush()
    stream.Close()
End Sub 'GenerateRdl

public void GenerateRdl()
{
   // Open a new RDL file stream for writing
   FileStream stream;
   stream = File.OpenWrite("Report1.rdl");
   XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);

   // Causes child elements to be indented
   writer.Formatting = Formatting.Indented;

   // Report element
   writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");
   writer.WriteStartElement("Report");
   writer.WriteAttributeString("xmlns", null, "https://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition");
   writer.WriteElementString("Width", "6in");

   // DataSource element
   writer.WriteStartElement("DataSources");
   writer.WriteStartElement("DataSource");
   writer.WriteAttributeString("Name", null, "DataSource1");
   writer.WriteStartElement("ConnectionProperties");
   writer.WriteElementString("DataProvider", "SQL");
   writer.WriteElementString("ConnectString", m_connectString);
   writer.WriteElementString("IntegratedSecurity", "true");
   writer.WriteEndElement(); // ConnectionProperties
   writer.WriteEndElement(); // DataSource
   writer.WriteEndElement(); // DataSources

   // DataSet element
   writer.WriteStartElement("DataSets");
   writer.WriteStartElement("DataSet");
   writer.WriteAttributeString("Name", null, "DataSet1");

   // Query element
   writer.WriteStartElement("Query");
   writer.WriteElementString("DataSourceName", "DataSource1");
   writer.WriteElementString("CommandType", "Text");
   writer.WriteElementString("CommandText", m_commandText);
   writer.WriteElementString("Timeout", "30");
   writer.WriteEndElement(); // Query

   // Fields elements
   writer.WriteStartElement("Fields");
   foreach (string fieldName in m_fields)
   {
      writer.WriteStartElement("Field");
      writer.WriteAttributeString("Name", null, fieldName);
      writer.WriteElementString("DataField", null, fieldName);
      writer.WriteEndElement(); // Field
   }

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

   // Body element
   writer.WriteStartElement("Body");
   writer.WriteElementString("Height", "5in");

   // ReportItems element
   writer.WriteStartElement("ReportItems");

   // Table element
   writer.WriteStartElement("Table");
   writer.WriteAttributeString("Name", null, "Table1");
   writer.WriteElementString("DataSetName", "DataSet1");
   writer.WriteElementString("Top", ".5in");
   writer.WriteElementString("Left", ".5in");
   writer.WriteElementString("Height", ".5in");
   writer.WriteElementString("Width", (m_fields.Count * 1.5) + "in");

   // Table Columns
   writer.WriteStartElement("TableColumns");
   for (int i = 0; i < m_fields.Count; i++)
   {
      writer.WriteStartElement("TableColumn");
      writer.WriteElementString("Width", "1.5in");
      writer.WriteEndElement(); // TableColumn
   }
   writer.WriteEndElement(); // TableColumns

   // Header Row
   writer.WriteStartElement("Header");
   writer.WriteStartElement("TableRows");
   writer.WriteStartElement("TableRow");
   writer.WriteElementString("Height", ".25in");
   writer.WriteStartElement("TableCells");

   foreach (string fieldName in m_fields)
   {
      writer.WriteStartElement("TableCell");
      writer.WriteStartElement("ReportItems");

      // Textbox
      writer.WriteStartElement("Textbox");
      writer.WriteAttributeString("Name", null, "Header" + fieldName);

      writer.WriteStartElement("Style");
      writer.WriteElementString("TextDecoration", "Underline");
      writer.WriteEndElement(); // Style

      writer.WriteElementString("Top", "0in");
      writer.WriteElementString("Left", "0in");
      writer.WriteElementString("Height", ".5in");
      writer.WriteElementString("Width", "1.5in");
      writer.WriteElementString("Value", fieldName);
      writer.WriteEndElement(); // Textbox

      writer.WriteEndElement(); // ReportItems
      writer.WriteEndElement(); // TableCell
   }

   writer.WriteEndElement(); // TableCells
   writer.WriteEndElement(); // TableRow
   writer.WriteEndElement(); // TableRows
   writer.WriteEndElement(); // Header

   // Details Row
   writer.WriteStartElement("Details");
   writer.WriteStartElement("TableRows");
   writer.WriteStartElement("TableRow");
   writer.WriteElementString("Height", ".25in");
   writer.WriteStartElement("TableCells");

   foreach (string fieldName in m_fields)
   {
      writer.WriteStartElement("TableCell");
      writer.WriteStartElement("ReportItems");

      // Textbox
      writer.WriteStartElement("Textbox");
      writer.WriteAttributeString("Name", null, fieldName);

      writer.WriteStartElement("Style");
      writer.WriteEndElement(); // Style

      writer.WriteElementString("Top", "0in");
      writer.WriteElementString("Left", "0in");
      writer.WriteElementString("Height", ".5in");
      writer.WriteElementString("Width", "1.5in");
      writer.WriteElementString("Value", "=Fields!" + fieldName + ".Value");
      writer.WriteElementString("HideDuplicates", "DataSet1");
      writer.WriteEndElement(); // Textbox

      writer.WriteEndElement(); // ReportItems
      writer.WriteEndElement(); // TableCell
   }   

   // End Details element and children   
   writer.WriteEndElement(); // TableCells
   writer.WriteEndElement(); // TableRow
   writer.WriteEndElement(); // TableRows
   writer.WriteEndElement(); // Details

   // End table element and end report definition file
   writer.WriteEndElement(); // Table
   writer.WriteEndElement(); // ReportItems
   writer.WriteEndElement(); // Body
   writer.WriteEndElement(); // Report

   // Flush the writer and close the stream
   writer.Flush();
   stream.Close();
}