Database Reference
In-Depth Information
AND
[TitleId] LIKE
@Prefix
ORDER BY DP.PublisherName, [Title], [OrderDate]
The report parameters can now be used in the
WHERE
clause by replacing the hard-coded values with our
variables. Now, if we run our report query, we will see rows of data based on whatever dates and prefix we supply
to the query, as shown in Figure
13-13
.
Figure 13-13.
Results filtered with parameters
You can expect that some clients want the option of showing every row on some occasions while showing
only some of the rows at other times. There are a number ways to do this, but one way is to add a parameter that
serves as a flag to indicate whether you want to show all the results or just filtered results.
In Listing 13-18, you can see that we have added a parameter called
@ShowAll
. This parameter's job is to
toggle between showing all the results or only some of the results.
Listing 13-18.
Adding a Parameter Flag to Show All Data as Needed
DECLARE
@ShowAll nVarchar(4) = 'True'
, @StartDate datetime = '09/13/1994'
, @EndDate datetime = '09/14/1994'
, @Prefix nVarchar(3) = 'PS%'
SELECT
DP.PublisherName
, [Title] = DT.TitleName
, [TitleId] = DT.TitleId
, [OrderDate] = CONVERT(varchar(50), [Date], 101)
, SalesQuantity
