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.
Parameter values can come from
(except Cleveland and
of south Texas). They can be provided by the user
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
controlsespecially if these are values that are
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
(and possibly most dangerous) way to capture a parameter values is to place a
in your UI and ask the user to enter the value. Figure 14.33 shows a
containing a prompted
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
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" (
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
. One way is to simply test the value as shown in Figure 14.34. This routine
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
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
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
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
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
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:
(incorporated into a
control). This approach lets the user pick one item from a drop-down list. This list is populated from a
that returns all known product colors.
A custom dialog that contains a
control populated with data returned by the same
used in the
population. I could not
as it is not supported.
No, I did not have to use a
to fetch the rowset used to populate these pick listsfrankly, it was almost as much trouble as using my own
instancesbut I'm trying to leverage this new technology and it did take fewer lines of code.
control with its two pick lists is shown in Figure 14.35. I find that the
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
-hosted controls, so we ended up having to populate the lists
Figure 14.35. Using a ToolStrip to host pick lists.
is shown in Figure 14.36. Note how both pick lists include an "<All>" item. This
the user to
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
or with any
-based pick list, you don't have to worry about the user entering invalid entries. However, with a
, you do. This means you still need a validation routine to make sure the entry
(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
to return a list of the valid colors. This query is executed by creating an instance of the
and a strongly typed
and executing the
method to populate the strongly typed
. I extract the list of colors from the
collection and move it to an array of objects acceptable to the
(hosted in the
) and the
(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
and a suitable
instance for each
parameter, and sets the
property of each
to the item chosen from the pick list. That is, unless the user has selected a
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
(as it appears that Reporting Services is doing) and concatenate it to the rest of the SELECT query
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
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
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
of packing tape.
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 (
) on the
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
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
be executed in
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
. 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
not just a
. This operation completes rowset population of the
which is ultimately passed to the
control while it's processing the report definition (RDLC) file. This
can be created via a
(as I've done so far), by building a
or by using a
to populate a
with one or more
objects. Later in this chapter, I'll discuss some of the advanced techniques that permit you to bind your own
object to the
. In any case, the rowset returned from the report query
referenced in the RDLC report definition. Sure, the
can contain extra columnsbut it
or expressions that match the
and types of the
referenced in the RDLC report definition.
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
in local mode. Yes, report parameters also map to query parameters when using the
in remote modeas when the
is hosting an RDL Reporting Services report. The techniques used here apply to both
Basically, report parameters are exposed via the
instance exposed by the
instance. This structure contains an array of
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
the parameters. Yes, parameters can be "
" 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
the parameter to accept several values.
Creating report parameters is accomplished via the Report
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
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
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
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
trips to the data source are needed to fetch
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.
Start from the Products By Profit example report I've been working with (
one of the existing reports to build
Select the report in the Report Designer and
the Report Report
menu item. This exposes the dialog shown in Figure 14.39.
Set up a new report parameter as shown. No, you don't need to define the
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.
report item in the Report Designer and choose
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
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
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.
Next, I need to set up a dialog to capture the minimum profit report parameter value at runtime and feed it into the
parameters. I accomplish that with a label and
control on the Windows Form, and I'm ready to go...well, almost.
In order for the
to use the parameter, I have to construct a
instance and populate its value with the UI
value. The code shown in Figure 14.41 does this.
Figure 14.41. Populate the ReportParameter instance and set the LocalReport parameters collection.
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
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.