Hi this example used vs 2019 on 2017 developer. I'm hoping whatever the answer is will be backward compatible to vs 2017 over 2014 enterprise.
I have included a table create, inserts and what i think you want to know about my report. The table represents information about various camps. Every year a camp starts a new program with multiple sessions. In these examples, the sessions run in the same year but a program can carry over to the next year. For session 3 only we have to stagger the leave dates to deal with local traffic issues. so there can be multiple leave counts and leave date pairs for the 3rd session in any program. it doesn't make sense to repeat the column values thru session 3 arrive more than once. and if there are multiple leave dates in a 3rd session for a program, i'd like to list each leave date/leave count pair and then total them before the next program shows. at the moment i have no requirement for any such totals at higher levels. we have to assume data for any session can be missing. before i started this post i tried adding a group in the row group item "details" but couldn't even get camp name etc to stop repeating. so i removed them till i have a better strategy. If i have any issues uploading images/rs dataset etc, i'll post them in subsequent answers.
USE [research]
--drop table campresearch
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[campresearch](
[Id] [int] IDENTITY(1,1) NOT NULL,
[campprogramkey] smallint,
[campprogramgid] uniqueidentifier NULL,
[campnumber] varchar(5) NULL,
[campname] varchar(100) NOT NULL,
[camptypeflag] char(1),
opendate datetime,
closedate datetime,
startyear smallint,
endyear smallint,
isactive char(1),
totalsession1candidates int,
totalsession2candidates int,
session1arrivedate date,
session2arrivedate date,
session3arrivedate date,
session1leavedate date,
session2leavedate date,
session3leavedate date,
session3leavecount int,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert campresearch
select 417,newid(),'150','Rolling Hills','N','2013-04-01','2013-09-30',2013,2013,'N',0,0,'2013-04-01','2013-06-01','2013-08-01','2013-05-31','2013-07-31' ,'2013-09-28',100
union all
select 550,newid(),'150','Rolling Hills','N','2014-04-01','2014-09-30',2014,2014,'N',2732,1278,'2014-04-01','2014-06-01','2014-08-01','2014-05-31','2014-07-31' ,'2014-09-28',2000
union all
select 835,newid(),'150','Rolling Hills','N','2015-04-01','2015-09-30', 2015,2015,'N',1751,952,'2015-04-01','2015-06-01','2015-08-01','2015-05-31','2015-07-31' ,'2015-09-28',3000
union all
select 999,newid(),'150','Rolling Hills','N','2016-04-01','2016-09-30',2016,2016,'N',2098,1203,'2016-04-01','2016-06-01','2016-08-01','2016-05-31','2016-07-31' ,'2016-09-28',1000
union all
select 999,newid(),'150','Rolling Hills','N','2016-04-01','2016-09-30',2016,2016,'N',2098,1203,'2016-04-01','2016-06-01','2016-08-01','2016-05-31','2016-07-31' ,'2016-09-29',1000
union all
select 999,newid(),'150','Rolling Hills','N','2016-04-01','2016-09-30',2016,2016,'N',2098,1203,'2016-04-01','2016-06-01','2016-08-01','2016-05-31','2016-07-31' ,'2016-09-30',1000
union all
select 100,newid(),'999','Flat Plains','N','2013-04-01','2013-09-30',2013,2013,'N',0,0,'2013-04-01','2013-06-01','2013-08-01','2013-05-31','2013-07-31' ,'2013-09-28',100
union all
select 100,newid(),'999','Flat Plains','N','2013-04-01','2013-09-30',2013,2013,'N',0,0,'2013-04-01','2013-06-01','2013-08-01','2013-05-31','2013-07-31' ,'2013-09-29',200









