Custom Criteria

CoverStory

LANGUAGES: C# | VB.NET

 

Custom Criteria

Customize Data-driven Report Criteria Pages

 

By Carl Ganz, Jr.

 

One of the most time-consuming tasks involved in building any application is the customization of the different criteria pages for the reports. This article illustrates how to centralize your reporting criteria interface into one page and dynamically create report criteria pages at run time using definitions stored in a table.

 

The controls typically required for criteria pages are text/numeric input boxes, date controls, checkboxes, listboxes, and comboboxes. Radio buttons are not necessary because comboboxes can handle the same choices and are much easier to work with when using data-driven programming techniques.

 

As a general rule, date criteria should be set up in a from/to layout specified by the user as a range. If a user only needs one day s worth of information, the from/to dates can be set to the same date. If the particular parameter truly requires only a single date, then certainly offer the user only one date box. Your validation code should preclude the user from entering a from date that is later than the to date (and any other such logical inconsistencies). Optionally, the to date can be defaulted to the system date as user needs require. Because there s normally very little free-form data you would pass to a report engine, most of the error checking can occur before the user selections are submitted to the reporting tool.

 

Checkboxes handle Boolean values; textboxes can allow the user to restrict the output based on free-form text a last name, for example. Often you may wish to treat all entries in the textbox as partial search criteria. For example, if in filtering by last name the user enters Sm , the report could return Smoot , Smith , Smythe , etc. Depending on the flavor of SQL you are using, this can be accomplished by using the LIKE keyword and appending a percent sign to the end of the search value:

 

SELECT *

FROM Employees

WHERE LastName LIKE 'Sm%'

 

Listboxes provide the most flexible criteria control. Use of a listbox implies that the user may select more than one option. Each listbox should have a button underneath it that allows the user to clear the choices made. This way if a user wishes to run a report for both the Human Resources and Manufacturing departments, it is rather easy to do so. In those cases where the user is permitted to select only one out of a set of options, use a combobox. In both cases, each control has two columns. The first is invisible to the user and contains the unique ID of the table that contains the filter options. The second column contains the text description the user would recognize.

 

CheckBoxList controls, specifically, are usually best for making multiple selections on an individual control. Though you can certainly accomplish the same thing with a ListBox control, my experience has been that the user can easily forget to press the Ctrl key when making multiple selections and may end up deselecting everything selected so far. This is not a problem with CheckBoxList controls. ListBox controls are more appropriate in cases where you need mutually exclusive lists. For example, when you want two adjacent ListBox controls so that when the user double-clicks on the first listbox, the choice is moved to the second, and vice versa. The reason a CheckBoxList control is not being used here is because the id values assigned to it through the ListItem object do not persist client side, and therefore cannot be retrieved through JavaScript. Microsoft Knowledge Base article Q309338 (http://support.microsoft.com/kb/309338) explains this problem in greater detail. CheckBoxList controls work very well when building a WinForms interface.

 

Dynamic Criteria Controls

The data-driven techniques for each of the criteria controls are very similar to one another. Therefore, we ll examine the code for creating ListBox controls. ListBoxes are the most complex of the controls and, therefore, illustrate every feature.

 

You can set the definitions for your criteria controls in a table. The table structure in Figure 1 shows the data needed to display a criteria screen. Each report will have as many rows in the table as it has parameters.

 

Column Name	Data Type	Description
ReportID	Int	ID of report to which these criteria controls belong.
ControlName	Varchar	Name of the control object using txt , lst , etc. prefix.
ParameterName	Varchar	Name of the report parameter to which the entered value is assigned.
Label	Varchar	Text used to described the control on the Web page.
Type	Tinyint	Enumerated value indicating the type of control: ComboBox, ListBox, etc.
DataSource	Varchar	Connection string for data-populated controls.
StoredProc	Varchar	Name of the stored procedure, the value column, and the description column that populated the control separated by semi-colons.
DefaultValue	Varchar	Default value of the control.
Left	Int	Left position on the Web page.
Top	Int	Top position on the Web page.
Width	Int	Width position on the Web page.
Height	Int	Height position on the Web page.

Figure 1: Set the definitions for your criteria controls in a table.

This isn t a complete list of attributes for the various controls. You also could add a Required column that tells the engine to generate the necessary JavaScript to force the user to make a selection. Likewise, you could have a JavaScript column that contains a block of JavaScript code that handles validation. You also could have a RegEx column that contains the regular expression that formats input; for example, a valid e-mail address or a masked edit.

 

Suppose you want to display a listbox to the user that will allow them to select none, one, or multiple items. You can accomplish this with the following line of code:

 

ShowListBox(szControlName,

        szParameterName,

        szLabel,

        szDataSource,

        szStoredProc,

        szDefaultValue,

        iLeft,

        iTop,

        iWidth,

        iHeight);

 

This code will display the Web page shown in Figure 2.

 

Figure 2: Filter departments.

 

The goal is to retrieve the user selections client side only because you can always pass the values to the server via hidden controls. Because of the increasing popularity of asynchronous JavaScript (AJAX), more and more developers are avoiding full-page postbacks for tasks such as report generation. By retrieving the user selections client side and invoking an on-demand reporting Web service asynchronously, you can display the report output with minimal server traffic. This approach also simplifies the UI code because we don t need to maintain state between postbacks.

 

The ShowListBox method invokes additional methods that display the Label, ListBox, and Button objects that make up the set of controls. These controls are managed in the classes shown in Figure 3.

 

abstract class ControlManager

{

  private string szName;

  private Label oLabelControl;

  public string Name

  {

     get { return szName; }

     set { szName = value; }

  }

  public Label LabelControl

  {

     get { return oLabelControl; }

     set { oLabelControl = value; }

  }

}

class ListBoxManager : ControlManager

{

  private System.Web.UI.WebControls.ListBox oListBoxControl;

  private Button oButtonControl;

  public System.Web.UI.WebControls.ListBox ListBoxControl

  {

     get { return oListBoxControl; }

     set { oListBoxControl = value; }

  }

  public Button ButtonControl

  {

     get { return oButtonControl; }

     set { oButtonControl = value; }

  }

}

Figure 3: ControlManager and ListBoxManager classes.

 

Because every control has a name, and almost all have an associated Label control, these properties are encapsulated in the ControlManager base class from which the individual control s classes, like ListBoxManager and TextBoxManager, inherit. To display the control, you must pass the control name to the ShowListBox method (see Figure 4), along with the data source information, the dimensions of the listbox, and the caption. ShowListBox instantiates an object of type ListBoxManager, which receives the instantiated objects of the Label, ListBox, and Button types.

 

private void ShowListBox(string szControlName,

                 string szParameterName,

                 string szLabel,

                 string szDataSource,

                 string szStoredProc,

                 string szDefaultValue,

                 int iLeft,

                 int iTop,

                 int iWidth,

                 int iHeight)

{

  ListBoxManager oListBoxManager;

  oListBoxManager = new ListBoxManager();

  oListBoxManager.Name = szControlName;

  oListBoxManager.LabelControl =

     AddDynamicLabel(szControlName, iLeft, iTop, szLabel);

  oListBoxManager.ListBoxControl =

     AddDynamicListBox(szControlName, szDataSource,

     szStoredProc, iLeft, iTop + 20, iWidth, iHeight);

  oListBoxManager.ButtonControl =

     AddDynamicListBoxButton(szControlName, iLeft,

     iTop + iHeight + 20, iWidth, 23, szLabel);

}

Figure 4: The ShowListBox method.

 

The AddDynamicLabel method instantiates a Label object and assigns its location via the Style method (see Figure 5). The new control is then added to the Controls collection of the owner Panel object, which displays it to the user.

 

private Label AddDynamicLabel(string szControlName,

  int iLeft,

  int iTop,

  string szCaption)

{

  Label oLabel;

  

  oLabel = new Label();

  oLabel.Style["position"] = "absolute";

  oLabel.Style["left"] = iLeft.ToString() + "px";

  oLabel.Style["top"] = iTop.ToString() + "px";

  oLabel.Text = szCaption;

  Panel1.Controls.Add(oLabel);

  return oLabel;

}

Figure 5: The AddDynamicLabel method.

 

Likewise, the ListBox control itself is displayed in a similar fashion, as illustrated in the AddDynamicListBox method shown in Figure 6. The SelectionMode property is always set to multiple selections; otherwise, a combobox would suffice. The szStoredProc parameter contains a semi-colon-delimited list of the stored procedure name, the value id, and the displayed description. The LoadListBox method will parse this string into its three components. Using the connection string supplied by szDataSource, it will connect to the database, execute the stored procedure, and load the appropriate data columns into the ListBox control.

 

private System.Web.UI.WebControls.ListBox

  AddDynamicListBox(string szControlName,

  string szDataSource,

  string szStoredProc,

  int iLeft,

  int iTop,

  int iWidth,

  int iHeight)

{

  System.Web.UI.WebControls.ListBox oListBox;

  

  oListBox = new System.Web.UI.WebControls.ListBox();

  LoadListBox(oListBox, szDataSource, szStoredProc);

  oListBox.ID = szControlName;

  oListBox.Style["position"] = "absolute";

  oListBox.Style["left"] = iLeft.ToString() + "px";

  oListBox.Style["top"] = iTop.ToString() + "px";

  oListBox.Style["height"] = iHeight.ToString() + "px";

  oListBox.Style["width"] = iWidth.ToString() + "px";

  oListBox.BorderStyle = BorderStyle.Solid;

  oListBox.SelectionMode = ListSelectionMode.Multiple;

  Panel1.Controls.Add(oListBox);

  return oListBox;

}

Figure 6: The AddDynamicListBox method.

 

The Button control is also created and displayed in a similar fashion as the Label and ListBox controls, as shown in Figure 7.

 

private Button AddDynamicListBoxButton(string szControlName,

  int iLeft,

  int iTop,

  int iWidth,

  int iHeight,

  string szCaption)

{

  Button oButton;

  oButton = new Button();

  oButton.Style["position"] = "absolute";

  oButton.Style["left"] = iLeft.ToString() + "px";

  oButton.Style["top"] = iTop.ToString() + "px";

  oButton.Style["width"] = iWidth.ToString() + "px";

  oButton.Text = "Clear Selected " + szCaption;

  oButton.Attributes.Add("onclick",

     "return DeselectAll('" + szControlName + "')");

  Panel1.Controls.Add(oButton);

  return oButton;

}

Figure 7: The AddDynamicListBoxButton method.

 

The Button control triggers JavaScript code that, when clicked, must clear the selections in the ListBox with which it is associated. The generic JavaScript function that clears the selected options in the ListBox is shown in Figure 8. The Button s onclick event is wired to this JavaScript function using the Attributes.Add method.

 

function DeselectAll(szListBox)

{

  var oListBox = document.getElementById(szListBox);

  

  for (i=0; i < oListBox.options.length; i++)     

     oListBox.options[i].selected = false;

  

  return false;

}

Figure 8: The DeselectAll method.

 

Extracting the User Selections

After the user has completed entering their report criteria, the Get Data button can generically retrieve the information one control at a time. In the code shown here, the selected departments are retrieved and displayed in an alert box:

 

function GetData()

{        

  var szDepartments = "Departments: " +

ParseIt("ListBox6", false, true) + "\n";

  alert(szDepartments)

  return false;

}

 

The JavaScript ParseIt function shown in Figure 9 iterates the ListBox and extracts the data.

 

function ParseIt(szListBox, bQuotes, bCheckedOnly)

{

  var oListBox = document.getElementById(szListBox);

  var szResult = new String('');

  var szQuotes = '';

  var szValue = '';

  

  if (bQuotes)

     szQuotes = "'";

  for (i=0; i < oListBox.options.length; i++)

  {

     szValue = oListBox.options[i].value;

     

     if (bCheckedOnly)

     {

        if (oListBox.options[i].selected)

           szResult += szQuotes + szValue + szQuotes + ",";

     else

           szResult += szQuotes + szValue + szQuotes + ",";

  

  if (szResult.length > 0)

     szResult = szResult.substring(0, szResult.length - 1);

  

  return szResult;

}

Figure 9: The JavaScript ParseIt function.

 

The other controls are handled in a fashion similar to the ListBox. Ultimately, the data definitions will produce the screen shown in Figure 10.

 

Figure 10: Dynamically generated criteria screen.

 

Using these techniques can dramatically reduce the effort required to produce and maintain report criteria screens. By storing the definitions in a database table, you can easily add new criteria for a report, or provide support for a brand new report, without releasing another version of the application.

 

Source code accompanying this article is available for download.

 

Carl Ganz, Jr. is president of Seton Software Development, Inc., a provider of software design and development services located in Raritan, New Jersey. He has an MBA in Finance from Seton Hall University and is the author of three books on software development, as well as dozens of articles on Visual Basic, C#, and Microsoft .NET technology. He is the president and founder of the New Jersey Visual Basic User Group and has been a featured speaker at software development conferences in both the US and Germany. Contact Carl at mailto:[email protected].

 

Notes

When running the sample application, please adjust the database path on line 638 as needed. In addition, please adjust the database path in the DataSource column of the ReportParameters table in the MDB file.

 

Also, add the Access database inside the C:\Articles\DataDrivenCriteriaScreens directory. You should create a directory if it doesn t exist.