none
Subtotal on all but one grouping

    Question

  • Howdy!  New to SSRS, did a little bit of Crystal Reports before migrating over.  I have a report which runs off of a stored procedure the report will take subscribers from different states and group them first by state, then by region (multiple states in one region) and then it will subtotal based on region.  Currently the report will do this for all 50 states and then the US Possessions as well.  The only thing I'm looking to do is to put in a subtotal for all of the regions and exclude the US Possessions.  Is this possible within Visual Studio 2008 or would I need to alter the stored proc to create a counter then simply display it? 

    Here's a snippet from the stored proc which displays the current numbers...

    declare @lsource    char(1)
        declare @lissue_id    int
        declare @product_id    int

        set @lsource = 'S'
        set @lissue_id = 900
    /*    create table #o_sub_subscription
        (
            [customer_id]            int            not null,
            [active_version_type]    char(1)        not null,
            [payment_status_id]        tinyint        not null,
            [quantity]                smallint    not null,
            [class_definition_id]    int            null,
            [class]                    varchar(3)    null,
            [territory_seq]            smallint    not null,
            [territory_desc]        varchar(75)    not null,
            [region_desc]            varchar(75)    not null,
            [region_scf_range]        varchar(75) not null,
            [territory_region_seq]    int            not null
        );
    */

            --insert into #o_sub_subscription
                select    customer_id                = s.customer_id,
                        active_version_type        = isnull(m.active_version_type, 'U'),
                        payment_status_id        = isnull(s.payment_status_id, 0),
                        quantity                = isnull(s.quantity, 0),
                        class_definition_id        = isnull(s.class_definition_id, 999),
                        class                    = isnull(cd.class, 'XX'),
                        territory_seq            = isnull(t.territory_seq, 99),
                        territory_desc            = isnull(t.territory_desc, 'Unknown'),
                        region_desc                = isnull(case when t.territory_seq = 13 or tr.region_desc = '' then isnull(bpa.country_name, pa.country) else tr.region_desc end,'Unknown'),
                        region_scf_range        = isnull(tr.region_scf_range, ''),
                        territory_region_seq    = isnull(case tr.territory_region_seq when -1 then 9999 else tr.territory_region_seq end, 9999)
                from dbo.o_sub_issue                    i    with (nolock)
                inner join dbo.o_sub_subscription        s    with (nolock) on i.product_id = s.product_id
                inner join dbo.o_cst_customer            c    on s.customer_id = c.customer_id AND c.[status] = 1
                inner join dbo.o_bas_class_definition    cd    with (nolock) on s.class_definition_id = cd.class_definition_id
                left  join dbo.o_cst_postal_address        pa    with (nolock) on s.shipping_address_id = pa.postal_address_id and s.customer_id = pa.customer_id --and pa.status_code in (1,2)
                left  join (select    bpa.country_code,
                                    territory_country_id    = min(t.territory_id),
                                    territory_count            = count(distinct t.territory_id),
                                    territory_country_seq    = min(t.territory_seq),
                                    country_name            = isnull(bpa.country_name, 'Unknown')
                                from dbo.o_bas_bpa_country_region_mapping    bpa    with (nolock)
                                inner join dbo.o_bas_territory_region        tr    with (nolock) on bpa.country_code = tr.country_code
                                inner join dbo.o_bas_territory                t    with (nolock) on tr.territory_id = t.territory_id
                                group by bpa.country_code,isnull(bpa.country_name, 'Unknown')
                            ) bpa on pa.country_code = bpa.country_code
                left  join dbo.o_bas_territory_region    tr    with (nolock) on pa.country_code = tr.country_code and pa.region_code = tr.region_code
                left  join dbo.o_bas_territory            t    with (nolock) on case bpa.territory_count
                                                                                    when 1 then bpa.territory_country_id
                                                                                    else tr.territory_id
                                                                                end = t.territory_id
                left  join dbo.MediaType()                m    on s.active_version_type = m.active_version_type
                where    cd.class in ('1','2')
                        and i.issue_id = @lissue_id and tr.country_code = 'USA'

    Wednesday, April 11, 2012 3:58 PM

Answers

  •   Currently the report will do this for all 50 states and then the US Possessions as well.  The only thing I'm looking to do is to put in a subtotal for all of the regions and exclude the US Possessions.  Is this possible within Visual Studio 2008 or would I need to alter the stored proc to create a counter then simply display it?

    Hi mnovosel,

    In your case, I think that it is not necessary to alter the Stored Procedure. If you want to exclude the "US Possessions" subtotal, I think that you can refer to SSASBeginner's solution to hide the specify subtotal. For the grant total, we can use the following expression to calculate other subtotals:
    =Sum(IIF(Fields!Region.Value="US Possession",Nothing, Fields!NonPaid.Value))

    If you have any more questions, please feel free to let me know.

    Regards,
    Bin Long


    Bin Long

    TechNet Community Support

    Tuesday, April 17, 2012 9:20 AM

All replies

  • You can use the row visibility feature to hide the sub-total. Right click on the row in your Matrix which belongs to SubTotal and in the row visibility property, you can write an expression like this -

    =IIF(Fields!Region.Value = "US Possession",True,False)


    Hope this Helps!

    Wednesday, April 11, 2012 4:38 PM
  • I'm thinking that it may not be a bad idea to modify the stored procedure to simply add a group.  Would anyone know the syntax to create a custom column which I could then group on?  I have an int field called "Territory.Territory_Seq".  If the value is 1 through 9 then its a US State.  If it's 10 then it's a US Possession.  Can I simply add into the stored proc something that assigns a new column either a STATE or POSSESS value?
    Wednesday, April 11, 2012 6:24 PM
  •   Currently the report will do this for all 50 states and then the US Possessions as well.  The only thing I'm looking to do is to put in a subtotal for all of the regions and exclude the US Possessions.  Is this possible within Visual Studio 2008 or would I need to alter the stored proc to create a counter then simply display it?

    Hi mnovosel,

    In your case, I think that it is not necessary to alter the Stored Procedure. If you want to exclude the "US Possessions" subtotal, I think that you can refer to SSASBeginner's solution to hide the specify subtotal. For the grant total, we can use the following expression to calculate other subtotals:
    =Sum(IIF(Fields!Region.Value="US Possession",Nothing, Fields!NonPaid.Value))

    If you have any more questions, please feel free to let me know.

    Regards,
    Bin Long


    Bin Long

    TechNet Community Support

    Tuesday, April 17, 2012 9:20 AM