SSRS 2008 Need to lookup customer name with largest order

Question

Hi,

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?

Thanks

Answer 1

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.