I want to create a SSRS report. I have 5 tables that I want all of them to stay in one page, except when year changes, like below:
page1: year 2014
table1
table2
table3
table4
table4
page2: year 2015 (the same tables as page 1, but with data for year 2)
table1
table2
table3
table4
table5
How can I do this?
A (rather ugly) way to do this is to use a list filtered on Year. Unfortunately this does mean that you need to return ALL your data in a single dataset.
What I have done previously, is create a table variable to return all the data in such as @ReturnData
. This has every field from every table you wish to return, as well as a tableID
identifier.
I then insert all the tables into this @ReturnData table with the TableID set for the current insert statement, such as
INSERT INTO @ReturnData(tableID, col01, col02, col03)
SELECT
1 AS tableID,
a.col1 as col01,
a.col2 as col02,
a.col3 as col03,
...
FROM myTableA a
Then for the second table
INSERT INTO @ReturnData(tableID, col11, col12, col13)
SELECT
2 AS tableID,
b.col1 as col11,
b.col2 as col12,
b.col3 as col13,
FROM myTableB b
Then in Reporting Services you can create a list based on the grouped together returned dataset. This list can be grouped on year, and each table can be filtered on the tableID
column.
Note if there are common columns between the tables there is no reason, not to put data in a single column for both tables, as each row will have its own tableID identifier.