none
Sort Tablix Results Dynamically

    Question

  • Is there any way to pass a parameter to SQL from SSRS to dynamically assign the ORDER BY criteria?

    I tried a solution I found in an online search, but it seems to have problems when there are diverse data types. Basically, this routine has SSRS pass an ordinal to SQL which uses a CASE statement to determine the proper ORDER BY criteria. But, it didn't like the datetime column.

    Any ideas?

    Thanx in advance for any help!

    Monday, September 23, 2013 8:21 PM

Answers

  • Hi Adam,

    According to the description, I understand that you want to use parameters to control the sort order of data in the tablix.

    In this case, as a reference, I create the parameter named SortByParameter, set the parameter Available values to “SalesTerritoryGroup” and “SalesTerritoryCountry”, and specify “SalesTerritoryCountry” as the default value of the parameter. Please see the report design’s screenshot:

    Following this, I select the Group Properties on SalesTerritoryGroup and SalesTerritoryCountry, and remove the Sorting option in each group. Right click the handle of the tablix and select Tablix Properties. Then select Sorting in the left pane, and then add the expression below:

    Sort by: =Fields(Parameters!SortByParameter.Value).Value

    Finally, I can check the parameter value to sort the data in the tablix. Please see the result’s screenshot:

    If I have anything misunderstood, please feel free to correct me.

    Regards,
    Heidi Duan

    Monday, September 30, 2013 9:17 AM
    Moderator
  • Hi ,

    Include sort order in the dataset query to pre-sort data before it is retrieved for a report.
    To sort data in the report after it is retrieved from the data source, you can set sort expressions on a
    Tablix data region or a group, including the details group.

    To handle dynmaic sorting ,try with sorting expression given by Heidi Duan .

    Related reference links :

    http://blog.stevienova.com/2012/04/27/dynamic-sorting-using-parameters-in-ssrs/

    http://technet.microsoft.com/en-us/library/dd220417.aspx

    http://technet.microsoft.com/en-us/library/dd239331.aspx

    You can also try with ORDER BY CASE :

    DECLARE @Tmp TABLE (Id VARCHAR(5),Name VARCHAR(5))
    INSERT @Tmp SELECT 1,'C'
    INSERT @Tmp SELECT 2,'B'
    INSERT @Tmp SELECT 3,'A'
    DECLARE @Sortby VARCHAR(20) = 'Name'
    SELECT * FROM @Tmp
    ORDER BY CASE WHEN @Sortby = 'Name' THEN Name 
                  ELSE Id END 
    SET @Sortby = 'Id'
    SELECT * FROM @Tmp
    ORDER BY CASE WHEN @Sortby = 'Name' THEN Name 
                  ELSE Id END 


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Monday, September 30, 2013 4:56 PM
    Moderator

All replies

  • Hi Adam,

    According to the description, I understand that you want to use parameters to control the sort order of data in the tablix.

    In this case, as a reference, I create the parameter named SortByParameter, set the parameter Available values to “SalesTerritoryGroup” and “SalesTerritoryCountry”, and specify “SalesTerritoryCountry” as the default value of the parameter. Please see the report design’s screenshot:

    Following this, I select the Group Properties on SalesTerritoryGroup and SalesTerritoryCountry, and remove the Sorting option in each group. Right click the handle of the tablix and select Tablix Properties. Then select Sorting in the left pane, and then add the expression below:

    Sort by: =Fields(Parameters!SortByParameter.Value).Value

    Finally, I can check the parameter value to sort the data in the tablix. Please see the result’s screenshot:

    If I have anything misunderstood, please feel free to correct me.

    Regards,
    Heidi Duan

    Monday, September 30, 2013 9:17 AM
    Moderator
  • Hi ,

    Include sort order in the dataset query to pre-sort data before it is retrieved for a report.
    To sort data in the report after it is retrieved from the data source, you can set sort expressions on a
    Tablix data region or a group, including the details group.

    To handle dynmaic sorting ,try with sorting expression given by Heidi Duan .

    Related reference links :

    http://blog.stevienova.com/2012/04/27/dynamic-sorting-using-parameters-in-ssrs/

    http://technet.microsoft.com/en-us/library/dd220417.aspx

    http://technet.microsoft.com/en-us/library/dd239331.aspx

    You can also try with ORDER BY CASE :

    DECLARE @Tmp TABLE (Id VARCHAR(5),Name VARCHAR(5))
    INSERT @Tmp SELECT 1,'C'
    INSERT @Tmp SELECT 2,'B'
    INSERT @Tmp SELECT 3,'A'
    DECLARE @Sortby VARCHAR(20) = 'Name'
    SELECT * FROM @Tmp
    ORDER BY CASE WHEN @Sortby = 'Name' THEN Name 
                  ELSE Id END 
    SET @Sortby = 'Id'
    SELECT * FROM @Tmp
    ORDER BY CASE WHEN @Sortby = 'Name' THEN Name 
                  ELSE Id END 


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Monday, September 30, 2013 4:56 PM
    Moderator
  • If you want to dynamically sort based on a parameter, do the sorting on your table.

    On your tablix properties create a sorting expression with changes depending on what has been selected in the parameter.

    Tuesday, October 1, 2013 4:11 AM