Solved

SSRS Sum Grouping

Posted on 2014-02-03
10
3,788 Views
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
0
Comment
Question by:holemania
  • 5
  • 5
10 Comments
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
What happens if you use the following expression?

=Sum(ReportItems!TotalSum.Value)
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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

=Sum(ReportItems!TotalSum.Value)

it would be something like

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

Author Comment

by:holemania
Comment Utility
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.
0
 

Author Comment

by:holemania
Comment Utility
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.

=code.GetTotal()

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

Expert Comment

by:ValentinoV
Comment Utility
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?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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?
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
Comment Utility
Have a look at the following attachment, it contains a table similar to what you've posted in your question...
GroupSum.rdl
0
 

Author Comment

by:holemania
Comment Utility
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.
0
 

Author Comment

by:holemania
Comment Utility
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?
0
 

Author Closing Comment

by:holemania
Comment Utility
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now