Report designers often revert to overly complex report designs and often use sub reports unnecessarily. Building a report on a single dataset query to return all related rows gives you greater flexibility to group and filter the data.
The following sample report contains a single dataset using the following query:
select 1 as StoreID, 'Freds Store' as StoreName
, convert(date, '2009-01-15') as TransDate, 'Chicken' as Product
, 14.99 as Price
union
select 1 as StoreID, 'Freds Store' as StoreName
, convert(date, '2009-01-25') as TransDate, 'Beef' as Product
, 19.99 as Price
union
select 1 as StoreID, 'Freds Store' as StoreName
, convert(date, '2009-02-15') as TransDate, 'Chicken' as Product
, 15.99 as Price
union
select 2 as StoreID, 'Bobs Store' as StoreName
, convert(date, '2009-01-15') as TransDate, 'Tuna' as Product
, 17.99 as Price
union
select 2 as StoreID, 'Bobs Store' as StoreName
, convert(date, '2009-02-15') as TransDate, 'Chicken' as Product
, 15.99 as Price
A list data region is grouped on StoreID
Table1 (in the list) has columns bound to TransDate, Product & Price
Filter for this table is: TransDate Between ‘2009-01-01’ And ‘2009-01-31’
Table2 (in the list) has columns bound to TransDate, Product & Price
Filter for this table is: TransDate Between ‘2009-02-01’ And ‘2009-02-28’
Select a table and choose Filters in the Properties Window to open the Tablix Properties dialog (in SSRS 2008.) This shows the filter for the first table:
*In this simple example, the date values are hard-coded but could be parameterized and/or based in expressions, such as using the DateAdd() function to compare the current month sale to the prior month, etc.
The report produces this output: