Skip to main content Skip to footer

Customizing Report Designer: Making it Easy to Create Parameterized Reports

While working with Report Designer in Reports for WinForms, the first thought that may come in your mind is "How great would it be if my application had this or that functionality?" And why shouldn't your app have it? After all, you can almost do everything from creating reports to print, preview, import and export them using a single application. Reports for WinForms is a complete package for creating simple to complex reports of almost every need. My-App-My-Rule ReportDesigner is a royalty-free re-distributable application that can be customized to cater your own needs. Source code of the application is available and can be modified, localized and re-distributed to your clients, making them able to create and edit their reports. ComponentOne Studio controls are so flexible that you can't imagine a single feature that can't be incorporated with them. Having said that, I would like to extend the features of C1ReportDesigner in terms of creating parameterized reports using a customized dialog box. Basically, Report for WinForms allows to add parametric queries that display a built-in dialog box prompting the user for information such as criteria for retrieving records or the value of a report field. In order to create a parametric query, you need to edit the SQL statement in the RecordSource property of Report's DataSource and add a PARAMETERS clause to it. The syntax used to create such queries is the same as that used by Microsoft Access. Each item in the parametric query describes one parameter and includes the following information: Parameter name, Parameter type, Default value (in the same order).

"PARAMETERS [Critical Stock Level] Short 10;"

These parameters can be further used to filter the data (using WHERE clause) coming from the database. When the SQL statement is executed, the report shows a dialog box on-the-fly prompting the user for Parameter values. The values supplied by the user are plugged into the SQL statement and the report is generated only for the Values selected by the user (by using filters). The only catch with this functionality is that you need to modify the RecordSource manually by adding a Parameter clause in order to filter the data. Parameter Addition Dialog Box Being human, we need everything automatic, nothing manual :D. This blog explains about creating parametric queries using a dialog box (without writing any code in Report's RecordSource) . Customized Report Designer Just the way I wanted it!

Implementing the Parameters Form

To accomplish the same, a new Form named "ParametersForm" is added in C1ReportDesigner project. This Form contains various input controls which accept the Parameters' Name, Type and their default Values. These parameters are mapped with the existing database fields to evaluate a modified SQL query in order to prompt the user for the parameters and further filtering the data (from the database) based on Parameter values.

Working of ParameterAddition

Firstly, the already existing SQL query of C1Report is retrieved using C1Report.DataSource.RecordSource property. Once user adds the parameters and further maps them with DataBase fields to create filters, a modified SQL query is generated and gets assigned to the RecordSource property of report's DataSource.



string[] delimiterChars = { "--" };  
foreach (string parameter in _lbAddedParams.Items)  
{  
string[] words = parameter.Split(delimiterChars, StringSplitOptions.None);  
\_sAddedSQLQuery = \_sAddedSQLQuery + " [" + words[0] + "] " + words[1];  
if (words[1] == "DateTime" || words[1] == "Boolean" || words[1] == "Short")  
\_sAddedSQLQuery = \_sAddedSQLQuery + " " + words[2];  
else if (words[1] == "String")  
{  
if (words[2].StartsWith("\\""))  
words[2] = words[2].Remove(0, 1);  
if (words[2].EndsWith("\\""))  
words[2] = words[2].Remove(words[2].Length - 1, 1);  

\_sAddedSQLQuery = \_sAddedSQLQuery + " \\"" + words[2] + "\\"";  
}  
\_sAddedSQLQuery = \_sAddedSQLQuery + ",";  
}  
if (\_sAddedSQLQuery.EndsWith(",") || \_sAddedSQLQuery.EndsWith(";"))  
\_sAddedSQLQuery = \_sAddedSQLQuery.Remove(_sAddedSQLQuery.Length - 1);  
if(_lbAddedParams.Items.Count>0)  
\_sAddedSQLQuery = \_sAddedSQLQuery + ";";  

foreach (string filter in _lbFiltersList.Items)  
{  
\_sAddedSQLFilter = \_sAddedSQLFilter + filter + " AND ";  
}  
if (\_sAddedSQLFilter.EndsWith(",") || \_sAddedSQLFilter.EndsWith(";"))  
\_sAddedSQLFilter = \_sAddedSQLFilter.Remove(_sAddedSQLFilter.Length - 1);  
else if (_sAddedSQLFilter.EndsWith(" AND "))  
\_sAddedSQLFilter = \_sAddedSQLFilter.Remove(_sAddedSQLFilter.Length - 5);  
if (\_sSQLQuery.EndsWith(",") || \_sSQLQuery.EndsWith(";"))  
\_sSQLQuery = \_sSQLQuery.Remove(_sSQLQuery.Length - 1);  
if (\_sAddedSQLFilter.Length > 1 && \_sAddedSQLQuery.Length > 1 && _sOrderByQuery.Length > 1)  
\_report.DataSource.RecordSource = "PARAMETERS " + \_sAddedSQLQuery + \_sSQLQuery + " WHERE " + \_sAddedSQLFilter + " ORDER BY " + _sOrderByQuery + ";";  
else if (\_sAddedSQLFilter.Length > 1 && \_sAddedSQLQuery.Length > 1 && _sOrderByQuery.Length <= 0)  
\_report.DataSource.RecordSource = "PARAMETERS " + \_sAddedSQLQuery + \_sSQLQuery + " WHERE " + \_sAddedSQLFilter + ";";  
else if (\_sAddedSQLFilter.Length > 1 && \_sAddedSQLQuery.Length <= 0 && _sOrderByQuery.Length > 1)  
\_report.DataSource.RecordSource = \_sSQLQuery + " WHERE " + \_sAddedSQLFilter + " ORDER BY " + \_sOrderByQuery + ";";  
else if (\_sAddedSQLFilter.Length > 1 && \_sAddedSQLQuery.Length <= 0 && _sOrderByQuery.Length <= 0)  
\_report.DataSource.RecordSource = \_sSQLQuery + " WHERE " + _sAddedSQLFilter + ";";  
else if (\_sAddedSQLFilter.Length <= 0 && \_sAddedSQLQuery.Length > 1 && _sOrderByQuery.Length > 1)  
\_report.DataSource.RecordSource = "PARAMETERS " + \_sAddedSQLQuery + \_sSQLQuery + " ORDER BY " + \_sOrderByQuery + ";";  
else if (\_sAddedSQLFilter.Length <= 0 && \_sAddedSQLQuery.Length > 1 && _sOrderByQuery.Length <= 0)  
\_report.DataSource.RecordSource = "PARAMETERS " + \_sAddedSQLQuery + _sSQLQuery + ";";  
else if (\_sAddedSQLFilter.Length <= 0 && \_sAddedSQLQuery.Length <= 0 && _sOrderByQuery.Length > 1)  
\_report.DataSource.RecordSource = \_sSQLQuery + " ORDER BY " + _sOrderByQuery + ";";  
else  
\_report.DataSource.RecordSource = \_sSQLQuery + ";";  
MessageBox.Show("SQL Query Modified Successfully");  
this.Close();  


Download Sample >>

Try ComponentOne Studio v2.5 Today!

MESCIUS inc.

comments powered by Disqus