none
SSRS 2008 r2 sort not working

    Question

  • In a SSRS 2008 r2 report, I have the following sql that sorts correctly in SSIS manager.

            SELECT
                SA.CustNumber AS CNumber
      ,  DS.firstName  AS FirstName
      ,  DS.lastName  AS LastName
      ,  admindate  AS AdminDT
      
             FROM
            table1
       
                  
       ORDER BY
       DS.lastName
      , DS.firstName
      , SA.studentNumber
      , admindate DESC

    However when the report is display in SSRS 2008 report, the data is not sorted by DS.lastName first.
    Each detail line header has a sort on the field header.

    Can you tell me what you think can cause the problem of why the data is not sorted by last name
    when the report is rendered?

    Thursday, October 17, 2013 7:32 PM

Answers

  • I am basically going to allow the users to click on the header of 9 of the columns for the detail row. I will  allow the user to select which column they want to sort on. In this case will I need to put a parent group on the column headings?

    Hi,

    Try this thread for an idea about creating dynamic sorting .


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

    Friday, October 18, 2013 5:25 PM

All replies

  • Right-Click on a cell in the detail row and go to Row Group aand then Group Properties.

    Find the listing on the left side for Sorting (Third from top) and click on it.

    This will show you how the detail rows are being sorted. You can add, remove, or change the order of itesm to be used for sorting and select ascending (A-z) or descending order. If needed, you can sort based on an expression.

    The key is knowing what is being sorted. In this case, it's the details row. If you have several groups, you might need to determine which group is best for sorting.

    HTH.

    Thursday, October 17, 2013 8:27 PM
  • What you suggested worked! I removed the sort that was on the detail row. The only default sort is from the sql that is executed by the ssrs report. I also want to put a sort on the detail headers for each column. Is there any sort problem that I should ber made aware of to look for?
    Thursday, October 17, 2013 9:50 PM
  • Only a word of caution.

    The detail headers sort with the detail rows. Since they are in the same group, if you try to add a different sort of the detail header, it will change the sort of the detail rows themselves.

    If the headers need to be sorted differently, consider putting them in a parent group and sorting on that level.

    HTH.

    Thursday, October 17, 2013 10:28 PM
  • I am basically going to allow the users to click on the header of 9 of the columns for the detail row. I will  allow the user to select which column they want to sort on. In this case will I need to put a parent group on the column headings?
    Friday, October 18, 2013 1:13 PM
  • I am basically going to allow the users to click on the header of 9 of the columns for the detail row. I will  allow the user to select which column they want to sort on. In this case will I need to put a parent group on the column headings?

    Hi,

    Try this thread for an idea about creating dynamic sorting .


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

    Friday, October 18, 2013 5:25 PM