question

db042190-2983 avatar image
0 Votes"
db042190-2983 asked db042190-2983 answered

stop repeating parts of a tablix and subtotal one section

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                     


102695-camp01.png


102744-camp02.png


sql-server-reporting-services
camp01.png (40.8 KiB)
camp02.png (67.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered

rt clicking the count that needs to be subtotaled, choosing add a total, and then adding the visibility expression you see below to the new row that ssrs added for the total seems to get me even further.

102728-camp06.png


102738-camp07.png


102802-camp08.png



camp06.png (36.3 KiB)
camp07.png (56.3 KiB)
camp08.png (50.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered

this is what the dataset looks like currently. And when i said added groups, i meant i added an additional group inside "details" for most of the columns..

 SELECT [Id]
       ,[campprogramkey]
       ,[campprogramgid]
       ,[campnumber]
       ,[campname]
       ,[camptypeflag]
       ,[opendate]
       ,[closedate]
       ,[startyear]
       ,[endyear]
       ,[isactive]
       ,[totalsession1candidates]
       ,[totalsession2candidates]
       ,[session1arrivedate]
       ,[session2arrivedate]
       ,[session3arrivedate]
       ,[session1leavedate]
       ,[session2leavedate]
       ,[session3leavedate]
       ,[session3leavecount]
   FROM [research].[dbo].[campresearch]
   order by         [campname]
           ,[camptypeflag]
           ,[opendate]
           ,[closedate]
           ,[startyear]
           ,[endyear]
           ,[isactive]
    
            ,[session1arrivedate]
            ,[totalsession1candidates]
            ,[session1leavedate]
            ,[session2arrivedate]
            ,[totalsession2candidates]
            ,[session2leavedate]
    
            ,[session3arrivedate]
            ,[session3leavedate]

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered

it looks like i am half way there. i did not know i would need to add a parent group as shown below above eac column to the left of the leave count/leave date pair.

102791-camp03.png


102792-camp04.png



camp03.png (29.8 KiB)
camp04.png (34.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered

ssrs doest allow the merging of cells where it says "Session 3" probably because there is a grouping above details. so i hid the right borders and left borders as needed and merged just the last 2 cells. if my heading would have needed to span these non mergeable cells, i would have made their padding 0 and just put part of the heading left and part right. i still need to figure out the totaling only when there are multiple session 3 leave dates in a program. And i have a bad feeling that repeating both headings on new pages will be a challenge.

102782-camp05.png



camp05.png (35.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @db042190-2983,

Thanks for your share!

I think it could be helpful to others who have the issue!

You could mark your reply as answer so other user with similar issue could see this easier. :)

Have a nice day!

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered

thx Zoehui. Finally after looking at the internet where half the posts say to click the repeat headers properties of the tablix and the other half said to use those static items in the advanced row groups section below the design pane to set those properties I finally did the latter after trial and error. Its funny to me how folks posting the right answer sometimes admit they have no idea why they need to do this. One thing i did conclude as possibly true ...you may only need to set these properties on one static row group item per row. Not sure. But on the bottom (2nd) row of column headers, i did it only on the first static member (name) of that row and all the rest repeated as well when i rendered the report. Pretty good time saver if its true. if i ever have time, i'll try the same thing on the 4 static members on hdr row 1. i'll set it only on the empty cell you see left of "Session 1" and see what happens.

103340-screenshot01.png
103461-screenshot02.png



screenshot01.png (24.7 KiB)
screenshot02.png (26.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.