Remove nulls from sub totals / grand totals in tsql rollup

Question

I currently have a script below which aggregates some data using rollup:

SELECT 
        CASE 
            WHEN GROUPING(Custodian) = 1 
                THEN 'Grand Total'
            WHEN GROUPING(PortfolioID) = 1
                THEN Custodian+''+'Total'
            ELSE Custodian

        END AS Custodian

    ,   PortfolioID
    ,   PortfolioBaseCCY
    ,   [Date]
    ,   SUM(AmountTotalBaseEquiv) AS AmountTotalBaseEquiv
    ,   ExchangeRate
    ,   AmountTotalBaseEquivUSD
    ,   PortfolioNAVUSD
    ,   SUM(TotalCashPctNAV) AS TotalCashPctNAV 

FROM @ResultSet
WHERE TotalCashPctNAV > 5
GROUP BY Custodian
    ,   PortfolioID
    ,   PortfolioBaseCCY
    ,   [Date]
    ,   AmountTotalBaseEquiv
    ,   ExchangeRate
    ,   AmountTotalBaseEquivUSD
    ,   PortfolioNAVUSD 
    ,   TotalCashPctNAV WITH ROLLUP

HAVING GROUPING_ID(Custodian
    ,   PortfolioID
    ,   PortfolioBaseCCY
    ,   [Date]
    ,   AmountTotalBaseEquiv
    ,   ExchangeRate
    ,   AmountTotalBaseEquivUSD
    ,   PortfolioNAVUSD 
    ,   TotalCashPctNAV) IN (1,255,511)

ORDER BY CASE WHEN GROUPING(Custodian) = 1 THEN 2 ELSE 1 END, Custodian, TotalCashPctNAV DESC, PortfolioID

This returns data like as an example:

Custodian   PortfolioID PortfolioBaseCCY Date         AmountTotalBaseEquiv  ExchangeRate    AmountTotalBaseEquivUSD PortfolioNAVUSD TotalCashPctNAV
XXXX        TEST        USD              11/09/2012   85708860.21           1               85708860.21             370253861.3     23.15
XXXX  Total NULL        NULL             NULL         85708860.21           NULL            NULL                    NULL            23.15
ZZZZ        TEST1       GBP              11/09/2012   48427.91              0.6225          77795.84                77795.84        100
ZZZZ        TEST2       GBP              11/09/2012   7772.61               0.6225          12486.12                12486.12        100
ZZZZ        TEST3       USD              11/09/2012   1832627.81            1               1832627.81              17343500.68     10.56
ZZZZ  Total NULL        NULL             NULL         1888828.33            NULL            NULL                    NULL            210.56
Grand Total NULL        NULL             NULL         310273031.4           NULL            NULL                    NULL            1051.71

What i would like is for the NULLS to become '' so that only the Total label and the two summed totals are the only bits of data on that particular line, is this possible?

Answer 1

If you are okay with NULL values appearing as empty strings in both data rows and total rows, you can convert all of the non-string columns to strings and use COALESCE (or ISNULL) like:

SELECT  
        CASE  
            WHEN GROUPING(Custodian) = 1  
                THEN 'Grand Total' 
            WHEN GROUPING(PortfolioID) = 1 
                THEN Custodian+' '+'Total' 
            ELSE Custodian 

        END AS Custodian 

    ,   COALESCE(PortfolioID,'') AS PortfolioID
    ,   COALESCE(PortfolioBaseCCY,'') AS  PortfolioBaseCCY
    ,   COALESCE(CONVERT(char(10),[Date],101),'') AS [Date]
    ,   SUM(AmountTotalBaseEquiv) AS AmountTotalBaseEquiv 
    ,   COALESCE(CONVERT(char(10),ExchangeRate),'') AS ExchangeRate
    ,   COALESCE(CONVERT(char(20),AmountTotalBaseEquivUSD),'') AS AmountTotalBaseEquivUSD
    ,   COALESCE(CONVERT(char(20),PortfolioNAVUSD),'') AS PortfolioNAVUSD
    ,   SUM(TotalCashPctNAV) AS TotalCashPctNAV

FROM @ResultSet 
WHERE TotalCashPctNAV > 5 
GROUP BY Custodian 
    ,   PortfolioID 
    ,   PortfolioBaseCCY 
    ,   [Date] 
    ,   AmountTotalBaseEquiv 
    ,   ExchangeRate 
    ,   AmountTotalBaseEquivUSD 
    ,   PortfolioNAVUSD  
    ,   TotalCashPctNAV WITH ROLLUP 

HAVING GROUPING_ID(Custodian 
    ,   PortfolioID 
    ,   PortfolioBaseCCY 
    ,   [Date] 
    ,   AmountTotalBaseEquiv 
    ,   ExchangeRate 
    ,   AmountTotalBaseEquivUSD 
    ,   PortfolioNAVUSD  
    ,   TotalCashPctNAV) IN (1,255,511) 

ORDER BY CASE WHEN GROUPING(Custodian) = 1 THEN 2 ELSE 1 END, Custodian, TotalCashPctNAV 

Otherwise, you can use a case expression, as you did with Custodian, to determine which rows are totals. For example:

CASE WHEN GROUPING(Custodian) + GROUPING(PortfolioId) > 0 THEN '' 
     ELSE PortfolioID END AS PortfolioID