Database Reference
In-Depth Information
Figure
13-12
shows the results of this query.
Figure 13-12.
Results filtered by multiple operators
Adding Dynamic Filters with Parameters
In many cases, report builders prefer to set the wildcard or date values themselves. You can allow this by defining
a set of parameters to further customize the report results. This can be programmed by creating SQL variables to
use for report parameters. Listing 13-17 shows an example.
Listing 13-17.
Adding Variables to Your Query
DECLARE
@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
FROM DWPubsSales.dbo.FactSales AS FS
INNER JOIN DWPubsSales.dbo.DimTitles AS DT
ON FS.TitleKey = DT.TitleKey
INNER JOIN DWPubsSales.dbo.DimDates AS DD
ON FS.OrderDateKey = DD.DateKey
INNER JOIN DWPubsSales.dbo.DimPublishers AS DP
ON DT.PublisherKey = DP.PublisherKey
WHERE
[Date] BETWEEN
@StartDate
AND
@EndDate
