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();
}
See Also
Tasks
Lesson 5: Running the RDL Generator Application (VB/C#)
Tutorial: Generating RDL Using the .NET Framework