SSRS Sum Grouping

Posted on 2014-02-03
Medium Priority
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.
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.


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.

=code.total(Sum(Fields!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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

770 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