Solved

SSRS Total not correct.

Posted on 2014-02-20
7
1,761 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now