SSRS Report Custom Sort

Question

I need to implement custom sort on SSRS report on a Payment-Range field obtained from one of the dateset

Payment-Range is appearing like this:

$0 - $200       
$200.01 - $1000     
$1,000.01 - $10,000     
$10,000.01 - $20,000        
$20,000.01 - $30,000        
$30,000.01 - $40,000        
$40,000.01 - $50,000        
$50,000.01 - $60,000

I have used if else in order to implement

  =IIF(Fields!netPaymentRange.Value= "$0 - $200", "A",
    IIF(Fields!netPaymentRange.Value= "$200.01 - $1000", "B",
    IIF(Fields!netPaymentRange.Value= "$1,000.01 - $10,000", "C", 
    IIF(Fields!netPaymentRange.Value= "$20,000.01 - $30,000", "D",
    IIF(Fields!netPaymentRange.Value= "$30,000.01 - $40,000", "E",
    IIF(Fields!netPaymentRange.Value= "$40,000.01 - $50,000", "F",
    IIF(Fields!netPaymentRange.Value= "$50,000.01 - $60,000", "G","")))))))

but it is not working for me. Please suggest

What is the query source for the dataset? SQL Server, Oracle, XML, etc. — aduguid, Sep 02 '18 at 11:54
It seems like you should be storing the `Payment-Range` in a lookup table were you can apply a sort column. — aduguid, Sep 02 '18 at 11:57
Not working? Please give more details than "not working" as it means nothing. — Nick.Mc, Sep 02 '18 at 13:36
The Query Source is SQL server, Not Working means it is giving results as follows:- $0 - $200 $1,000.01 - $10,000 $10,000.01 - $20,000 $200.01 - $1000 $20,000.01 - $30,000 $30,000.01 - $40,000 $40,000.01 - $50,000 $50,000.01 - $60,000 — nishant, Sep 02 '18 at 14:57
If you assign the above statement to a column as an expression, do you get the correct / desired result? I would consider using a switch statement for something like this.. better still.. return a suitable row in your data source to do the sorting on — Harry, Sep 02 '18 at 20:54

aduguid · Accepted Answer · 2018-09-03T00:56:23.670

I would create a CTE with a select from values query to create the sort order for your list of payment ranges. Then you can join to the source table/view for the report dataset. I would still suggest storing the payment_range as a table.

Example SQL

WITH
payment_range
AS
(
    SELECT tbl.* FROM (VALUES
      ( '$0 - $200', 1)
    , ( '$200.01 - $1000', 2)
    , ( '$1,000.01 - $10,000', 3)
    , ( '$20,000.01 - $30,000', 4)
    , ( '$30,000.01 - $40,000', 5)
    , ( '$40,000.01 - $50,000', 6)
    , ( '$50,000.01 - $60,000', 7)
    ) tbl ([netPaymentRange], [netPaymentRangeSortOrder]) 
)
SELECT 
    * 
FROM 
    payment_range --join to your source table here
ORDER BY 
    [netPaymentRangeSortOrder]

Results

No worries mate. If my answer sorted you out, can you please click on the check mark beside the answer to toggle it from greyed out to filled in? — aduguid, Sep 03 '18 at 10:48

SSRS Report Custom Sort

1 Answers1

Example SQL

Results