Solved

SSRS Total not correct.

Posted on 2014-02-20
7
2,052 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

728 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