Database Reference
In-Depth Information
Now we have report code that uses two
SELECT
statements. The primary query gets the report data, but the
secondary
SELECT
statement acts as a subquery to the primary. The purpose of this subquery is to gather the
average of all the sales quantity values in the fact table and then add that result to the next
SELECT
statement's
results. If we were to use the
AVERAGE
function within the second
SELECT
statement, we would get only the
average of the filtered values, not all of the quantity values in the fact sales table. Figure
13-17
shows the results.
Figure 13-17.
Results with parameters filled by a subquery
Creating KPI Queries
As reporting software has become more available, it is common for users to have to review many reports during
their day-to-day work. This often results in information overload. One way to help alleviate this issue is to create
some kind of indicator in your report that can be used to provide a quick determination of the value's meaning.
One way of accomplishing this is to provide key performance indicator (KPI) values within your result set.
he
CASE
operator is a common method of including KPI's in a SQL statement. Listing 13-21 shows the use of this
operator.
Listing 13-21.
Adding KPIs
DECLARE
@ShowAll nVarchar(4) = 'True'
, @StartDate datetime = '09/13/1994'
, @EndDate datetime = '09/14/1994'
, @Prefix nVarchar(3) = 'PS%'
, @AverageQty int
SELECT @AverageQty = Avg(SalesQuantity) FROM DWPubsSales.dbo.FactSales
SELECT
DP.PublisherName
, [Title] = DT.TitleName
, [TitleId] = DT.TitleId
, [OrderDate] = CONVERT(varchar(50), [Date], 101)
