How can I display Grand Total as a separate bar in stacked chart in SSRS 2012

Question

How can I display Grand Total as a separate bar in stacked chart? I broken down by Category Group which are New Business and Renewal. So how can I have another top bar that would show me a sum of $ for New Business and Renewal? Something like that:

EDIT:

Below is my main SELECT statement. I have created In first UNION ALL I created 'EQGrandTotal' as another BusinessType, then I summed up all types of premiums broken down by Description.

   SELECT   SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Bound'         THEN [Bound Premium] ELSE 0 END) as EQBoundNB,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Lost'          THEN [LostPremium] ELSE 0 END) as EQLostNB,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Not Taken Up'  THEN [NotTakenUpPremium] ELSE 0 END) as EQNtUpNB,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Quoted'        THEN [QuotedPremium] ELSE 0 END) as EQQuotedNB,
                    /* Earthquake, Renewal, by Description */
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Bound'          THEN [Bound Premium] ELSE 0 END) as EQBoundRN,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Lost'           THEN [LostPremium] ELSE 0 END) as EQLostRN,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Not Taken Up'   THEN [NotTakenUpPremium] ELSE 0 END) as EQNtUpRN,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Quoted'         THEN [QuotedPremium] ELSE 0 END) as EQQuotedRN,

                /* Wind, New Business, by Description */
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'New Business' AND Description = 'Bound'           THEN [Bound Premium] ELSE 0 END) as WindBoundNB,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'New Business' AND Description = 'Lost'            THEN [LostPremium] ELSE 0 END) as WindLostNB,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'New Business' AND Description = 'Not Taken Up'    THEN [NotTakenUpPremium] ELSE 0 END) as WindNtUpNB,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'New Business' AND Description = 'Quoted'          THEN [QuotedPremium] ELSE 0 END) as WindQuotedNB,
                /* Wind, Renewal, by Description */
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'Renewal' AND Description = 'Bound'        THEN [Bound Premium] ELSE 0 END) as WindBoundRN,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'Renewal' AND Description = 'Lost'         THEN [LostPremium] ELSE 0 END) as WindLostRN,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'Renewal' AND Description = 'Not Taken Up' THEN [NotTakenUpPremium] ELSE 0 END) as WindNtUpRN,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'Renewal' AND Description = 'Quoted'       THEN [QuotedPremium] ELSE 0 END) as WindQuotedRN,
        CASE WHEN LineOfCoverage = 'Earthquake' THEN  'Earthquake' ELSE 'Wind' END as LineOfCoverage,
        'Earthquake' as Earthquake,
        'Wind' as Wind,
        BusinessType,
        Description,
        NULL as Total
FROM    cte_Quotes
GROUP BY LineOfCoverage,BusinessType,Description
UNION ALL 
SELECT 
        NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
        CASE WHEN LineOfCoverage = 'Earthquake' THEN  'Earthquake' ELSE 'Wind' END as LineOfCoverage,
        NULL,
        NULL,
        'EQGrandTotal' as BusinessType,
        --NULL,
        Description, -- description
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Bound'         THEN [Bound Premium] ELSE 0 END)+
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Lost'          THEN [LostPremium] ELSE 0 END) +
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Not Taken Up'  THEN [NotTakenUpPremium] ELSE 0 END)+
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Quoted'        THEN [QuotedPremium] ELSE 0 END) +
                    /* Earthquake, Renewal, by Description */
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Bound'          THEN [Bound Premium] ELSE 0 END)+
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Lost'           THEN [LostPremium] ELSE 0 END) +
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Not Taken Up'   THEN [NotTakenUpPremium] ELSE 0 END)+
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Quoted'         THEN [QuotedPremium] ELSE 0 END) as Total   
FROM    cte_Quotes
GROUP BY Description,LineOfCoverage,BusinessType

So I got another "BusinessType" group but the line is solid color. My expression for column Total:

=Sum(IIF(Fields!BusinessType.Value = "EQGrandTotal" and Fields!LineOfCoverage.Value = "Earthquake", Fields!Total.Value,0))

EDIT 2: @Richards Removed UNION ALL and instead Added 4 new columns:

SELECT  SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Bound'         THEN [Bound Premium] ELSE 0 END) as EQBoundNB,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Lost'          THEN [LostPremium] ELSE 0 END) as EQLostNB,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Not Taken Up'  THEN [NotTakenUpPremium] ELSE 0 END) as EQNtUpNB,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Quoted'        THEN [QuotedPremium] ELSE 0 END) as EQQuotedNB,
                    /* Earthquake, Renewal, by Description */
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Bound'          THEN [Bound Premium] ELSE 0 END) as EQBoundRN,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Lost'           THEN [LostPremium] ELSE 0 END) as EQLostRN,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Not Taken Up'   THEN [NotTakenUpPremium] ELSE 0 END) as EQNtUpRN,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Quoted'         THEN [QuotedPremium] ELSE 0 END) as EQQuotedRN,

                /* Wind, New Business, by Description */
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'New Business' AND Description = 'Bound'           THEN [Bound Premium] ELSE 0 END) as WindBoundNB,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'New Business' AND Description = 'Lost'            THEN [LostPremium] ELSE 0 END) as WindLostNB,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'New Business' AND Description = 'Not Taken Up'    THEN [NotTakenUpPremium] ELSE 0 END) as WindNtUpNB,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'New Business' AND Description = 'Quoted'          THEN [QuotedPremium] ELSE 0 END) as WindQuotedNB,
                /* Wind, Renewal, by Description */
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'Renewal' AND Description = 'Bound'        THEN [Bound Premium] ELSE 0 END) as WindBoundRN,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'Renewal' AND Description = 'Lost'         THEN [LostPremium] ELSE 0 END) as WindLostRN,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'Renewal' AND Description = 'Not Taken Up' THEN [NotTakenUpPremium] ELSE 0 END) as WindNtUpRN,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'Renewal' AND Description = 'Quoted'       THEN [QuotedPremium] ELSE 0 END) as WindQuotedRN,
        CASE WHEN LineOfCoverage = 'Earthquake' THEN  'Earthquake' ELSE 'Wind' END as LineOfCoverage,
        'Earthquake' as Earthquake,
        'Wind' as Wind,
        BusinessType,
        Description,

/*  Test @Richards*/

        SUM(CASE WHEN  Description = 'Bound'        THEN [Bound Premium] ELSE 0 END) as TotalBound,
        SUM(CASE WHEN Description = 'Lost'          THEN [LostPremium] ELSE 0 END) as TotalLost,
        SUM(CASE WHEN  Description = 'Not Taken Up' THEN [NotTakenUpPremium] ELSE 0 END) as TotalNTU,
        SUM(CASE WHEN  Description = 'Quoted'       THEN [QuotedPremium] ELSE 0 END) as TotalQuoted
FROM    cte_Quotes
GROUP BY LineOfCoverage,BusinessType,Description

The result in SSRS: I added SUM(TotalBound) as a value in a chart data and seems like the totals are gonna go on a top of the bars.

EDIT 3: Added UNION ALL statement with Totals for each Description

SELECT  SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Bound'         THEN [Bound Premium] ELSE 0 END) as EQBoundNB,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Lost'          THEN [LostPremium] ELSE 0 END) as EQLostNB,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Not Taken Up'  THEN [NotTakenUpPremium] ELSE 0 END) as EQNtUpNB,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'New Business' AND Description = 'Quoted'        THEN [QuotedPremium] ELSE 0 END) as EQQuotedNB,
                    /* Earthquake, Renewal, by Description */
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Bound'          THEN [Bound Premium] ELSE 0 END) as EQBoundRN,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Lost'           THEN [LostPremium] ELSE 0 END) as EQLostRN,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Not Taken Up'   THEN [NotTakenUpPremium] ELSE 0 END) as EQNtUpRN,
        SUM(CASE WHEN LineOfCoverage = 'Earthquake' AND BusinessType = 'Renewal' AND Description = 'Quoted'         THEN [QuotedPremium] ELSE 0 END) as EQQuotedRN,

                /* Wind, New Business, by Description */
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'New Business' AND Description = 'Bound'           THEN [Bound Premium] ELSE 0 END) as WindBoundNB,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'New Business' AND Description = 'Lost'            THEN [LostPremium] ELSE 0 END) as WindLostNB,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'New Business' AND Description = 'Not Taken Up'    THEN [NotTakenUpPremium] ELSE 0 END) as WindNtUpNB,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'New Business' AND Description = 'Quoted'          THEN [QuotedPremium] ELSE 0 END) as WindQuotedNB,
                /* Wind, Renewal, by Description */
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'Renewal' AND Description = 'Bound'        THEN [Bound Premium] ELSE 0 END) as WindBoundRN,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'Renewal' AND Description = 'Lost'         THEN [LostPremium] ELSE 0 END) as WindLostRN,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'Renewal' AND Description = 'Not Taken Up' THEN [NotTakenUpPremium] ELSE 0 END) as WindNtUpRN,
        SUM(CASE WHEN LineOfCoverage = 'Wind' AND BusinessType = 'Renewal' AND Description = 'Quoted'       THEN [QuotedPremium] ELSE 0 END) as WindQuotedRN,
        CASE WHEN LineOfCoverage = 'Earthquake' THEN  'Earthquake' ELSE 'Wind' END as LineOfCoverage,
        'Earthquake' as Earthquake,
        'Wind' as Wind,
        BusinessType,
        Description,
        NULL as TotalBound,
        NULL as TotalLost,
        NULL as TotalNTU,
        NULL as TotalQuoted

FROM    cte_Quotes
GROUP BY LineOfCoverage,BusinessType,Description
UNION ALL 
SELECT  NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
        LineOfCoverage,--CASE WHEN LineOfCoverage = 'Earthquake' THEN  'Earthquake' ELSE 'Wind' END as LineOfCoverage,
        'Earthquake' as Earthquake,
        'Wind' as Wind,
        'Grand Total' as BusinessType,
        --NULL,
        Description, -- description
        SUM(CASE WHEN  Description = 'Bound'        THEN [Bound Premium] ELSE 0 END) as TotalBound,
        SUM(CASE WHEN Description = 'Lost'          THEN [LostPremium] ELSE 0 END) as TotalLost,
        SUM(CASE WHEN  Description = 'Not Taken Up' THEN [NotTakenUpPremium] ELSE 0 END) as TotalNTU,
        SUM(CASE WHEN  Description = 'Quoted'       THEN [QuotedPremium] ELSE 0 END) as TotalQuoted
FROM    cte_Quotes
GROUP BY Description,LineOfCoverage,BusinessType    

Result in SSRS:

Answer 1

As stated in my original comment, adding a UNION to the SQL that rolls up the summary data to the proper level made this possible.

This is useful technique in report writing when the tool won't do the rolling up for you.

Here is a simple example (from a database I have handy):

SELECT T.account_type_desc AS category, COUNT(1) AS cnt
FROM account A JOIN account_type T
ON A.account_type_key = T.account_type_key
GROUP BY T.account_type_desc

UNION ALL

SELECT 'Grand Total' AS category, COUNT(1) AS cnt
FROM account A

Glad I could help!!