Total SUM in SSRS doesnt display corrent number

问题:

The reason for that probably lays in my query, because I used MAX to pick up the unique record. But now my SSRS report does not display correct NetWrittenPremium amount. What would be a workaround for this problem? Here is my query:

select  b.YearNum,
        b.MonthNum,
        ClassCode,
        QLL.Description,
        SUM( Premium) as NetWrittenPremium
FROM        tblCalendar b  
LEFT JOIN   ProductionReportMetrics prm ON b.MonthNum=Month(prm.EffectiveDate) AND b.YearNum = YEAR(EffectiveDate)  
AND prm.EffectiveDate >=DateAdd(yy, -1, DATEADD(d, 1, EOMONTH(GETDATE()))) AND prm.EffectiveDate <= EOMONTH(GETDATE())  AND CompanyLine = 'Ironshore Insurance Company' 
LEFT JOIN  NetRate_Quote_Insur_Quote Q ON prm.NetRate_QuoteID = Q.QuoteID
LEFT JOIN NetRate_Quote_Insur_Quote_Locat QL ON Q.QuoteID = QL.QuoteID  

LEFT JOIN   (SELECT * FROM NetRate_Quote_Insur_Quote_Locat_Liabi nqI 
            JOIN ( SELECT LocationID as LocID, MAX(ClassCode) as ClCode 
            FROM NetRate_Quote_Insur_Quote_Locat_Liabi  GROUP BY LocationID ) nqA 
            ON nqA.LocID = nqI.LocationID AND nqA.ClCode = nqI.ClassCode ) QLL 
            ON QLL.LocationID = QL.LocationID 

WHERE ( b.YearNum = YEAR(GETDATE())-1 and b.MonthNum >= MONTH(GETDATE())+1 ) OR 
                    ( b.YearNum = YEAR(GETDATE()) and b.MonthNum <= MONTH(GETDATE()) ) 
GROUP BY b.YearNum,b.MonthNum,ClassCode,        QLL.Description

My tablix structure:I am gruping on Description and ClassCode.Sorting it by SUM(NetWrittenPremium) DESC and Filtering by SUM(NetWrittenPremium) TOP 10. And another group is MonthNum.

I have added a new group with grouping expression =1 But still same incorrect totals

回答1:

I believe your problem is that your group is filtering for the top 10 but the Total line isn't.

Add a new group Add Group -> Adjacent Below for your total line. Group it on 1 (so it groups them all together). Then use the same expressions as you used for the other cells (add a SUM if they aren't already). Add the same TOP 10 Filter to the new group.

回答2:

The issue here is that group filters don't apply to aggregate functions. So your SUM()s are picking everything up. From the MSDN documentation for Expression Scope for Totals, Aggregates, and Built-in Collections (https://msdn.microsoft.com/en-us/library/dd255256.aspx):

Group filters are not used when calculating aggregates for data regions.

If possible, it is probably best to move the Top 10 statement into the query instead.

If that's not an option, you should be able to use your "Rank" expression instead. Create a "totaling" row at the detail level that counts up running dollar values using RunningValue. Then set the visibility criteria to:

=RunningValue(Fields!ClassCode.Value,CountDistinct,Nothing) <> 10  

Edit: Apparently there is an MSDN thread that covers this scenario. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0b21bab0-ff02-4655-a69c-efedfb2a7077.