Solved

SSRS Sum Grouping

Posted on 2014-02-03
10
3,808 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
ID: 39831737
What happens if you use the following expression?

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

Expert Comment

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

=Sum(ReportItems!TotalSum.Value)

it would be something like

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

Author Comment

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

Author Comment

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

=code.GetTotal()

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

Expert Comment

by:ValentinoV
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?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 37

Expert Comment

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

Accepted Solution

by:
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...
GroupSum.rdl
0
 

Author Comment

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

Author Comment

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

Author Closing Comment

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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to fix this error 14 57
How to query date ranges with SQL 6 37
poor performance from  MySQL stored procedure 6 23
Alter an update query which rounds 7 29
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

930 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

10 Experts available now in Live!

Get 1:1 Help Now