Yeah, their advert--explicit control over which rollups--is pretty attractive and I'll try them, or probably more like it, I'll trial-and-error them until I can figure them out. (Betty hurry up and find all the new 2008 changes, Carl; in about 10 minutes or so we'll have to learn all the new tricks to be found in 2012.) Thank you again for your time and help.HomeCookN

I’ve got a data set which is just a straight forward Select * From tblTable SQL statement. The table has about 500k records, each with only about 10 small, very standard text and date fields. However I need to rollup these records up into three levels of groups, each showing SUMs of their children rows, plus a detail row, and for the first time I’m having some performance issues with SSRS 2008 R2—and they are not hardware-based. The only thing I’ve thought of is to work on the record source SQL in order to go ahead and calculate some of the rolled up group totals, I would like to ask if there are any other standard tricks to help SSRS handle tough data sets? Should I be using subreports or is there another way to prepare the data so that it will be easier for SSRS to handle? HomeCookN

Two solutions come to mind: Use OLAP - roll-ups are what OLAP is all about. Use T-SQL to calculate the rollups for you Sub-reports are rarely (if ever) a path to better performance. If you need both the detail rows and the roll-ups in a single table (a table with 500K rows sounds a bit dodgy), you can write the SQL query as a UNION (or series of UNIONS) between the detail rows and similarly shaped roll-up rows that include NULL values in one or more key columns (the dimensions over which the data has been rooled up). You can then use sorting and row filtering in your SSRS table to arrange for the roll-up rows to appear in the right places without doing any of the aggregation in the report itself. You might also be able to make use of the "with cube" or "with rollup" clauses in a SQL GROUP BY, depending on exactly what you need and exactly what you've got to work with. For example: select it.ItemID, it.DimensionOneID, it.DimensionTwoID, SUM(it.Amount), SUM(1) as [Count] from Item it where { conditions } group by it.ItemID, it.DimensionOneID, it.DimensionTwoID with cube order by it.ItemID, it.DimensionOneID, it.DimensionTwoID -cd Mark the best replies as answers!

Thanks a lot, Carl, for your answer. I've never used a subreport so I'm glad I don't have to begin now. As far as a T-SQL solution goes, I was thinking that GROUPING SETS might be the right Group By clause to use in this case, but your WITH CUBE looks very interesting as well. I appreciate your help.HomeCookN

I'd forgotten about those (too new!) - GROUPING SETS might be just the thing you want - they're really just a generalization of "with cube" that gives you explicit control over which rollups you get. I haven't played with those at all, but I'd definitely try 'em out if I had the requirements you called out. -cd Mark the best replies as answers!