Friday, 4 February 2011

SSRS: where should I do my grouping?

I'm no SSRS expert and one of the things I often struggle with is where to group my data in a report. Essentially, you have the option of doing the aggregations within your T-SQL query or returning a standard dataset and then letting the Report Server engine manage the groupings.

Here are a few screenshots to illustrate the different approaches which both yield the same results.

Using T-SQL to Group

1) Write your grouping query in the dataset dialog box:

2) Attach your dataset to a table and drop in the columns - no additional changes are necessary in the reporting layer:

Using SSRS to Group

1) Write a simple query returning the dataset of records you want to display:

2) Create a table and attach your dataset but edit the Details group to add your grouping:

3) Drop your columns into the table but notice how there is a function around the aggregate value (by default SUM which I have changed to COUNT)

I am much more comfortable writing T-SQL statements and have less of an understanding of SSRS grouping behaviours so this tends to lead me down the T-SQL route. However, if you have are running reports on a high volume OLTP database then it may make sense to remove as much load as possible from the database and onto the report server. My example is also quite limited in that it only caters for a single grouping. As you add aggregations to your queries you may want to make use of SSRS rending groups to avoid repetition of data. In this instance, it probably makes sense to do all the grouping in one place rather than have a hybrid solution of some T-SQL grouping and some SSRS grouping.

No comments:

Post a Comment

/* add this crazy stuff in so i can use syntax highlighter