MS Report Designer - What are table groups and how are they used?

Question

Hi. I am working on a project with some existing MS Reports using Reporting Services 2000/2005 and Visual Studio 2005 with the built in Report Designer.

I have come accross a report that makes use of a table to display data which is fetched by a Stored Proc. My work required me to modify the report and the stored Proc.

I've made the changes to the stored proc, and it returns the correct data when tested in SQL Management Studio. However, in the report, some of the data is missing for reason, even though I am using the exact same parameters.

I have a sneaky feeling that it has to do with Table Groups defined for the main table of the report. The table has 2 table groups defined. I deleted some of the items in the 2nd Table Group, as they were removed from the stored proc and report. Perhaps I need to add the new fields/columns from the stored proc to the table group?

Can anyone point me in the right direction on how to investigate/solve this problem, and also what table groups are, and how they are meant to be used.

Answer 1

Groups in tables work in a similar way to groups in a SQL query - data in them is only returned once per group, instead of once per record. However, because you can also include details rows in a table, you can also see both group level and detail level data from a single report table, whereas this would require separate (sub)queries in SQL.

As an example, consider a sales structure in SQL:

sales_month
sales_day
sales_product
sales_value

You could return a list of all sales in date order in SQL with the query

select * from sales order by sales_month, sales_day;

Or you could return a summary of all sales by month in SQL with the query

select sales_month, sum(sales_value) as monthly_sales 
from sales group by sales_month order by 1;

By using a report table grouped on month, with detail rows and a group footer, you could list all sales in date order with a subtotal for each month, at the end of each month.

There are tutorials within the SSRS help that illustrate the use of grouping within a report.

Answer 2

Ok, so I added the new columns in the stored proc to the table group, and it is working as expected.

I think by ommitting a few columns in the table group, the report was grouping on fewer fields, meaning that these rows were all seen to be the same value.

As I added one new field at a time to match the fields in the stored proc, the better the accuracy of the report became.

I think it is similar to how SQL requires that every field in the Select clause also appear in the Group By Clause.