Stepped table not sorting

Question

I'm trying to create a stepped table report using SQL report builder 3.0. The stepped report contains Groups/devices/users along with associated totals for each group/device/user.

I want the entire report to be sorted by these totals along with each individual step sorted this way also.

Currently users are sorted by their totals, but not devices or groups.

Is there a way to sort the other steps?

Answer 1

You can just do this in SQL using some nested queries. Let's assume you have the following tables: Transaction, User, Device, and Group. The transaction table records the transactions of the User on a Device and has an Amount field to sum. A user belongs to a Group.

So you need to sum the Amount for the User, for the Groups and for the Devices used within a Group which will give you SQL that looks like this:

SELECT G.Description AS [Group], D.Description AS Device, U.Description AS UserName, MAX(GT.GroupTotal) AS GroupTotal, MAX(GDT.GroupDeviceTotal) AS GroupDeviceTotal, SUM(T.Amount) AS UserTotal
FROM Transaction AS T
    INNER JOIN User AS U ON L.UserId = F.UserId 
    INNER JOIN Group AS G ON G.GroupId = L.GroupId 
    INNER JOIN Device AS D ON T.DeviceId = L.DeviceId 
    INNER JOIN
        (SELECT GroupId, SUM(Amount) AS GroupTotal
        FROM Transaction 
            INNER JOIN User ON User.UserId = Transaction.UserId
        WHERE (Transaction.TxDate >= '2011-01-01')
        GROUP BY User.GroupId) AS GT ON GT.GroupId = U.GroupId 
    INNER JOIN
        (SELECT GroupId, DeviceId, SUM(Amount) AS GroupDeviceTotal
        FROM Transaction  
            INNER JOIN User ON User.UserId = Transaction.UserId
        WHERE (TxDate >= '2011-01-01')
GROUP BY GroupId, DeviceId) AS GDT ON GDT.GroupId = U.GroupId AND GDT.DeviceId = T.DeviceId
WHERE (T.TxDate >= '2011-01-01')
GROUP BY G.GroupId, D.DeviceId, U.UserId
ORDER BY GroupTotal DESC, GroupDeviceTotal DESC, UserTotal DESC

Note that the where clause you use has to be the same in the main query and each nested query (this is the "WHERE (T.TxDate >= '2011-01-01')" bit).

Answer 2

You can try going to the Row/Column groups area... then for each group you have, double click the group, select "Sorting" and then add as many sorting fields as you need for the info contained at that group level.

If you have other sorts applied on the data... such as to the tablix/matrix, sometimes SSRS can get confused, so If my suggestion does help you with the effect you're going for but there are some issues, try removing all other sorting you've applied to the data elsewhere in the report besides on those groups... And I would start with the innermost and work out, trying not to repeat a field that is in a lower group's data. (if that makes sense).

---

edit:

So, let's say we have a report for a vet's office that shows client information, and we want to group by personID, petID and visitID. The tablix as a whole would be sorted by the person's name (or last name, then first name... or whatever). Then your first group would group on the personID and be sorted by the petName. The second, lower group would group on the petID and be sorted by the visitDate. The third level would group on the visitID, and... this doesn't really need to be sorted unless by visitTime if its not included in visitDate.