I'm creating an SSRS report which contains a table of orders, grouped by day. Now I can easily get the max order value for the day and put it in the group header by using the SSRS MAX() function.
However, I also want to get the corresponding customer name who placed this order, and place this in the group header too.
We can assume my result set simply contains date, name and order value. Is there any way to do this in SSRS 2008?
开发者_Go百科Thanks
Do it the lazy way and let Sql Server give you the results. For example, let's say your original query was something like this:
SELECT OrderDate, OrderId, CustomerName, OrderValue
FROM OrderTable
WHERE (OrderDate >= @DateFrom) AND (OrderDate <= @DateTo)
Join this with the grouped results for the day so that they appear on every row:
SELECT DT.OrderDate, DT.OrderId, DT.CustomerName, DT.OrderValue,
GT.OrderId AS MaxOrderId, GT.CustomerName AS MaxCustomerName, GT.OrderValue AS MaxOrderValue
FROM
(SELECT OrderDate, OrderId, CustomerName, OrderValue
FROM OrderTable
WHERE (OrderDate >= @DateFrom) AND (OrderDate <= @DateTo)) AS DT
INNER JOIN
(SELECT OrderDate, OrderId, CustomerName, OrderValue
FROM OrderTable AS OrderTable_1
WHERE (OrderDate >= @DateFrom) AND (OrderDate <= @DateTo) AND (OrderId =
(SELECT TOP 1 OrderId
FROM OrderTable AS OrderTable_2
WHERE (OrderDate = OrderTable_1.OrderDate)
GROUP BY OrderId
ORDER BY SUM(OrderValue) DESC))) AS GT ON DT.OrderDate = GT.OrderDate
ORDER BY DT.OrderDate, DT.OrderValue DESC
DT = Detail Table
GT = Group Results Table
The maximum value order for the day is now added to every row, letting you include it in group headers easily. Obviously, if the CustomerName comes from a different table, you just need to join that table in the original query and in the OrderTable_1 query.
This query assumes that the OrderDate field is a pure date field with no time component.
精彩评论