SSRS - SUM CHALLENGE (EXPERTS HELP!!!)

I've been going at this for a while now and decided it was time to reach out for help!

Can someone please tell me why i cannot sum the OrigQty cell correctly?
SSRS
The OrigQty show's 174,280 as the total sum of everything below it which is incorrect. It should be 3,886 total.

340 + 360 + 10 + 375 + 378 + 371 + 367 + 360 + 383 + 357 + 352 + 192  = 3,886

Where is 174,280 coming from? How can I get it so that OrigQty value is 3,886?

The expression for OrigQty is:
 =Sum(Fields!originalnumberofQtyValue.Value, "DataSet1", Recursive)

The expression for OrigQty on the 2nd row is:
=Fields!originalnumberofQtyValue.Value
teknovationAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EmesCommented:
take out the recursive
 =Sum(Fields!originalnumberofQtyValue.Value, "DataSet1")

next it most likely has to do with the grouping. I have had similar problems.
take out all the grouping data and rebuild it.
 if that does not work I have rebuild the report working on small sections of data to find out the  report
teknovationAuthor Commented:
Removing the recursive keyword did nothing.
Russell FoxDatabase DeveloperCommented:
The issue is that the SUM has no concept of the group, so I suspect that the 174,280 is the total sum over the entire data set. You'll need a way to put a WHERE clause into the expression, something like 
=Sum(IIF(Fields!ProductID.Value = First(ReportItems("ProductID").Value, Fields!originalnumberofQtyValue.Value, NOTHING), "DataSet1")

Open in new window

where "First(ReportItems("ProductID").Value" points to a text box that you've named "ProductID". I'm not sure it will work. You might be better off creating a new data set that just has the totals.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download eBook now!
teknovationAuthor Commented:
Hmm...what's interesting is that the row that is grouped by the Stock Id gives each row a different value when I sum the 2nd row.

=Sum(Fields!originalnumberofQtyValue.Value)

Do you have a tutorial or link to a website that can show me how I can create the value in another dataset? I assume you can use more than 1 data set on a report?

The thing is I'm using Fetch XML for the data source and not SQL. So that makes it more difficult
teknovationAuthor Commented:
anyone with fetchxml experience? Need help on how to write sum a value
Russell FoxDatabase DeveloperCommented:
Yes, you can have multiple data sets, but each tablix control can only have one as its source. You can generally get around this:
Create a data set that just has the [StockID] and total for that StockID.
Create a tablix control with that data on one line and a blank line within that group below it.
Create a new report with no header/footer/border, just a tablix with the detailed data (like you have now), but have a WHERE STOCKID = @STOCKID clause in the query.
On the main report, put a subreport object in that blank second row and set the source to the new detailed subreport.
Right-click the subreprort and set the @STOCKID parameter to the [StockID] field on the main report.
Sorry, no experience with FetchXml, but SUM is an aggregation function:
Use FetchXML aggregation (MSDN)
See if you can add FetchXML as a tag on the question.
teknovationAuthor Commented:
thanks Russell, I'll give your solution a try and let you know how I make out.
Russell FoxDatabase DeveloperCommented:
Bear in mind that sub-reports can run slowly, so it would be better if you can get it working with regular grouping/aggregation.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.