I am creating an SSRS report that has a datasource with the following fields (and sample data):
StartDate | EndDate | GroupId | Amount1 | Amount2
---------------------------------------------------|
01-Jan-16 | 06-Jan-16 | G1 | 11 | 23
01-Jan-16 | 06-Jan-16 | G2 | 28 | 47
01-Jan-16 | 06-Jan-16 | G3 | 14 | 8
07-Jan-16 | 20-Jan-16 | G1 | 7 | 15
07-Jan-16 | 20-Jan-16 | G3 | 34 | 16
21-Jan-16 | 31-Jan-16 | G2 | 3 | 11
21-Jan-16 | 31-Jan-16 | G3 | 9 | 23
21-Jan-16 | 31-Jan-16 | G4 | 16 | 2
I need to group this data by period (represented by the StartDate and EndDate fields), display the dates on top of the page and list the rest of the fields (GroupId, Amount1 and Amount2) in a table/matrix. Something like that:
StartDate: 01-Jan-16
EndDate: 06-Jan-16
GroupId | Amount1 | Amount2
---------------------------|
G1 | 11 | 23
G2 | 28 | 47
G3 | 14 | 8
-- new page --
StartDate: 07-Jan-16
EndDate: 20-Jan-16
GroupId | Amount1 | Amount2
---------------------------|
G1 | 7 | 15
G3 | 34 | 16
-- new page --
StartDate: 21-Jan-16
EndDate: 31-Jan-16
GroupId | Amount1 | Amount2
---------------------------|
G2 | 3 | 11
G3 | 9 | 23
G4 | 16 | 2
I have tried using Tablix, but the closest representation I could come up with was
StartDate | EndDate | GroupId | Amount1 | Amount2
---------------------------------------------------|
01-Jan-16 | 06-Jan-16 | G1 | 11 | 23
| | G2 | 28 | 47
| | G3 | 14 | 8
-- new page --
StartDate | EndDate | GroupId | Amount1 | Amount2
---------------------------------------------------|
07-Jan-16 | 20-Jan-16 | G1 | 7 | 15
| | G3 | 34 | 16
-- new page --
StartDate | EndDate | GroupId | Amount1 | Amount2
---------------------------------------------------|
21-Jan-16 | 31-Jan-16 | G2 | 3 | 11
| | G3 | 9 | 23
| | G4 | 16 | 2
That is, I could not move the dates outside of the rows to the header.
Any idea how I can achieve the required result?
Try this: When adding the parent group, check the check box says "Add group header"