http://www.sqlservercentral.com/blogs/pturley/2010/03/11/nested-tables-in-a-repeating-list-data-region/

Printed 2014/07/28 07:04AM

Nested Tables in a Repeating List Data Region

By pturley, 2010/03/11

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.
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.