none
How to sort items on each page? RRS feed

  • Question

  • I have a simple report which is basically a table spanning over a few pages.

    I have an unusual - but firm - requirement from the client. They must have the items sorted in descending order overall, but then in ascending order on each page.

    I don't even know how to get started on this. I can't order the items myself ahead of time because I don't know how many rows will fit on to each page, which depends on orientation, printer, margins, etc., etc.

    Does anyone have any advice?
    Thanks

    Wednesday, August 27, 2008 12:42 PM

Answers

  • Hello gman,

    i think, it's an impossible mission........



    But maybe i've got a workaround for you with fix rows.

    Here is my solution:

    -------------------------------------------------------------------------------------------------------------

    I made a simple table(SORTTEST) with one column(item). I put the datas in : A,B,C,...,Z. So i have 26 Rows.

    Then i make my select:

    select * from(

    SELECT *,(row_number() over (order by item desc)-1 ) / 10 as ROWNUM

    FROM SORTTEST

    ) as t2

    order by t2.rownum ,t2.item 

    -------------------------------------------------------------------------------------------------------------

    And here is the Result:

    Item

    RowNum

    Q

    0

    R

    0

    S

    0

    T

    0

    U

    0

    V

    0

    W

    0

    X

    0

    Y

    0

    Z

    0

    G

    1

    H

    1

    I

    1

    J

    1

    K

    1

    L

    1

    M

    1

    N

    1

    O

    1

    P

    1

    A

    2

    B

    2

    C

    2

    D

    2

    E

    2

    F

    2



    Reportserver now can create a new Page if you make a Group on RowNum.


    HTH, BanditTreiber


    Germany, NRW
    • Marked as answer by gman Friday, August 29, 2008 2:02 AM
    Thursday, August 28, 2008 7:32 AM

All replies

  • Hello gman,

    i don't really know what you want. But you can order the datas by SQL. You know?


    select * from table order by ColumnName1 desc, ColumnName2 desc, ColumnName3 asc


    Asc ist default, you don't have to write it.




    HTH, BanditTreiber
    Germany, NRW
    Wednesday, August 27, 2008 1:28 PM
  • Thanks for your reply, but I already know how to sort ALL the items in the report. That is not my question...

    As I stated: I want to sort ALL items in DESCENDING order, THEN on each page sort the items in ASCENDING order.  I don't know how else to explain it.

    Would you like an example? Let's say I have 26 items A, B, C, ... Z.

    First sort them in descending order: Z, Y, X, ... A. OK, fine. Now let's say my report is displaying 10 items per page.  On each page I need to sort the items ascending. So I want this:

    Page 1 items: Q, R, S, T, U, V, W, X, Y, Z
    Page 2 items: G, H, I, J, K, L, M, N, O, P
    Page 3 items : A, B, C, D, E, F

    How can I do this? Keeping in mind that the number of rows per page is totally dependent on printer, margins, orientation, etc., etc., set by the user at runtime.

    Wednesday, August 27, 2008 1:55 PM
  • Ah, ok. I'm relieved to hear that can sort by sql. :-)


    Your Sample is now good enough, i think. I will look tomorrow, or an other one give you an Answer.




    BanditTreiber.
    Germany, NRW
    Wednesday, August 27, 2008 2:08 PM
  • Thanks for the reply and any help you can provide! :)
    Wednesday, August 27, 2008 6:18 PM
  • Hello gman,

    i think, it's an impossible mission........



    But maybe i've got a workaround for you with fix rows.

    Here is my solution:

    -------------------------------------------------------------------------------------------------------------

    I made a simple table(SORTTEST) with one column(item). I put the datas in : A,B,C,...,Z. So i have 26 Rows.

    Then i make my select:

    select * from(

    SELECT *,(row_number() over (order by item desc)-1 ) / 10 as ROWNUM

    FROM SORTTEST

    ) as t2

    order by t2.rownum ,t2.item 

    -------------------------------------------------------------------------------------------------------------

    And here is the Result:

    Item

    RowNum

    Q

    0

    R

    0

    S

    0

    T

    0

    U

    0

    V

    0

    W

    0

    X

    0

    Y

    0

    Z

    0

    G

    1

    H

    1

    I

    1

    J

    1

    K

    1

    L

    1

    M

    1

    N

    1

    O

    1

    P

    1

    A

    2

    B

    2

    C

    2

    D

    2

    E

    2

    F

    2



    Reportserver now can create a new Page if you make a Group on RowNum.


    HTH, BanditTreiber


    Germany, NRW
    • Marked as answer by gman Friday, August 29, 2008 2:02 AM
    Thursday, August 28, 2008 7:32 AM
  • Thanks!

    I appreciate your looking into this.

    I have also received some suggestions in another forum: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3799135&SiteID=1

    Thanks again!
    Friday, August 29, 2008 2:03 AM