Help creating report where groups are added based on totals

[jpack23]

I need to create a report based a sql table that list Item and quantities. the items are not grouped but have unique IDs. In reporting services I would like the report to start moving down the list of items adding the quantities together and when the total reaches 100, i need the report to start a new group. for example

Group 1
Item 4
Next Item 10
third Item 50
Fourth Item 30
fifth Item 6
Group 2
Sixth Item 20
Seventh Item 50
eight Item 20
ninth Item 10
Group 3
tenth Item
and so on

Basically what I am trying to do is create a report that tell the user what items go into a group when the maximum number of item the group can hold is 100. The database table doesnt separate the items into the group...so either I need to do it with reporting services or with an sql stored procedure that can create a view that gives the item a group number for every 100 items.

any help would be appreciated Thanks
joe

 

[xlbo]

You will need to do this in SQL rather than SSRS - suggest posting in the SQL Server Programming forum

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question