To Create SQL Report, Should i Include Aggregate Functions in SQL Query or Do it via Reporting Tools?

Question

While learning about creating MS SQL Reporting and deploying reports to SQL Reporting Server, i found that there is no need to worry about creating a query that involves sorting, grouping and so on. All i need to do is fetch the columns and data i need from various tables and then once the data loads in dataset, i can use the GUI tools to do the groupings, aggregate functions, sorting, etc which will rid of duplicate e.g. categories name. I find it much easier than typing my own complex queries to write the report i need. Thus, from now i will just focus on writing a query that fetches data without worrying how the output should look like. Once the data is loaded in sql reports, i will use the tools available to format the data in the way i want.

I won't focus much on coding grouping, aggregate functions, etc manually anymore (only when necessarily needed).

Is this the right and practical way to create reports?

Please confirm ...

Thanks

Answer 1

I would lean on the reporting tool as much as possible as long as it doesn't create a performance issue which in most cases it shouldn't.

By leaning on the reporting tool you have more flexibility to work with the underlying data set the way you want to.

Answer 2

One of the things I regret about reporting services is the lack of automated unit testing (though now that I've written that, I see that there is some attempt at this: http://rsunit.codeplex.com/). One thing I can unit test is the query. So I tend to do all my work in the query (which is always in a stored procedure) and have a "dumb" report to the extent possible.