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.