highest row group doesnt allow adding a total after

db042190 1,516 Reputation points
2021-06-23T19:23:45.153+00:00

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  

108708-researchdesign.png

108709-researchrender.png

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,788 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Joyzhao-MSFT 15,561 Reputation points
    2021-06-24T02:28:54.69+00:00

    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:
    108835-01.jpg
    Preview:
    108842-02.jpg

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

    108836-03.jpg

    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.

    108785-04.jpg

    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.

    0 comments No comments

  2. db042190 1,516 Reputation points
    2021-06-24T11:21:03.483+00:00

    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.


  3. db042190 1,516 Reputation points
    2021-06-28T12:18:44.82+00:00

    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) .

    109932-researchrenderwithextradatapoint.png

    109855-xmlforresearchreport.txt

    0 comments No comments

  4. db042190 1,516 Reputation points
    2021-06-28T12:21:26.92+00:00

    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'