Report Solution Patterns and Recipes - Professional SQL Server 2005 Reporting Services - page 286

-- Top 10 and Others:
SELECT Top10.ProductKey, Top10.ProductName
, Top10.AmountSum
FROM (
-- Top 10:
SELECT TOP 10
DimProduct.ProductKey
, DimProduct.EnglishProductName AS ProductName
, SUM(FactResellerSales.ExtendedAmount) AS AmountSum
FROM DimProduct INNER JOIN FactResellerSales
ON DimProduct.ProductKey = FactResellerSales.ProductKey
INNER JOIN DimTime
ON FactResellerSales.OrderDateKey = DimTime.TimeKey
GROUP BY DimProduct.ProductKey, DimProduct.EnglishProductName
ORDER BY SUM(FactResellerSales.ExtendedAmount) DESC
) AS Top10
UNION
SELECT Other.ProductKey, Other.ProductName, Other.AmountSum
FROM (
-- Others excluding the top 10:
SELECT TOP 100 PERCENT
-1 AS ProductKey, 'Other' AS ProductName
, SUM(FactResellerSales.ExtendedAmount) AS AmountSum
FROM DimProduct INNER JOIN FactResellerSales
ON DimProduct.ProductKey = FactResellerSales.ProductKey
INNER JOIN DimTime
ON FactResellerSales.OrderDateKey = DimTime.TimeKey
WHERE DimProduct.ProductKey NOT IN
(
SELECT TOP 10 DimProduct.ProductKey
FROM DimProduct INNER JOIN FactResellerSales
ON DimProduct.ProductKey =
FactResellerSales.ProductKey
INNER JOIN DimTime ON
FactResellerSales.OrderDateKey = DimTime.TimeKey
GROUP BY DimProduct.ProductKey
ORDER BY SUM(FactResellerSales.ExtendedAmount) DESC
)
ORDER BY SUM(FactResellerSales.ExtendedAmount) DESC
) AS Other
You should be mindful that this query must reselect data in the same tables multiple times and may
not perform well with large tables. This solution can also be achieved using an upgraded version of the
chart item available from Dundas Software. Dundas Chart for Reporting Services includes a similar fea-
ture that doesn't require complex query expressions.
Dynamic Images: Scales and Gauges
Images can be made to display different content under different conditions. Using expressions, the image
report item may be used with a series of images to show progress gauges or indicators.