73609
Question:
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?
<a href="https://i.stack.imgur.com/CL8Q7.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/CL8Q7.png" data-original="https://i.stack.imgur.com/CL8Q7.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>
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.
<a href="https://i.stack.imgur.com/JcKRs.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/JcKRs.png" data-original="https://i.stack.imgur.com/JcKRs.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>
<a href="https://i.stack.imgur.com/o3qWN.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/o3qWN.png" data-original="https://i.stack.imgur.com/o3qWN.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>
I have added a new group with grouping expression =1 But still same incorrect totals <a href="https://i.stack.imgur.com/BEd4E.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/BEd4E.png" data-original="https://i.stack.imgur.com/BEd4E.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>
Answer1:I believe your problem is that your group is filtering for the <strong>top 10</strong> but the Total line isn't.
Add a new group <strong>Add Group -> Adjacent Below</strong> 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 <strong>TOP 10 Filter</strong> to the new group.
<a href="https://i.stack.imgur.com/S1Jlz.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/S1Jlz.png" data-original="https://i.stack.imgur.com/S1Jlz.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>
Answer2: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 (<a href="https://msdn.microsoft.com/en-us/library/dd255256.aspx" rel="nofollow">https://msdn.microsoft.com/en-us/library/dd255256.aspx</a>):
<blockquote>Group filters are not used when calculating aggregates for data regions.
</blockquote>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
<strong>Edit:</strong> Apparently there is an MSDN thread that covers this scenario. <a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0b21bab0-ff02-4655-a69c-efedfb2a7077" rel="nofollow">https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0b21bab0-ff02-4655-a69c-efedfb2a7077</a>.
