Managing Parameters

At this point, I expect you to understand that your reports are going to need to manage, limit, sort, filter, and focus the report rowset using parameters. Because of the nature of today's applications, users expect applications to be interactivethey rarely settle for static reports that don't reflect current or user-selectable sets of information. In order to adapt reports so they meet user requirements, you're going to need to use one or more parameters. Basically, ReportViewer parameters are organized into two basic types:

• Query parameters: These parameters are passed to the data source query engine to alter the TOP, WHERE, or ORDER BY or other SQL clauses to change which or how many rows are returned from the database query. Unlike RDL-based reports, the query parameters are not stored in the RDLC report description. However, when the ReportViewer is used to host remote reports, the RDLC maps a report parameter to each query parameter. In this case, your code needs to populate these parameters before launching the report.

• Report parameters: These parameters are used to alter the report properties and behavior at runtime when working with RDLC reports. For example, I could have set a "ShowColor" property to let the user choose whether or not to show the product color background in color. These parameters can also be used to filter, sort, and refine the rowset data using post-fetch techniques, as I discussed earlier. When working with remote RDL reports, the report parameters serve to expose the underlying query parameters, as well as those parameters used to feed report expressions.

One of the challenges you'll face when working with the ReportViewer paradigm is that all parameter-management tasks fall on your shoulders. Unlike the Reporting Services BIDS development process, RDLC reports do not manage the report or query parameters for you in the context of the report. Remember Figure 14.3? The "Low Price" parameter TextBox was generated by the RDL report interfacenot by a hand-coded UI. The BIDS Report Designer is capable of managing a wealth of parameter optionsnone of which requires additional code. Even if your RDLC file is built from an imported parameter-driven RDL report definition, the report parameters section is ignored unless you build a custom Parameters structure in code to populate it. This means you're in for considerable extra work, including (but not limited to) capturing the parameters, managing pick lists, and passing the parameters into structures recognized by the RDLC compiler. Let's get startedI'm losing daylight.

IMHO

If wishes were horses, I would need to clean the streets more often.

One of the tools that might make sense is a routine that generates the UI controls needed to manage parameters for any given RDL report. Perhaps, one of us will write that if we get bored.

Capturing Parameters

Parameter values can come from anywhere (except Cleveland and parts of south Texas). They can be provided by the user punching keys or doing cut/copy/paste mouse operations, from code-generated values, from data retrieved from a pick list, from external devices like a GPS coordinate generator, or from your cat walking on the keyboard. Ideally, your report (and your application) avoids scenarios where the user is required to enter anything into TextBox controlsespecially if these are values that are concatenated into your SQL. That's because SQL injection attacks are a very common problem, and report parameters are an ideal place to enter nasty SQL.

Let's walk through the typical parameter-management scenarios and how to implement them.

Prompting for a Parameter Value

The easiest (and possibly most dangerous) way to capture a parameter values is to place a TextBox in your UI and ask the user to enter the value. Figure 14.33 shows a ToolStrip containing a prompted TextBox used to supply a value to a parameter that limits the scope of the rows returned for the report.

Figure 14.33. Using a prompted TextBox to capture a parameter.

Here, the user can enter (almost) anythingas long as it's a number between 0 and 10,000. Behind the scenes, I've done a number of things to make sure this value is "reasonable" (falls with the specified values) and safe. These include:

• Setting a reasonable default value. Ideally, the last value entered should be retained in the user settings so when the user returns to this application, the value last entered is the default.

• Limiting the type and scope of the object value captured by the TextBox. One way is to simply test the value as shown in Figure 14.34. This routine validates the Text property to ensure that it's numeric and falls within a reasonable range.

  Figure 14.34. Validating the user-input string.

  

• Make sure that the value is applied to an ADO.NET Command object's Parameter objectnot simply concatenated to the SQL. This simple step goes a long way toward preventing SQL injection attacks.

Prompting with a Pick List

In cases where the parameter can be only one or more specific items, a pick-list of some kind is the best way to capture the parameter value. Generally, you'll want to populate the pick list early in the application if the content of the list does not change that frequently. If the list changes more frequently, of course, the pick list of valid options will have to be populated more frequentlyperhaps each time it's referenced.

One of the new features of Reporting Services 2005 is the ability to expose one or more "multi-choice" parameter lists. In this case, Reporting Services knows how to present and populate a list of choices that can be populated via a static list or a query. It's this functionality that I'm implementing here with my own code and controls.

If you use the query approach to populate a pick list, you'll have to make an extra round-trip to the data source to fetch the values. Alternatives to this query can be a locally persisted data store of some kind that maintains a suitable list, but this means building your own handlers, files, and many other issues. I find it's not at all onerous to simply fill the pick lists from a common command that includes SELECT statements for all of the pick-list rowsets. Of course, if you're using JET or some other lightweight database that does not support multiple SELECT commands, you'll need to find another alternative, like paper tape.

For this example, I built two types of pick lists:

• A query-populated ComboBox (incorporated into a ToolStrip control). This approach lets the user pick one item from a drop-down list. This list is populated from a TableAdapter that returns all known product colors.

• A custom dialog that contains a CheckedListBox control populated with data returned by the same TableAdapter used in the ComboBox population. I could not incorporate the CheckedListBox into the ToolStrip, as it is not supported.

No, I did not have to use a TableAdapter to fetch the rowset used to populate these pick listsfrankly, it was almost as much trouble as using my own Connection, Command, and DataTable instancesbut I'm trying to leverage this new technology and it did take fewer lines of code.

The ToolStrip control with its two pick lists is shown in Figure 14.35. I find that the ToolStrip is a pretty cool control, as it provides a clear and intuitive way to generate the needed UI elements. The problem with this approach is that it's harder to bind to the ToolStrip-hosted controls, so we ended up having to populate the lists ourselves.

Figure 14.35. Using a ToolStrip to host pick lists.

The populated CheckedListBox is shown in Figure 14.36. Note how both pick lists include an "<All>" item. This permits the user to indicate an ambiguous item choice. In the query that returns the filtered rowset, I include logic in the WHERE clause to ignore the pick list items and simply accept any color value when "<All>" is passed as a parameter.

Figure 14.36. The populated CheckedListBox pick list.

In the case of the CheckedListBox or with any ListBox-based pick list, you don't have to worry about the user entering invalid entries. However, with a ComboBox, you do. This means you still need a validation routine to make sure the entry chosen (or entered) is still valid.

Creating a Pick-List Query

By now, you should be very familiar with the techniques used to return a rowset. That's all you need to populate you pick-list control. In the example, I create a TableAdapter to return a list of the valid colors. This query is executed by creating an instance of the TableAdapter and a strongly typed DataTable, and executing the TableAdapter instance Fill method to populate the strongly typed DataTable. I extract the list of colors from the Rows collection and move it to an array of objects acceptable to the ComboBox (hosted in the ToolStrip) and the CheckedListBox (hosted in its own dialog).

Figure 14.37 shows the code to generate the object array used to populate the pick lists. Note that I add "<All>" to the list (line 13) so that all color lists support the option to select all items.

Figure 14.37. Fetching the list of known colors.

Passing the Picked Items to a Query Parameter

Passing a single value to a T-SQL query is very easy. One simply creates a parameterized CommandText and a suitable Parameter instance for each marked parameter, and sets the Value property of each Parameter to the item chosen from the pick list. That is, unless the user has selected a list of items (chosen more than one item from the list of possible choices). In this case, one needs to either generate the T-SQL WHERE clause on-the-fly (as it appears that Reporting Services is doing) and concatenate it to the rest of the SELECT query or leverage the WHERE clause IN operator. As I've discussed before, it's not possible (due to SQL syntax limitations) to pass a parameter to an IN operator. To deal with this shortcoming, I pass a table populated with the items to the IN operator.

The trick is to populate this (temporary) table from the delimited list of selected items chosen by the userthat's what my CLR function DelimitedStringToTable does. I deployed this commonly used CLR function to the Common database so that it could be accessed by any database that needs it but still could be centrally managed. Of course, this assumes that your DBA will let you near the "enable CLR" switch. Most DBAs have this held in the off position with a dozen layers of packing tape.

Tip

I use a "common" database to host functions that can be used by more than one database.

To return the "filtered" set of row for the report, I created another method (FillByColor) on the TableAdapter used in the previous (parameterless) example. The parameter-driven query is shown in Figure 14.38. Note that it uses the CLR function drawn from the Common database to convert the delimited list to a temporary SQL table used by the IN clause.

Figure 14.38. The Parameter-based FillByColor used to return rows that match one or more colors.

Sure, there are lots of other ways to limit or focus your report rowset query. These include (but are not limited to):

• Using the WHERE clause to focus the rows using specific criteria either passed by parameter-driven expressions or generated by other means.

• Using the TOP clause to cap the number of rows returned by the query based on a value or simply the top N percent of the available rowsets. This is a great way to rank rowset valuesreturning the top 10 salespeople or the top 5% of the stores based on volume.

• Using the WHERE clause IN operator, as illustrated earlier, to include a list of items that further refine the inbound rowset.

Running the Report Query

Yes, unlike Reporting Services, your application code is responsible for executing the report queriesthose that populate the pick lists and, ultimately, those that return the rowset used to populate the report RDLC fields. You decide when these queries need to run, but I suggest that they not be executed in Form_Load, as this delays the painting of your form and means the user stares at a blank screen until the query is executed and the report processed. Generally, your application should collect any focusing parameters from the user before launching off on a report query that might take several minutes to the rest of the afternoon to execute.

Note that all of the techniques I describe here execute code that executes the query and returns a DataTablenot just a DataReader. This operation completes rowset population of the DataTable, which is ultimately passed to the ReportViewer control while it's processing the report definition (RDLC) file. This DataTable can be created via a TableAdapter (as I've done so far), by building a DataTable from a DataReader, or by using a DataAdapter Fill to populate a DataSet with one or more DataTable objects. Later in this chapter, I'll discuss some of the advanced techniques that permit you to bind your own DataTable object to the ReportViewer. In any case, the rowset returned from the report query must contain the Fields referenced in the RDLC report definition. Sure, the DataTable can contain extra columnsbut it must return columns or expressions that match the names and types of the Fields referenced in the RDLC report definition.

Report Parameters

If you recall, a bit earlier in this chapter I mentioned a second type of parameterthose used by the RDLC to filter, sort, and alter the behavior or how information is shown in the report. These are called "report" parameters. Remember, report parameters map only to expressions in an RDL report used by the ReportViewer in local mode. Yes, report parameters also map to query parameters when using the ReportViewer in remote modeas when the ReportViewer is hosting an RDL Reporting Services report. The techniques used here apply to both ReportViewer modes.

Basically, report parameters are exposed via the ReportParameterInfoCollection instance exposed by the LocalReport instance. This structure contains an array of ReportParameterInfo objects that define (in infinite detail) each parameter defined for the report. With its properties, you can construct a sophisticated prompting dialog to capture the values to be passed to the reports that consume the parameters. Yes, parameters can be "multi-value" and have more than one value. For example, if you're building a dialog to capture valid state codes, but your report logic permits more than one selection, you can designate the parameter to accept several values.

Creating report parameters is accomplished via the Report Parameters dialog exposed by the Visual Studio IDE when focused on an RDLC report definition. This dialog (as shown in Figure 14.39) permits you to name the parameter and set the datatype, the prompt (if needed), as well as default values. Unlike with Reporting Services, you can't automatically populate the parameter permissible values at runtimeat least, not automatically. And no, the ReportViewer control won't prompt for parametersit's up to you to add logic to your host Windows Form or ASP page to capture the values.

Figure 14.39. Constructing a report parameter.

Let's step through the scenario I just demonstrated to set the lowest acceptable profit and the desired list of colors, but in this example, I'll use report parameters to do the same job. This means I'll accept far more rows from the query and filter "out" those rows based on report parameter settings. The advantage to this approach is that once the report is rendered for the first time, no subsequent trips to the data source are needed to fetch subsets or to reorder data. Sure, you still might need to capture the values of the report parameters using UI elementsthat part has not changed. The only part that will change is how these values are applied to modify the behavior of the report.

1.	Start from the Products By Profit example report I've been working with (prjLocalReport). I copied one of the existing reports to build ProductByProfitRptParm.rdlc.
2.	Select the report in the Report Designer and open the Report | Report Parameters menu item. This exposes the dialog shown in Figure 14.39.
3.	Set up a new report parameter as shown. No, you don't need to define the Prompt property unless you plan to use the parameter definition to feed a UI element to capture the parameter value. It's okay to specify a default value, as this makes debugging simpleryou won't have to define a "real" value until you're ready.
4.	Select the Table report item in the Report Designer and choose Properties and then the Filters tab. This opens the dialog shown in Figure 14.40. Note that I already have several filters in placethe first hides rows that have no color defined. The second filter makes sure that the cost and list price are not NULL. The new filter stipulates that all rows where the profit is less than the minimum acceptable value are hidden. This value is extracted from the MinimumProfit report parameter at runtime (when the report is rendered). Note the somewhat peculiar syntax. This is often (usually) necessary because the RDL/RDLC compiler is a bit anal about datatypes. It requires that both the Expression type and the Value type be the same. This means it's best to simply code an expression that resolves to True or False and test for that Boolean state in the Operator/Value pair. Figure 14.40. Setting the Filter property to limit visible rows based on the report parameter.
5.	Next, I need to set up a dialog to capture the minimum profit report parameter value at runtime and feed it into the LocalReport parameters. I accomplish that with a label and TextBox control on the Windows Form, and I'm ready to go...well, almost.
6.	In order for the ReportViewer to use the parameter, I have to construct a ReportParameter instance and populate its value with the UI TextBox value. The code shown in Figure 14.41 does this. Figure 14.41. Populate the ReportParameter instance and set the LocalReport parameters collection. The SetParameters method cannot be called until the RDLC file is set. This method also validates the parametersthey must exist and conform to basic type validation. This means you'll have to be sure to pre-define the parameters (as described earlier) before trying to apply a value. Basically, this process maps the existing RDLC-defined report parameters with a matching array of ReportParameter instances.

Report parameters can be used in expressions all through the RDLC report definition. This means you can use them to manage the look and feel of the report, filter data in (or out), customize the text boxes used in headings, or anywhere else and expression is exposed.