Solved

SSRS Total not correct.

Posted on 2014-02-20
7
1,920 Views
Last Modified: 2014-03-04
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).
0
Comment
Question by:ButtonDownBobby
  • 3
  • 3
7 Comments
 
LVL 37

Expert Comment

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

Author Comment

by:ButtonDownBobby
ID: 39882846
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
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39884954
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:ButtonDownBobby
ID: 39886621
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
 
LVL 37

Expert Comment

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

Author Comment

by:ButtonDownBobby
ID: 39904273
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

A short article about problems I had with the new location API and permissions in Marshmallow
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.

828 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