CHAPTER 2 REPORT AUTHORING: DESIGNING EFFICIENT QUERIESconstant at or is less than one second, and the cost of the WHERE clause goes from 11 percent to only 4
percent. Also, it’s important to note in the performance analysis that the record count went up by only
35 records, from 3,924 to 3,959, by removing the “V” from the WHERE clause. You can see this in the lower-
right corner of Figure 2-6.
To take advantage of a report filter, you need to add a field—Services.ServiceTypeID—to the SELECT
and GROUP BY portions of the query, like so:
Select
...
Branch.BranchName AS Branch,
Services.ServiceTypeID
...
GROUP BY
...
Branch.BranchName,
Services.ServiceTypeID
You will use the additional field Services.ServiceTypeID as the filter value in the report that you will
be designing. By proceeding in this fashion, even though you’re returning more rows than you might
need for a particular report, you also gain the benefit of using this same base query for other reports
when you eventually make it a stored procedure, which you will do in the following sections. Other
reports might need to show service types other than visits, and this query will serve this purpose with
only slight modifications to the report. For example, you might need to investigate the cost or quantity of
supplies (a service type of “S”) used by employees. You can use this same query and stored procedure for
that report. We could make use of a parameterized stored procedure that would allow various reports to
pass in a value to be used to filter the results, but we’ll progress to that in a moment.
Figure 2-6. Viewing the execution plan with the modified query