CHAPTER 6 BUILDING REPORTSFigure 6-28 Report with populated parameter selections
You could perform the same steps for the ServiceLogCtgryID parameter and provide a valid drop-
down list from the table values. However, since you may also be viewing the report in a custom report
viewer that will also accept parameter values, this particular parameter value is of little use to you now
for direct user input. That being the case, it will be beneficial to take advantage of another new and
much needed feature, the ability to hide parameters. This functionality was added in Service Pack 2 for
SSRS for SQL Server 2000 and is available all other releases since. Sometimes a parameter can and
should be populated by events other than user input. In these instances, users will only be confused by
seeing these additional parameters. In the Report Parameters dialog box, select the ServicesLogCtgryID
property, and check the Hidden box. It will also be beneficial to modify the time-based parameters
(Service Year and Service Month) for this report. Time-based values are often tricky to deal with because
of the special formatting needs of the DateTime datatype, which can store years, months, and days as
well as hours, minutes, and seconds. The procedures for setting up the Service Year and Service Month
parameters with valid values is almost identical to the Branch and Employee procedures covered earlier,
with the exception that the Service Year needs to default to the current year and not NULL.
The first step is to create a dataset for the Service Year and Month parameters based on the service
date, which is the field ChargeServiceStartDate in the stored procedure. You will use the DatePart and
DateName functions in the two queries to derive valid values. The valid values for the dates are
contingent upon their existence in the table, so, for example, if your data contained values for 2009 and
2010, only those two years would show up in the drop-down list. Populating the date values in this way
precludes the user from having to enter a date and also prevents the report designer from having to
hard-code year and month values into the report. These Year and Month datasets have already been
added to the report. However, we need to set the parameters Available Values to use Get values from
query as we did previously. For ServiceMonth, use the Month dataset and set ServiceYear to use the Year
dataset.
Listing 6-2 shows the two queries that drive the parameter values.