SSRS Sum Grouping

Posted on 2014-02-03
Last Modified: 2014-03-06
I created a report from SSRS and did a summary at the detail table header.  The operation is use for the grouping and then there's the material sequence that is use for determine what sub component makes up for that fabricated part.

Example fabricated part 123ABC has subcomponent of 399DD and 299FDS that makes up that part.  The material and labor from those 2 parts also make up the total cost to make part 123ABC.  I am able to get the summary at the header detail level.

However, I also need to do a final summary at the table footer.  I can't seem to total up the header detail grouping.

I tried doing the following, but kept stating I can't use it.  Can only be use at headers and footers.  What can I do so that I can sum up the highlighted in my footer table?

=RunningValue(ReportItems!TotalSum.Value, sum, nothing)

TotalSum is the "Total Cost" highlighted in blue.  I want all these to sum up at row 12 with total of $6105.  However, it kept giving me error.

Report Example
Question by:holemania
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
LVL 37

Expert Comment

ID: 39831737
What happens if you use the following expression?

LVL 37

Expert Comment

ID: 39831756
Ow, just noticed that you're using the ReportItems collection, that's the culprit.  You should try to use Fields to calculate these sums.

So instead of


it would be something like

=Sum(Fields!Qty.Value * (Fields!LaborCost.Value + Fields!MaterialCost.Value))

Author Comment

ID: 39832509
When I do that, my sum isn't correct.  However, the summary for the header detail in the table is.  I was hoping I can add that up instead.

Would custom code work to just add that Report item to get my total?  Not sure how to go about creating custom code.  Will try to google, but if you can provide example, that would be appreciated.
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?


Author Comment

ID: 39834157
So I tried this custom code by adding my sum from the grouping together and hoping that I can use this for the footer table summary.

I'm getting a $0 dollar amount for the footer detail summary.  Any ideas?

Dim Public total as Decimal
Public Function addTotal(ByVal amt as Decimal) As Decimal
  total = total + amt
  return amt
End Function

Public Function GetTotal()
  return total
End Function

So from the header table summary, I put this in there.  The output at the summary header detail is right.!Qty.Value * (Fields!LaborCost.Value + Fields!MaterialCost.Value)))

For my footer total summary I put this in there.


Now my output is $0 which is not true.  Should be $6105.  Any ideas?
LVL 37

Expert Comment

ID: 39834838
When I do that, my sum isn't correct.  However, the summary for the header detail in the table is.

So the blue total is correct but the green one isn't, is that what you mean?

I was hoping I can add that up instead.

It doesn't work that way, you need to use exactly the same formula as for your blue total.  Thanks to the scope where the expression is located SSRS knows what to calculate.

Would custom code work...

You don't need custom code for that, it should be possible using the method I explained in my previous post.  Are you sure your formula was correct?
LVL 37

Expert Comment

ID: 39834864
Erm, now that I've taken a closer look at your numbers in the blue rows, I noticed that your total is not the total for the operation even though you've named it "table header detail group 1".  In fact, it seems as if it's just the first line of the operation details.  Was that the intention?
LVL 37

Accepted Solution

ValentinoV earned 500 total points
ID: 39834867
Have a look at the following attachment, it contains a table similar to what you've posted in your question...

Author Comment

ID: 39836442
Thanks for the example, but here's my issue.  The item highlighted in blue, may call out different quantity.  Example Material "8933BD" is the main part that the sub component parts "9902AB" and "88CC32" is required to make.  Both those sub components make up 2 pieces for material "8933BD".  I need to take the combination of (Labor Cost + Material Cost) * Qty from the "8933BD" line to get the Total Cost.  This then needs to total up for my summary (example like the highlighted green you had).

Also note that the first line with the operation is my raw output as well.  I'm just calculating the "Total Cost".

So my raw data look like the following:

5, 0, 8933BD, 2, 50, 250
5, 1, 9902AB, 1, 25, 200
5, 2, 88CC32, 1, 25, 50

The 0 with the first line above tells that it's the main fab parts that the sub component 1 and 2 will make.  

However, if I do it similar to your example, then it is summing up the sub component with the main operation line as well causing my total to be incorrect.

I also tried doing the following to see if it can be grouped base on the grouping name without luck either.  Example, I named the grouping to "Total" and then use this as my summary without much luck.

=Sum((Fields!LaborCost.Value + Fields!MaterialCost.Value)*Fields!Qty.Value, "Total")

So if there's a way I can only sum up the first line with the "Total Cost" highlighted in blue, and put that in the summary at the bottom of the detail table footer, that would fix my issue.  However, my custom code doesn't seem to be working either.

Author Comment

ID: 39837092
Seems the custom code is working now that I tried it in Visual Studio 2008.  However, reporting services is running on 2005.  So I can't deploy it out.  

Any reason why that custom code would work in 2008 and not 2005?

Author Closing Comment

ID: 39909660
Thank you.  Didn't use this example since custom code is now working for me.  Some reason it wasn't working in SQL 2005, but we also have 2008 and was able to get it to work.

Give you points for helping me out.  Can utilize your example for future need.

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question