1. | Right-click on the report in Solution Explorer and select View Code or view the report's RDL file in a text editor. |
Note
The accompanying code contains changes to the <CommandText> value to SELECT F1, F2, F3 FROM TBL ; however, the value of this node is irrelevant because it gets completely replaced by the sample application. You can decide if complete replacement of <CommandText> is desirable for each individual template.
2. | Edit the RDL and replace every occurrence of the first query field name with some unique value, such as F1. Repeat the procedure for the rest of the fields (F2 and F3, respectively). When going through the replacement process, please keep in mind that Report Designer adds spaces between capitalizations (that is, OrderId gets changed to Order Id ). This happens when Report Designer assigns values for the <Header> row report items. |
The resulting RDL/XML should look similar to the following (only key points are shown) :
[View full width]... <ConnectString> Data Source=localhost;Initial Catalog=AdventureWorks; Integrated Security=SSPI; </ConnectString> ... <ReportItems> <Table Name="table1"> <Details> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="F1"> <rd:DefaultName>F1</rd:DefaultName> <CanGrow>true</CanGrow> <Value>=Fields!F1.Value</Value> </Textbox> </ReportItems> </TableCell> ... </Details> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox2"> <CanGrow>true</CanGrow> <Value>F1</Value> </Textbox> </ReportItems> </TableCell> ... </Header> ... </Table> </ReportItems> ... <DataSets> <DataSet Name="DataSource"> <Query> <CommandText>SELECT F1, F2, F3 FROM TBL </CommandText> <DataSourceName>DataSource</DataSourceName> </Query> <Fields> <Field Name="F1"> <rd:TypeName>System.String</rd:TypeName> <DataField>F1</DataField> </Field> ... </DataSet> </DataSets>>
After changes are completed, you can deploy this RDL template. Because an RDL template conforms to RDL specifications, SSRS does not "complain" about such deployment despite the fact that this is not really a functional report.
To achieve the desired functionality, the sample uses the following namespaces:
System.IO to access stream handling, particularly the MemoryStream class
ReportService2005 to interact with SSRS to retrieve a template and to store a report
System.Web.Services.Protocols to handle SOAP exceptions
System.Xml to access XML handling, particularly the XmlDocument class
System.Data.SqlClient to validate a query and get field names
First, the sample loads a template and displays a connection string and the query ( CommandText ) from the template:
byte[] reportDefinition = rs.GetReportDefinition(templatePath); MemoryStream stream = new MemoryStream(reportDefinition); System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); doc.Load(stream); txtConnection.Text = doc.GetElementsByTagName("ConnectString")[0].InnerXml; txtQuery.Text = doc.GetElementsByTagName("CommandText")[0].InnerXml;
As a next series of steps, the sample
1. | Collects an updated string and a query from the UI and replaces the original values in the RDL template doc.GetElementsByTagName("ConnectString")[0].InnerXml = txtConnection.Text; doc.GetElementsByTagName("CommandText")[0].InnerXml = txtQuery.Text; |
2. | Executes a query to validate the query syntax SqlConnection con = new SqlConnection(txtConnection.Text); con.Open(); System.Data.SqlClient.SqlCommand cmd = new SqlCommand(txtQuery.Text, con); SqlDataReader reader = cmd.ExecuteReader(); |
3. | Gets the names of fields and replaces template strings with field names reader.Read(); String strTmp = doc.InnerXml.Replace("F1", reader.GetName(0)); strTmp = strTmp.Replace("F2", reader.GetName(1)); strTmp = strTmp.Replace("F3", reader.GetName(2)); doc.InnerXml = strTmp; |
4. | Writes the result of changes as a report to a specified location MemoryStream stream = new MemoryStream(); doc.Save(stream); Byte[] definition = stream.ToArray(); stream.Close(); Warning[] warnings = rs.CreateReport(txtResultName.Text, txtResultPath.Text, false, definition, null); |
After the final report is deployed, it can be used just like any report. The purpose of the sample is to demonstrate the basic capabilities of an RDL generation. The sample provides rudimentary error handling and can use some performance improvements. For example, you can find better alternatives to validate a query and to retrieve fields than executing a SqlDataReader .