SSRS Total not correct.

From what I've seen SSRS does not actually sum "Detail" row data but, rather in the footer you rewrite your "Detail" expression with the sum function encompassing it.  In Crystal Reports you have the option of explicitly expressing that the sum you want is from a detail section. I guess what I would like to do is, place into my footer the sum function with the textbox handle (or any other handle that associates with the detail expression).
ButtonDownBobbyAsked:
Who is Participating?
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
I would put this in a calculated field on the dataset:

=Round(Fields!prod_balance.Value * Fields!unit_conv.Value,3,MidpointRounding.AwayFromZero) * Fields!Expr2.Value

Open in new window

Then, instead of using ReportItems, your second expression would become:

=Sum(Fields!YourNewCalculatedField.Value, "prod_code")

Open in new window

0
 
ValentinoVBI ConsultantCommented:
The Sum function in SSRS does have a scope parameter, that may be what you're looking for.

Without it the expression is indeed as you described it:

=Sum(Fields!SomeNumber.Value)

With scope included it goes like this:

=Sum(Fields!SomeNumber.Value, "OrderDate")

The above would be valid if your tablix is grouping the data on OrderDate.

More info: Sum Function (Report Builder and SSRS)

Using the ReportItems collection you can refer to textboxes, but there are some limitations.  The largest one is scope: you cannot use an item from a lower level in a higher level.  For instance, you can't use a detail-level textbox in the OrderDate header/footer.

More info: Using the ReportItems Collection References (Report Builder 3.0 and SSRS)
0
 
ButtonDownBobbyAuthor Commented:
Here is the expression in the prod_code's group:

=Sum((Round(Fields!prod_balance.Value*Fields!unit_conv.Value,3,MidpointRounding.AwayFromZero)*Fields!Expr2.Value))

Open in new window


The name of the textbox is "Spot_Value" within the group "prod_code".

So I'm trying in prod_code's parent group footer the following:
=Sum(ReportItems!Spot_Value.Value,"prod_code")

Open in new window


and it's coming back with:

"The definition of the report '/Holdings' is invalid."

How can I fix this?( It does this within the group and within a child group as well)

I've even tried the following:
=Sum((Round(Fields!prod_balance.Value,"prod_code") * Round(Fields!unit_conv.Value,"prod_code") * Max(Fields!Expr2.Value,"prod_code")))

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ButtonDownBobbyAuthor Commented:
The following code is placed in the parent group of "prod_code":
=Sum(Fields!YourNewCalculatedField.Value, "prod_code")

Open in new window


It produces the following error:

"The Value expression for the text box "Spot_Value_Total" has a scope parameter that is not valid for an aggregate function.  The scope patameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset."
0
 
ValentinoVBI ConsultantCommented:
Can you post an example of what you're trying to achieve?

That error means you've put the expression in a location where it's not able to figure out the result, such as in the parent group of your prod_code group.  Let's say your parent is prod_cat.  Image prod_cat A has three prod_code group items which each have a "sum".  What sum do you expect your expression to retrieve?  Or are you after the sum of all prod_code items?  In that case you just need to leave out the scope:

=Sum(Fields!YourNewCalculatedField.Value)

Open in new window

0
 
ButtonDownBobbyAuthor Commented:
I got the totals to match up. In the calculated field I do a final rounding to two decimal places to preempt the rounding that I placed in the Tablix.

=Round(Round(Round(Fields!prod_balance.Value * Fields!unit_conv.Value,3,MidpointRounding.AwayFromZero) * Fields!Expr2.Value,3,MidpointRounding.AwayFromZero),2,MidpointRounding.AwayFromZero)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.