JonWay
Reputation: 1735
SSRS custom sort one column ASC and DESC
use the attached data set to product SSRS report. I have been trying to sort the following columns in ascending and descending. Sales_Status column is sorted ascending while the Serial_Number number column needs to be sorted as follows. Serial_Number column has a parent group of Sales_Status column When the Sales_Status column is “Futures Sales” I want to sort Serial_Number ascending when is “Past Sales” it should be sorted descending. I have tried different sort order without luck. find attached expected result
Tsql query used
DECLARE @SalesTBL TABLE (Department VARCHAR(10), Sales_Status VARCHAR(50), Serial_Number INT);
INSERT INTO @SalesTBL VALUES
('A','Past Sales','3'),('D','Futures Sales','1'),('H','Futures Sales','1'),('L','Past Sales','3'),
('H','Past Sales','5'),('D','Past Sales','1'),('L','Past Sales','2'),('B','Futures Sales','6'),
('P','Past Sales','5'),('A','Past Sales','4'),('Q','Past Sales','1'),('D','Past Sales','6'),
('D','Past Sales','6'),('O','Past Sales','4'),('I','Past Sales','5'),('Q','Past Sales','4'),
('F','Past Sales','2'),('N','Past Sales','5'),('H','Past Sales','1'),('H','Past Sales','3'),
('P','Past Sales','3'),('H','Past Sales','4'),('B','Past Sales','4'),('L','Past Sales','5'),
('H','Past Sales','5'),('Q','Past Sales','5'),('H','Past Sales','4'),('D','Past Sales','2'),
('k','Past Sales','2'),('H','Past Sales','3'),('E','Past Sales','2'),('F','Past Sales','2'),
('D','Past Sales','3'),('R','Past Sales','4'),('D','Past Sales','3'),('L','Past Sales','2'),
('M','Futures Sales','6'),('I','Futures Sales','3'),('D','Past Sales','1'),('A','Futures Sales','1'),
('E','Futures Sales','3'),('A','Past Sales','5'),('L','Past Sales','6'),('F','Past Sales','3'),
('O','Past Sales','4'),('H','Past Sales','4'),('D','Past Sales','3'),('F','Past Sales','1'),
('A','Past Sales','6'),('U','Past Sales','6'),('H','Past Sales','2'),('F','Past Sales','4'),
('R','Past Sales','6'),('O','Past Sales','5'),('E','Past Sales','1'),('H','Past Sales','6'),
('B','Past Sales','4'),('H','Past Sales','6'),('H','Past Sales','5'),('N','Past Sales','1'),
('O','Past Sales','6'),('Q','Past Sales','2'),('A','Past Sales','5'),('I','Past Sales','4'),
('H','Past Sales','5'),('L','Futures Sales','6'),('k','Past Sales','1'),('H','Past Sales','4'),
('N','Futures Sales','4'),('k','Past Sales','4'),('O','Past Sales','2'),('F','Past Sales','4'),
('Q','Past Sales','4'),('A','Past Sales','1'),('L','Past Sales','4'),('A','Past Sales','3'),
('F','Past Sales','3'),('K','Past Sales','2'),('H','Past Sales','5'),('B','Past Sales','4'),
('O','Past Sales','1'),('O','Past Sales','1'),('H','Past Sales','6'),('F','Past Sales','4'),
('k','Past Sales','1'),('T','Past Sales','6'),('D','Past Sales','4'),('R','Past Sales','4'),
('B','Past Sales','6'),('H','Past Sales','4'),('A','Past Sales','2'),('B','Past Sales','2'),
('H','Past Sales','2'),('H','Past Sales','3'),('F','Past Sales','1'),('F','Futures Sales','3'),
('H','Past Sales','1'),('H','Futures Sales','6'),('H','Past Sales','5'),('N','Past Sales','2'),
('R','Futures Sales','3'),('H','Past Sales','2'),('E','Futures Sales','5'),
('F','Past Sales','5'),('D','Past Sales','4'),('H','Past Sales','6'),('H','Futures Sales','2'),
('B','Past Sales','6'),('K','Past Sales','1'),('N','Past Sales','2'),('D','Past Sales','2'),
('W','Past Sales','2'),('S','Futures Sales','2'),('H','Past Sales','1'),('E','Past Sales','6'),
('E','Past Sales','5'),('N','Past Sales','6'),('k','Past Sales','5'),('S','Past Sales','1'),
('O','Past Sales','4'),('Q','Past Sales','6'),('F','Past Sales','2'),('O','Past Sales','4'),
('B','Past Sales','5'),('K','Futures Sales','4'),('F','Past Sales','6'),('A','Past Sales','4'),
('N','Past Sales','5'),('B','Past Sales','1'),('S','Past Sales','3'),('H','Past Sales','6'),
('L','Past Sales','6'),('O','Past Sales','5'),('H','Past Sales','6'),('R','Past Sales','4'),
('k','Past Sales','5'),('H','Past Sales','3'),('F','Past Sales','1'),('A','Futures Sales','1'),
('F','Past Sales','4'),('B','Past Sales','5'),('k','Past Sales','4'),('D','Past Sales','5'),
('B','Past Sales','2'),('k','Past Sales','1'),('L','Past Sales','4'),('B','Past Sales','4'),
('I','Past Sales','5'),('H','Past Sales','1'),('K','Past Sales','3'),('E','Past Sales','4'),
('E','Past Sales','1'),('A','Past Sales','6'),('A','Past Sales','4'),('k','Past Sales','6'),
('A','Past Sales','3'),('A','Past Sales','2'),('H','Futures Sales','4'),
('W','Past Sales','3'),('Q','Past Sales','1'),('k','Past Sales','5'),('S','Past Sales','1'),
('Q','Past Sales','5'),('H','Past Sales','4'),('V','Past Sales','6'),('N','Past Sales','6'),
('H','Past Sales','2'),('k','Past Sales','1'),('H','Past Sales','3'),('K','Futures Sales','1'),
('H','Past Sales','1'),('H','Past Sales','3'),('K','Past Sales','1'),('D','Past Sales','5'),
('B','Past Sales','6'),('N','Past Sales','3'),('B','Past Sales','3'),('O','Past Sales','3'),
('D','Past Sales','5'),('A','Past Sales','5'),('F','Past Sales','4'),('A','Past Sales','4'),
('F','Past Sales','3'),('J','Past Sales','6'),('k','Past Sales','3'),('H','Past Sales','3'),
('E','Past Sales','4'),('A','Past Sales','1'),('H','Past Sales','2'),('F','Past Sales','1'),
('K','Past Sales','5'),('B','Past Sales','2'),('E','Past Sales','4'),('B','Past Sales','6'),
('L','Past Sales','4'),('T','Past Sales','3'),('H','Past Sales','6'),('k','Futures Sales','4'),
('H','Past Sales','1'),('D','Futures Sales','3'),('M','Past Sales','5'),('L','Past Sales','2'),
('O','Past Sales','2'),('B','Past Sales','1'),('k','Past Sales','1'),('F','Past Sales','2'),
('E','Past Sales','1'),('N','Past Sales','5'),('B','Past Sales','5'),('H','Past Sales','2'),
('F','Past Sales','6'),('k','Past Sales','2'),('M','Past Sales','1'),('K','Past Sales','1'),
('H','Past Sales','4'),('N','Past Sales','4'),('M','Past Sales','1'),('H','Past Sales','2'),
('D','Futures Sales','6'),('F','Past Sales','2'),('S','Past Sales','3'),
('T','Past Sales','1'),('N','Past Sales','3'),('D','Past Sales','3'),('O','Past Sales','2'),
('P','Past Sales','6'),('k','Past Sales','3'),('L','Past Sales','6'),('H','Past Sales','6'),
('F','Past Sales','3'),('A','Past Sales','6'),('k','Past Sales','4'),('Q','Past Sales','1'),
('Q','Futures Sales','4'),('Q','Past Sales','5'),('O','Past Sales','5'),
('T','Past Sales','2'),('L','Past Sales','5'),('B','Past Sales','6'),('L','Past Sales','4'),
('S','Past Sales','4'),('K','Futures Sales','2'),('D','Past Sales','6'),('O','Past Sales','3'),
('B','Past Sales','6'),('H','Past Sales','4'),('H','Past Sales','2'),('F','Past Sales','2'),
('A','Past Sales','4'),('B','Past Sales','6'),('F','Past Sales','4'),('K','Past Sales','1'),
('A','Past Sales','6'),('L','Past Sales','5'),('O','Past Sales','4'),('B','Futures Sales','5'),
('S','Past Sales','5'),('B','Futures Sales','2'),('k','Past Sales','5'),('A','Past Sales','6'),
('k','Past Sales','3'),('H','Past Sales','3'),('Q','Past Sales','1'),('H','Past Sales','3'),
('T','Past Sales','6'),('R','Past Sales','6'),('B','Past Sales','5'),('U','Past Sales','5'),
('K','Past Sales','2'),('B','Futures Sales','2'),('B','Past Sales','3'),('F','Past Sales','5'),
('L','Past Sales','5'),('k','Past Sales','3'),('O','Past Sales','6'),('H','Past Sales','2'),
('L','Past Sales','5'),('T','Past Sales','6'),('E','Past Sales','3'),
('L','Past Sales','2'),('B','Futures Sales','5'),('F','Past Sales','2'),('D','Past Sales','3'),
('N','Past Sales','3');
SELECT * FROM @SalesTBL
OutPut
Upvotes: 2
Views: 2379
Answers (1)
niktrs
Reputation: 10066
In the serial number group you will set two sorting expressions:
An ascending on
=Iif(Fields!Sales_Status.Value="Past Sales", Fields!Serial_Number.Value,"")
A descending on
=Iif(Fields!Sales_Status.Value="Future Sales", Fields!Serial_Number.Value,"")
Upvotes: 3
Related Questions
- SSRS - Sort table based on column value
- Sort Asc and desc in the same column
- SSRS (report builder) sorting a row
- SSRS [Sort Alphanumerically]: How to sort a specific column in a report to be [A-Z] & [ASC]
- SSRS Custom Sort Order
- Sorting Row In SSRS
- NVARCHAR sorting in SSRS
- SSRS Report Column Sorting
- How do I sort a column in an SSRS report alphabetically AND numerically?
- Fine control over SQL Sort Order