Post reply

Group by row number

  • April 6, 2009 at 8:18 pm

    #198974

    I have a report that uses a simple query that needs to be grouped into every 5 records. I was thinking about using where the row number is a multiple of 5, e.g - records 1-5, then 6-10, etc. I intend for the groups to be separated by the group footer.

    Can someone please advise on an expression to use on the table group that would allow me to do this? Or even if there's a better way.

    Thanks

    Angela

  • April 7, 2009 at 8:28 am

    #973416

    I think this expression will do what you want, but, as always, there may be a better way, such as returning a grouping column in the dataset.

    =IIF(RowNumber(Nothing) Mod 5 = 0, Floor(RowNumber(Nothing)/5) - 1, Floor(RowNumber(Nothing)/5))

    Here's how it works the Floor function will return the lowest integer value so Floor(1/5) is 0. You need the IIF because when you hit a multiple of 5 you get an integer, so when you hit a multiple of 5 subtract 1 from the floor value to make it match the previous 4 values. Here's a table that shows what I mean.

    RowNumber Floor DesiredGroup

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

    1 0 0

    2 0 0

    3 0 0

    4 0 0

    5 1 0

    6 1 1

    7 1 1

    8 1 1

    9 1 1

    10 2 1

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply