question

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

highest row group doesnt allow adding a total after

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
researchdesign.png (19.1 KiB)
researchrender.png (24.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.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi @db042190-2983 ,

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.


01.jpg (61.1 KiB)
02.jpg (66.3 KiB)
03.jpg (6.0 KiB)
04.jpg (13.3 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 db042190-2983 commented

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.

· 2
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.

Since it is not possible to view your report in the forum, I suggest you consider choosing phone support service(you need to pay some fees). This can usually resolve your issue quickly.
https://support.microsoft.com/en-us/help/4051701/global-customer-service-phone-numbers
https://support.microsoft.com/en-us/supportforbusiness/productselection

0 Votes 0 ·

thx, you only have 2 subtotal types so clearly you haven't achieved a subtotal for "grand", "type" and "acct" either.

i will add a data point or two to make my point, resubmit a report rendering and submit my xml. I will document how the extra data points were inserted. we dont want to pay for something this trivial.

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

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



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 db042190-2983 commented

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'
· 2
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.

I am confused. How to get your "a" value?

0 Votes 0 ·

its column dim05 in the table. its ok with me if you rename that col in the table to "acct". but my rdl already refers to it as dim05. sorry about that.

0 Votes 0 ·