Users like to be in control. They want to define custom columns, sort orders, and basically drag, drop, and pivot chart their way to victory. i this specific case user want to order sales of a specific reporting services report based on different date. This is the specific query:
SELECT SalesOrderNumber, OrderDate, DueDate, ShipDate,
PurchaseOrderNumber, AccountNumber, SubTotal,
TaxAmt, Freight, TotalDue
FROM Sales.SalesOrderHeader
The user want to order data by: ORder date, Due date or shipdate.
To do this we use a stored procedure where we read the selected time attribute and give a query order by it.
The specific query it is :
DECLARE @SortOrder VARCHAR(50) = ‘[Time attribute selected]’;
SELECT rn,
[list of attributes]
FROM (
SELECT CASE @SortOrder
WHEN ‘OrderDate’ THEN ROW_NUMBER() OVER (ORDER BY OrderDate DESC)
WHEN ‘DueDate’ THEN ROW_NUMBER() OVER (ORDER BY DueDate DESC)
WHEN ‘ShipDate’ THEN ROW_NUMBER() OVER (ORDER BY ShipDate DESC)
END AS rn,
[list of attributes]
FROM Sales.SalesOrderHeader
) AS x
ORDER BY rn ASC;
With this symple query you can order dataset with dynamic variable.