Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Nested Tables in a Repeating List Data Region

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.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.