?
Solved

SSRS Total not correct.

Posted on 2014-02-20
7
Medium Priority
?
2,163 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 2000 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
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…
Progress

770 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