Share via

Lesson 4: Creating Code to Generate the Report Definition File

  • Article

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

  1. 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();
}

See Also

Tasks

Lesson 5: Running the RDL Generator Application (VB/C#)
Tutorial: Generating RDL Using the .NET Framework

Help and Information

Getting SQL Server 2005 Assistance