Total SUM in SSRS does not display root number

Question

The reason for this probably lies in my request, because I used MAX to collect a unique record. But now my SSRS report does not display the correct NetWrittenPremium amount. What will be the workaround for this problem? Here is my request:

 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 integrate in Description and ClassCode. Sort it using SUM(NetWrittenPremium) DESC and Filtering by SUM(NetWrittenPremium) TOP 10 . And another group is MonthNum.

I added a new group with the group expression = 1 But all the same wrong totals

Answer 1

The problem is that group filters do not apply to aggregate functions. So your SUM () s collects everything. From the MSDN documentation for the expression area for totals, aggregates, and inline collections ( https://msdn.microsoft.com/en-us/library/dd255256.aspx ):

Group filters are not used in the calculation of aggregates in these regions.

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

If this is not an option, you should use the expression "Rank" instead. Create a "totaling" line at the level of detail that counts dollar values ​​using RunningValue. Then set the visibility criteria for:

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

Edit: There seems to be an MSDN stream that covers this scenario. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0b21bab0-ff02-4655-a69c-efedfb2a7077 .

Answer 2

I believe your problem is that your group is filtering for the top 10 , but the Total line is not.

Add a new group Add group → Adjacent below for your common row. Group it by 1 (so that it groups them all together). Then use the same expressions as for the other cells (add the SUM if they are not already installed). Add the same TOP 10 Filter to the new group.