highest row group doesnt allow adding a total after

Question

hi we run 2014 enterprise. I had to anonymize the following but i think i can state the question clearly.

if i have trouble uploading any images or code, i'll upload them in subsequent "answers" on this post.

the report i am showing has 4 row groups including details2. row group details2 includes everything right of and including dim01.

row group zipcodeplus2 includes everything between and including name and zip +.

row group acct includes only acct (aka dim05).

row group type includes only type (aka dim03).

when i rt clicked row group "acct" and added total "after", i had no problem achieving my goal of a subtotal on acct as you can see in the rendering.

but when i rt clicked row group "type" and added total "after", i got a grand total (which i want also) but i want a subtotal on "type" as well. how can i achieve that, say without adding a bogus group at a higher level?

USE [research]  
      
    SET ANSI_NULLS ON  
    GO  
      
    SET QUOTED_IDENTIFIER ON  
    GO  
      
    CREATE TABLE [dbo].[Subtotresearch](  
           [ID] [int] IDENTITY(1,1) NOT NULL,  
           [dim01] [varchar](50) NULL,  
           [dim02] [varchar](6) NULL,  
           [dim03] [char](10) NULL,  
           [dim05] [char](9) NULL,  
           [Name] [varchar](100) NULL,  
           [Status] [varchar](10) NULL,  
           [dim06] [varchar](50) NULL,  
           [dim07] varchar(60) NULL,  
           [amount1] [decimal](15, 4) NULL,  
           [amount2] [decimal](15, 4) NULL,  
           [dim08] [char](9) NULL,  
           [AddressCity] [varchar](50) NULL,  
           [StreetNumber] [varchar](10) NULL,  
           [StreetName] [varchar](60) NULL,  
           [Apartment] [varchar](8) NULL,  
           [State] [char](3) NULL,  
           [ZipCode] [varchar](14) NULL,  
           [ZipCodePlus] [varchar](8) NULL,  
    CONSTRAINT [PK_dbo.DimAccount] 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   
    [dbo].[Subtotresearch]  
    (  
           [dim01]  
          ,[dim02]  
          ,[dim03]  
          ,[dim05]  
          ,[Name]  
          ,[Status]  
          ,[dim06]  
          ,[dim07]  
          ,[amount1]  
          ,[amount2]  
          ,[dim08]  
          ,[AddressCity]  
          ,[StreetNumber]  
          ,[StreetName]  
          ,[Apartment]  
          ,[State]  
          ,[ZipCode]  
          ,[ZipCodePlus]  
    )  
    select   
      
    'dim01 - 01',  
    'd2 - 1',  
    'dim03 - 01',--just make 2 of these  
    'dim05 - 1',  
    'john smith',  
    'status 01',  
    'dim06 - 01',  
    'dim07 - 01',  
    1,  
    2,  
    'dim08 - 1',  
    'smallville',  
    '123',  
    'main',  
    'up',  
    'mn',  
    '12345',  
    '1234'  
      
    union all  
      
    select   
      
    'dim01 - 02',  
    'd2 - 2',  
    'dim03 - 01',--just make 2 of these  
    'dim05 - 1',  
    'john smith',  
    'status 01',  
    'dim06 - 01',  
    'dim07 - 01',  
    3,  
    4,  
    'dim08 - 1',  
    'smallville',  
    '123',  
    'main',  
    'up',  
    'mn',  
    '12345',  
    '1234'  
      
    union all  
      
    select   
      
    'dim01 - 03',  
    'd2 - 3',  
    'dim03 - 02',--just make 2 of these  
    'dim05 - 2',  
    'jane doe',  
    'status 01',  
    'dim06 - 01',  
    'dim07 - 02',  
    4,  
    5,  
    'dim08 - 2',  
    'louisville',  
    '345',  
    'first',  
    'dn',  
    'ky',  
    '67890',  
    '5679'  
      
    --this is the dataset used in the report  
    SELECT    
           [dim01]  
          ,[dim02]  
          ,[dim03]  
          ,[dim05]  
          ,[Name]  
          ,[Status]  
          ,[dim06]  
          ,[dim07]  
          ,[amount1]  
          ,[amount2]  
          ,[dim08]  
          ,[AddressCity]  
          ,[StreetNumber]  
          ,[StreetName]  
          ,[Apartment]  
          ,[State]  
          ,[ZipCode]  
          ,[ZipCodePlus]  
      FROM [dbo].[Subtotresearch]  
      
      order by dim03,dim05,dim01  

Answer 1

Hi @db042190 ,

but when i rt clicked row group "type" and added total "after", i got a grand total (which i want also) but i want a subtotal on "type" as well.

What are the grand totals and subtotals of the "Type" group you are referring to. In my opinion, because the "Type" group is currently the highest group, the value obtained after "Add Total" is the total of the group or the subtotal of the detail group.

I reproduced your report locally.

Design:

Preview：

When I was creating groups, I selected [dim01]——Group by [ZipCodePlus], selected [Name]_Group by [dim05], and selected [dim05]——Group by [dim03].

In the row groups below, select the "ACCT" group-Add Total-After, at this time I got the Total of the ACCT group
Select the "Type" group-Add Total-After, and now I get the Total of the Type group. Because the Type group is the highest group, the subtotal is the sum of all row groups.

So what value are you missing now?
Best Regards,
Joy

---

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.

Answer 2

thx joyzhao. you should have 2 subtotal "flavors" and one grand total.

One subtotal for acct (2 times or once if you suppress when only 1 row like i did) , one subtotal for type (2 times) and a grand total.

BTW, when i rt click acct group and add total, it indents under name. yours didn't . and if your type subtotal was really a subtotal, it should show both for types dim03 - 01 and dim03 - 02.

i should have mentioned that i suppressed acct subtotals when only one row appeared for acct.

i think i should post my xml but i'll need to anonymize the text box names first. Thx.

Answer 3

ok, here is more info... all i did was to insert an additional data point for clarity and anonymize some text boxes...

in the image,

"a" is the new data point.
"b" is a label = what i think ssrs considers that subtotal.
"c" is what i think ssrs considers that total.
"d" is confirmation that indeed "c" is a grand total.

attached are a re rendering, new data point insert and xml (you will have to chg the conn string) .

109855-xmlforresearchreport.txt

Answer 4

it looks like the new data point insert got lost, so here it is again...

use research

insert 
[dbo].[Subtotresearch]
(
       [dim01]
      ,[dim02]
      ,[dim03]
      ,[dim05]
      ,[Name]
      ,[Status]
      ,[dim06]
      ,[dim07]
      ,[amount1]
      ,[amount2]
      ,[dim08]
      ,[AddressCity]
      ,[StreetNumber]
      ,[StreetName]
      ,[Apartment]
      ,[State]
      ,[ZipCode]
      ,[ZipCodePlus]
)
--select 

--'dim01 - 01',
--'d2 - 1',
--'dim03 - 01',--just make 2 of these
--'dim05 - 1',
--'john smith',
--'status 01',
--'dim06 - 01',
--'dim07 - 01',
--1,
--2,
--'dim08 - 1',
--'smallville',
--'123',
--'main',
--'up',
--'mn',
--'12345',
--'1234'

--union all

--select 

--'dim01 - 02',
--'d2 - 2',
--'dim03 - 01',--just make 2 of these
--'dim05 - 1',
--'john smith',
--'status 01',
--'dim06 - 01',
--'dim07 - 01',
--3,
--4,
--'dim08 - 1',
--'smallville',
--'123',
--'main',
--'up',
--'mn',
--'12345',
--'1234'

--union all

--select 

--'dim01 - 03',
--'d2 - 3',
--'dim03 - 02',--just make 2 of these
--'dim05 - 2',
--'jane doe',
--'status 01',
--'dim06 - 01',
--'dim07 - 02',
--4,
--5,
--'dim08 - 2',
--'louisville',
--'345',
--'first',
--'dn',
--'ky',
--'67890',
--'5679'

--union all

select 

'dim01 - 01',
'd2 - 4',
'dim03 - 01',--just make 2 of these         --type
'dim05 - 3',                                --acct 
'john miller',                              --name 
'status 01',
'dim06 - 01',
'dim07 - 01',
6,                                          
7,                                          
'dim08 - 3',                                
'hooterville',
'678',
'1st street',
'3',
'nc',
'10111',
'1011'