Report designers often revert to overly complex report designs and often use subreports

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:

Weblog by Paul Turley and SQL Server BI Blog.