locked
How to Sort in the Order of Search RRS feed

  • Question

  • Hello,

    Is it possible to sort the data using the order or the search provided?

    For example, I use multiple search (blueberry, apple, strawberry, pineapple) I would like the results to show in this order with their corresponding data:

    blueberry    3

    apple          1

    strawberry  4

    pineapple    7

    Thank you

    Friday, July 20, 2018 4:52 PM

Answers

  • Hi MsRR

    You could also use the report variable combine with the lookup function to achieve your goal:

    Here are my processing:

    Parameter : default setting

    Filter:

    Expression: =Cstr(Fields!Pid.Value)

    Operation: in

    Value : =split(Parameters!ReportParameter1.Value,",")

     

    Report structure:

    Report variable:

    (in the third table)

    Expression for pid :

    =Split(Parameters!ReportParameter1.Value.ToString(),",")(Variables!A.Value)

    Expression for project:

    =Lookup(CInt(ReportItems!Textbox22.Value),Fields!Pid.Value,Fields!project.Value,"DataSet1")

    Expression for variable:

    =Variables!A.SetValue(Variables!A.Value+1)

    Then set the each text box visability of the last column as :

    =Iif(ReportItems!Textbox42.Value= "True",True,False)

    Result:

    (the third table)

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.


    • Edited by Mitarai Queen Monday, July 23, 2018 5:35 AM
    • Proposed as answer by Mitarai Queen Tuesday, July 24, 2018 8:00 AM
    • Marked as answer by MsRR Thursday, July 26, 2018 3:34 AM
    Monday, July 23, 2018 5:34 AM

All replies

  • its possible

    for that you need to have a query like below

    SELECT t.*
    FROM yourtable t
    JOIN dbo.ParseValues(@SearchString,',') f
    ON f.Val = t.Column
    ORDER BY f.ID

    ParseValues is a UDF which you can find here

    https://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

    @SerachString is the comma separated parameter you pass from SSRS with multiple search values

    Column represents the column from your table where values are to be searched

    Make sure you create the UDF first before using it in select as above


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, July 20, 2018 4:58 PM
  • Hi Please see below answer, I had same problem but this solution resolved my problem.

    Hi Lhen123  

    According to your description, I think you want set the sort order and sort by according to parameters.

    You can set for sort by like blew:

       Sort by   =Iif( Parameters!sortorder.Value="asc",Fields(Parameters!sortby.Value).Value,"")     order A to Z

       Then by =Iif( Parameters!sortorder.Value="desc",Fields(Parameters!sortby.Value).Value,"")   order Z to A

    Then you will get like this

    Hope it can help you.

    Best Regards,

    Eric Liu

    Friday, July 20, 2018 5:31 PM
  • Search is this:

    ID  12345, 95678, 90123, 12458

    Desired Result:

    ID       Name      Age

    12345  fsuma     4

    95678  rchase     2

    90123  kevere     7

    12458  bksy        4

    The determining sort is the order of search

    Friday, July 20, 2018 8:42 PM
  • Search is this:

    ID  12345, 95678, 90123, 12458

    Desired Result:

    ID       Name      Age

    12345  fsuma     4

    95678  rchase     2

    90123  kevere     7

    12458  bksy        4

    The determining sort is the order of search

    After creating the UDF in the last link

    use like

    SELECT t.*
    FROM Tablename t
    INNER JOIN dbo.ParseValues(@ID,',')f
    ON f.Val = t.ID
    ORDER BY f.ID


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, July 20, 2018 10:12 PM
  • Hi MsRR

    You could also use the report variable combine with the lookup function to achieve your goal:

    Here are my processing:

    Parameter : default setting

    Filter:

    Expression: =Cstr(Fields!Pid.Value)

    Operation: in

    Value : =split(Parameters!ReportParameter1.Value,",")

     

    Report structure:

    Report variable:

    (in the third table)

    Expression for pid :

    =Split(Parameters!ReportParameter1.Value.ToString(),",")(Variables!A.Value)

    Expression for project:

    =Lookup(CInt(ReportItems!Textbox22.Value),Fields!Pid.Value,Fields!project.Value,"DataSet1")

    Expression for variable:

    =Variables!A.SetValue(Variables!A.Value+1)

    Then set the each text box visability of the last column as :

    =Iif(ReportItems!Textbox42.Value= "True",True,False)

    Result:

    (the third table)

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.


    • Edited by Mitarai Queen Monday, July 23, 2018 5:35 AM
    • Proposed as answer by Mitarai Queen Tuesday, July 24, 2018 8:00 AM
    • Marked as answer by MsRR Thursday, July 26, 2018 3:34 AM
    Monday, July 23, 2018 5:34 AM
  • Thank you for your input.

    MsRR

    Thursday, July 26, 2018 3:34 AM