73609

Total SUM in SSRS doesnt display corrent number

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>.

Recommend

  • Can't get property names working with foreign keys in Code First
  • Script Assertion for json response
  • Magento. How to add simple product in cart programmatically with custom options
  • How would I search “Location” based on primary keys such as BAccountID and LocationID through Web Se
  • Exceptions when reading tutorial CSV file in the Cloudera VM
  • SMB join not working over Hive Tables
  • Avoiding N+One selects and Invalid results from eclipselink with batch read
  • Symfomy2 manual route definitions with FOSRestBundle
  • Using “where in” in spring-jdbc
  • Laravel 5 Creating a New Record that BelongsTo 3 Other Tables
  • Entity Framework Views and Linq .Where
  • grant permissions in hive does not work on hdp2.2
  • Hibernate - Cannot connect to DB
  • how to convert open xml representation of word document into RTF format using c#.net 4.0?
  • Is MVC2 ASP.Net URLDecoding automatically?
  • Deserialize Dictionary
  • SmtpClient sends email to junk
  • Microsoft Chart Controls for Microsoft .NET Framework 4.0
  • Generating random numbers directly inside a .htaccess file
  • ASP.NET MVC2 Error: No parameterless constructor defined for this object
  • UML diagram generator in Visual Studio 2010
  • Save website uploads in a subdomain
  • 3.0.0.M1: SSL - Invalid keystore format
  • Msvcr71.dll Msvcp71.dll missing
  • Change device language on Android 6.0 (Android M)
  • How to log in to an HTTPS website with Jsoup?
  • how does System.Web.HttpRequest::PathInfo work?
  • Swing - Get new component under mouseReleased
  • How to clear text inside text field when radio button is select
  • Launch Runnable Jar from Web Start
  • Why ng-show works with ng-repeat but ng-if doesn't? [duplicate]
  • Get object from AWS S3 as a stream
  • Cross-Platform Protobuf Serialization
  • Validaiting emails with Net.Mail MailAddress
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • Do I've to free mysql result after storing it?
  • How to get next/previous record number?
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?