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 an XmlDocument.
To generate RDL programmatically
- Replace the code for the GenerateRdl() method in your project with the following code:
Public Sub GenerateRdl()
' Create an XML document
Dim doc As New XmlDocument()
Dim xmlData As String = "<Report xmlns=" & '"' & _
"https://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition" & _
'"' & "></Report>"
doc.Load(New StringReader(xmlData))
' Report element
Dim report As XmlElement = DirectCast(doc.FirstChild, XmlElement)
AddElement(report, "AutoRefresh", "0")
AddElement(report, "ConsumeContainerWhitespace", "true")
'DataSources element
Dim dataSources As XmlElement = AddElement(report, "DataSources", Nothing)
'DataSource element
Dim dataSource As XmlElement = AddElement(dataSources, "DataSource", Nothing)
Dim attr As XmlAttribute = dataSource.Attributes.Append(doc.CreateAttribute("Name"))
attr.Value = "DataSource1"
Dim connectionProperties As XmlElement = AddElement(dataSource, "ConnectionProperties", Nothing)
AddElement(connectionProperties, "DataProvider", "SQL")
AddElement(connectionProperties, "ConnectString", m_connectString)
AddElement(connectionProperties, "IntegratedSecurity", "true")
'DataSets element
Dim dataSets As XmlElement = AddElement(report, "DataSets", Nothing)
Dim dataSet As XmlElement = AddElement(dataSets, "DataSet", Nothing)
attr = dataSet.Attributes.Append(doc.CreateAttribute("Name"))
attr.Value = "DataSet1"
'Query element
Dim query As XmlElement = AddElement(dataSet, "Query", Nothing)
AddElement(query, "DataSourceName", "DataSource1")
AddElement(query, "CommandText", m_commandText)
AddElement(query, "Timeout", "30")
'Fields element
Dim fields As XmlElement = AddElement(dataSet, "Fields", Nothing)
Dim field As XmlElement = AddElement(fields, "Field", Nothing)
attr = field.Attributes.Append(doc.CreateAttribute("Name"))
attr.Value = "CountryName"
AddElement(field, "DataField", "CountryName")
field = AddElement(fields, "Field", Nothing)
attr = field.Attributes.Append(doc.CreateAttribute("Name"))
attr.Value = "StateProvince"
AddElement(field, "DataField", "StateProvince")
'end of DataSources
'ReportSections element
Dim reportSections As XmlElement = AddElement(report, "ReportSections", Nothing)
Dim reportSection As XmlElement = AddElement(reportSections, "ReportSection", Nothing)
AddElement(reportSection, "Width", "6in")
AddElement(reportSection, "Page", Nothing)
Dim body As XmlElement = AddElement(reportSection, "Body", Nothing)
AddElement(body, "Height", "1.5in")
Dim reportItems As XmlElement = AddElement(body, "ReportItems", Nothing)
' Tablix element
Dim tablix As XmlElement = AddElement(reportItems, "Tablix", Nothing)
attr = tablix.Attributes.Append(doc.CreateAttribute("Name"))
attr.Value = "Tablix1"
AddElement(tablix, "DataSetName", "DataSet1")
AddElement(tablix, "Top", "0.5in")
AddElement(tablix, "Left", "0.5in")
AddElement(tablix, "Height", "0.5in")
AddElement(tablix, "Width", "3in")
Dim tablixBody As XmlElement = AddElement(tablix, "TablixBody", Nothing)
'TablixColumns element
Dim tablixColumns As XmlElement = AddElement(tablixBody, "TablixColumns", Nothing)
Dim tablixColumn As XmlElement = AddElement(tablixColumns, "TablixColumn", Nothing)
AddElement(tablixColumn, "Width", "1.5in")
tablixColumn = AddElement(tablixColumns, "TablixColumn", Nothing)
AddElement(tablixColumn, "Width", "1.5in")
'TablixRows element
Dim tablixRows As XmlElement = AddElement(tablixBody, "TablixRows", Nothing)
'TablixRow element (header row)
Dim tablixRow As XmlElement = AddElement(tablixRows, "TablixRow", Nothing)
AddElement(tablixRow, "Height", "0.5in")
Dim tablixCells As XmlElement = AddElement(tablixRow, "TablixCells", Nothing)
' TablixCell element (first cell)
Dim tablixCell As XmlElement = AddElement(tablixCells, "TablixCell", Nothing)
Dim cellContents As XmlElement = AddElement(tablixCell, "CellContents", Nothing)
Dim textbox As XmlElement = AddElement(cellContents, "Textbox", Nothing)
attr = textbox.Attributes.Append(doc.CreateAttribute("Name"))
attr.Value = "HeaderCountryName"
AddElement(textbox, "KeepTogether", "true")
Dim paragraphs As XmlElement = AddElement(textbox, "Paragraphs", Nothing)
Dim paragraph As XmlElement = AddElement(paragraphs, "Paragraph", Nothing)
Dim textRuns As XmlElement = AddElement(paragraph, "TextRuns", Nothing)
Dim textRun As XmlElement = AddElement(textRuns, "TextRun", Nothing)
AddElement(textRun, "Value", "CountryName")
Dim style As XmlElement = AddElement(textRun, "Style", Nothing)
AddElement(style, "TextDecoration", "Underline")
' TablixCell element (second cell)
tablixCell = AddElement(tablixCells, "TablixCell", Nothing)
cellContents = AddElement(tablixCell, "CellContents", Nothing)
textbox = AddElement(cellContents, "Textbox", Nothing)
attr = textbox.Attributes.Append(doc.CreateAttribute("Name"))
attr.Value = "HeaderStateProvince"
AddElement(textbox, "KeepTogether", "true")
paragraphs = AddElement(textbox, "Paragraphs", Nothing)
paragraph = AddElement(paragraphs, "Paragraph", Nothing)
textRuns = AddElement(paragraph, "TextRuns", Nothing)
textRun = AddElement(textRuns, "TextRun", Nothing)
AddElement(textRun, "Value", "StateProvince")
style = AddElement(textRun, "Style", Nothing)
AddElement(style, "TextDecoration", "Underline")
'TablixRow element (details row)
tablixRow = AddElement(tablixRows, "TablixRow", Nothing)
AddElement(tablixRow, "Height", "0.5in")
tablixCells = AddElement(tablixRow, "TablixCells", Nothing)
' TablixCell element (first cell)
tablixCell = AddElement(tablixCells, "TablixCell", Nothing)
cellContents = AddElement(tablixCell, "CellContents", Nothing)
textbox = AddElement(cellContents, "Textbox", Nothing)
attr = textbox.Attributes.Append(doc.CreateAttribute("Name"))
attr.Value = "CountryName"
AddElement(textbox, "HideDuplicates", "DataSet1")
AddElement(textbox, "KeepTogether", "true")
paragraphs = AddElement(textbox, "Paragraphs", Nothing)
paragraph = AddElement(paragraphs, "Paragraph", Nothing)
textRuns = AddElement(paragraph, "TextRuns", Nothing)
textRun = AddElement(textRuns, "TextRun", Nothing)
AddElement(textRun, "Value", "=Fields!CountryName.Value")
style = AddElement(textRun, "Style", Nothing)
' TablixCell element (second cell)
tablixCell = AddElement(tablixCells, "TablixCell", Nothing)
cellContents = AddElement(tablixCell, "CellContents", Nothing)
textbox = AddElement(cellContents, "Textbox", Nothing)
attr = textbox.Attributes.Append(doc.CreateAttribute("Name"))
attr.Value = "StateProvince"
AddElement(textbox, "HideDuplicates", "DataSet1")
AddElement(textbox, "KeepTogether", "true")
paragraphs = AddElement(textbox, "Paragraphs", Nothing)
paragraph = AddElement(paragraphs, "Paragraph", Nothing)
textRuns = AddElement(paragraph, "TextRuns", Nothing)
textRun = AddElement(textRuns, "TextRun", Nothing)
AddElement(textRun, "Value", "=Fields!StateProvince.Value")
style = AddElement(textRun, "Style", Nothing)
'End of second row
'End of TablixBody
'TablixColumnHierarchy element
Dim tablixColumnHierarchy As XmlElement = AddElement(tablix, "TablixColumnHierarchy", Nothing)
Dim tablixMembers As XmlElement = AddElement(tablixColumnHierarchy, "TablixMembers", Nothing)
AddElement(tablixMembers, "TablixMember", Nothing)
AddElement(tablixMembers, "TablixMember", Nothing)
'TablixRowHierarchy element
Dim tablixRowHierarchy As XmlElement = AddElement(tablix, "TablixRowHierarchy", Nothing)
tablixMembers = AddElement(tablixRowHierarchy, "TablixMembers", Nothing)
Dim tablixMember As XmlElement = AddElement(tablixMembers, "TablixMember", Nothing)
AddElement(tablixMember, "KeepWithGroup", "After")
AddElement(tablixMember, "KeepTogether", "true")
tablixMember = AddElement(tablixMembers, "TablixMember", Nothing)
AddElement(tablixMember, "DataElementName", "Detail_Collection")
AddElement(tablixMember, "DataElementOutput", "Output")
AddElement(tablixMember, "KeepTogether", "true")
Dim group As XmlElement = AddElement(tablixMember, "Group", Nothing)
attr = group.Attributes.Append(doc.CreateAttribute("Name"))
attr.Value = "Table1_Details_Group"
AddElement(group, "DataElementName", "Detail")
Dim tablixMembersNested As XmlElement = AddElement(tablixMember, "TablixMembers", Nothing)
AddElement(tablixMembersNested, "TablixMember", Nothing)
'End of Tablix, ReportItems, ReportSections
'Save XML document to file
doc.Save("Report1.rdl")
End Sub
Public Function AddElement(ByVal parent As XmlElement, ByVal name As String, ByVal value As String) As XmlElement
Dim newelement As XmlElement = parent.OwnerDocument.CreateElement(name, "https://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition")
parent.AppendChild(newelement)
If value IsNot Nothing Then
newelement.InnerText = value
End If
Return newelement
End Function
public void GenerateRdl()
{
// Create an XML document
XmlDocument doc = new XmlDocument();
string xmlData = "<Report " +
"xmlns=\"https://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition\">" +
"</Report>";
doc.Load(new StringReader(xmlData));
// Report element
XmlElement report = (XmlElement)doc.FirstChild;
AddElement(report, "AutoRefresh", "0");
AddElement(report, "ConsumeContainerWhitespace", "true");
//DataSources element
XmlElement dataSources = AddElement(report, "DataSources", null);
//DataSource element
XmlElement dataSource = AddElement(dataSources, "DataSource", null);
XmlAttribute attr = dataSource.Attributes.Append(doc.CreateAttribute("Name"));
attr.Value = "DataSource1";
XmlElement connectionProperties = AddElement(dataSource, "ConnectionProperties", null);
AddElement(connectionProperties, "DataProvider", "SQL");
AddElement(connectionProperties, "ConnectString", m_connectString);
AddElement(connectionProperties, "IntegratedSecurity", "true");
//DataSets element
XmlElement dataSets = AddElement(report, "DataSets", null);
XmlElement dataSet = AddElement(dataSets, "DataSet", null);
attr = dataSet.Attributes.Append(doc.CreateAttribute("Name"));
attr.Value = "DataSet1";
//Query element
XmlElement query = AddElement(dataSet, "Query", null);
AddElement(query, "DataSourceName", "DataSource1");
AddElement(query, "CommandText", m_commandText);
AddElement(query, "Timeout", "30");
//Fields element
XmlElement fields = AddElement(dataSet, "Fields", null);
XmlElement field = AddElement(fields, "Field", null);
attr = field.Attributes.Append(doc.CreateAttribute("Name"));
attr.Value = "CountryName";
AddElement(field, "DataField", "CountryName");
field = AddElement(fields, "Field", null);
attr = field.Attributes.Append(doc.CreateAttribute("Name"));
attr.Value = "StateProvince";
AddElement(field, "DataField", "StateProvince");
//end of DataSources
//ReportSections element
XmlElement reportSections = AddElement(report, "ReportSections", null);
XmlElement reportSection = AddElement(reportSections, "ReportSection", null);
AddElement(reportSection, "Width", "6in");
AddElement(reportSection, "Page", null);
XmlElement body = AddElement(reportSection, "Body", null);
AddElement(body, "Height", "1.5in");
XmlElement reportItems = AddElement(body, "ReportItems", null);
// Tablix element
XmlElement tablix = AddElement(reportItems, "Tablix", null);
attr = tablix.Attributes.Append(doc.CreateAttribute("Name"));
attr.Value = "Tablix1";
AddElement(tablix, "DataSetName", "DataSet1");
AddElement(tablix, "Top", "0.5in");
AddElement(tablix, "Left", "0.5in");
AddElement(tablix, "Height", "0.5in");
AddElement(tablix, "Width", "3in");
XmlElement tablixBody = AddElement(tablix, "TablixBody", null);
//TablixColumns element
XmlElement tablixColumns = AddElement(tablixBody, "TablixColumns", null);
XmlElement tablixColumn = AddElement(tablixColumns, "TablixColumn", null);
AddElement(tablixColumn, "Width", "1.5in");
tablixColumn = AddElement(tablixColumns, "TablixColumn", null);
AddElement(tablixColumn, "Width", "1.5in");
//TablixRows element
XmlElement tablixRows = AddElement(tablixBody, "TablixRows", null);
//TablixRow element (header row)
XmlElement tablixRow = AddElement(tablixRows, "TablixRow", null);
AddElement(tablixRow, "Height", "0.5in");
XmlElement tablixCells = AddElement(tablixRow, "TablixCells", null);
// TablixCell element (first cell)
XmlElement tablixCell = AddElement(tablixCells, "TablixCell", null);
XmlElement cellContents = AddElement(tablixCell, "CellContents", null);
XmlElement textbox = AddElement(cellContents, "Textbox", null);
attr = textbox.Attributes.Append(doc.CreateAttribute("Name"));
attr.Value = "HeaderCountryName";
AddElement(textbox, "KeepTogether", "true");
XmlElement paragraphs = AddElement(textbox, "Paragraphs", null);
XmlElement paragraph = AddElement(paragraphs, "Paragraph", null);
XmlElement textRuns = AddElement(paragraph, "TextRuns", null);
XmlElement textRun = AddElement(textRuns, "TextRun", null);
AddElement(textRun, "Value", "CountryName");
XmlElement style = AddElement(textRun, "Style", null);
AddElement(style, "TextDecoration", "Underline");
// TablixCell element (second cell)
tablixCell = AddElement(tablixCells, "TablixCell", null);
cellContents = AddElement(tablixCell, "CellContents", null);
textbox = AddElement(cellContents, "Textbox", null);
attr = textbox.Attributes.Append(doc.CreateAttribute("Name"));
attr.Value = "HeaderStateProvince";
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", "StateProvince");
style = AddElement(textRun, "Style", null);
AddElement(style, "TextDecoration", "Underline");
//TablixRow element (details row)
tablixRow = AddElement(tablixRows, "TablixRow", null);
AddElement(tablixRow, "Height", "0.5in");
tablixCells = AddElement(tablixRow, "TablixCells", null);
// TablixCell element (first cell)
tablixCell = AddElement(tablixCells, "TablixCell", null);
cellContents = AddElement(tablixCell, "CellContents", null);
textbox = AddElement(cellContents, "Textbox", null);
attr = textbox.Attributes.Append(doc.CreateAttribute("Name"));
attr.Value = "CountryName";
AddElement(textbox, "HideDuplicates", "DataSet1");
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!CountryName.Value");
style = AddElement(textRun, "Style", null);
// TablixCell element (second cell)
tablixCell = AddElement(tablixCells, "TablixCell", null);
cellContents = AddElement(tablixCell, "CellContents", null);
textbox = AddElement(cellContents, "Textbox", null);
attr = textbox.Attributes.Append(doc.CreateAttribute("Name"));
attr.Value = "StateProvince";
AddElement(textbox, "HideDuplicates", "DataSet1");
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!StateProvince.Value");
style = AddElement(textRun, "Style", null);
//End of second row
//End of TablixBody
//TablixColumnHierarchy element
XmlElement tablixColumnHierarchy = AddElement(tablix, "TablixColumnHierarchy", null);
XmlElement tablixMembers = AddElement(tablixColumnHierarchy, "TablixMembers", null);
AddElement(tablixMembers, "TablixMember", null);
AddElement(tablixMembers, "TablixMember", null);
//TablixRowHierarchy element
XmlElement tablixRowHierarchy = AddElement(tablix, "TablixRowHierarchy", null);
tablixMembers = AddElement(tablixRowHierarchy, "TablixMembers", null);
XmlElement 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");
XmlElement group = AddElement(tablixMember, "Group", null);
attr = group.Attributes.Append(doc.CreateAttribute("Name"));
attr.Value = "Table1_Details_Group";
AddElement(group, "DataElementName", "Detail");
XmlElement tablixMembersNested = AddElement(tablixMember, "TablixMembers", null);
AddElement(tablixMembersNested, "TablixMember", null);
//End of Tablix, ReportItems, ReportSections
//Save XML document to file
doc.Save("Report1.rdl");
}
public XmlElement AddElement(XmlElement parent, string name, string value)
{
XmlElement newelement = parent.OwnerDocument.CreateElement(name,
"https://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition");
parent.AppendChild(newelement);
if (value != null) newelement.InnerText = value;
return newelement;
}