Link to home
Start Free TrialLog in
Avatar of holemania
holemania

asked on

SSRS Sum Grouping

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.

User generated image
Avatar of ValentinoV
ValentinoV
Flag of Belgium image

What happens if you use the following expression?

=Sum(ReportItems!TotalSum.Value)
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))
Avatar of holemania
holemania

ASKER

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.
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?
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?
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?
ASKER CERTIFIED SOLUTION
Avatar of ValentinoV
ValentinoV
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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?
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.